Cláusulas Over e Partition by em SQL Server
A cláusula OVER define uma janela ou conjunto de registos especificado pelo utilizador nos resultados da consulta. Uma função de janela calcula um valor para cada registo na janela. A cláusula OVER pode ser usada com funções para calcular valores agregados.
A cláusula PARTITION BY é uma sub-cláusula da cláusula OVER. A cláusula PARTITION BY divide os resultados de uma consulta em partições. A função da janela é executada em cada partição separadamente.
A cláusula PARTITION BY divide os resultados em partições e altera como a função da janela é calculada. A cláusula PARTITION BY não reduz o número de linhas retornadas.
O exemplo seguinte serve apenas para ilustrar a aplicação combinada destas cláusulas.
Exemplo para uma factura
CREATE TABLE Factura
(
ID INT,Numero VARCHAR(9),Montantetotal DECIMAL
);
CREATE TABLE Facturalinha
(
ID INT,FacturaID INT,Descricao NVARCHAR(50),Montante DECIMAL
);
INSERT INTO Factura
VALUES (1,'002-00509',300.00);
INSERT INTO Facturalinha
VALUES (1,1,'produto 01',50.00);
INSERT INTO Facturalinha
VALUES (2,1,'produto 02',30.00);
INSERT INTO Facturalinha
VALUES (3,1,'produto 03',20.00);
INSERT INTO Facturalinha
VALUES (4,1,'produto descrição a definir',200.00);
INSERT INTO Factura
VALUES (2,'003-00595',400.00);
INSERT INTO Facturalinha
VALUES (1,2,'produto 01',70.00);
INSERT INTO Facturalinha
VALUES (2,2,'produto 02',30.00);
INSERT INTO Facturalinha
VALUES (3,2,'produto 03',100.00);
INSERT INTO Facturalinha
VALUES (4,2,'produto 04',50.00);
INSERT INTO Facturalinha
VALUES (5,2,'produto 05',150.00);
SELECT f.Numero AS "Factura número"
, ROW_NUMBER() OVER(PARTITION BY fl.FacturaID ORDER BY fl.ID ASC) AS "Linha nº"
, fl.Descricao AS "Descrição"
, fl.Montante
, SUM(fl.Montante) OVER(PARTITION BY fl.FacturaID ORDER BY fl.ID ASC ROWS UNBOUNDED PRECEDING) AS "Montante acumulado"
, IIF(fl.ID = MAX(fl.ID) OVER (PARTITION BY fl.FacturaID), SUM(fl.Montante) OVER(PARTITION BY fl.FacturaID), 0) AS "Montante total factura"
, f.MontanteTotal AS "Factura montante total"
FROM Factura f
INNER JOIN Facturalinha fl
ON f.Id = fl.Facturaid
O SELECT apresenta o seguinte resultado
Relacionado: SQL PARTITION BY Clause overview
Fonte: Microsoft Docs
Licença CC BY-SA 4.0
Silvia Pinhão Lopes, 8.6.20
Sem comentários: