Engenho & Engenhocas

Cláusulas Over e Partition by em SQL Server

Para ler

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