Aplicar funções de janela para análise
As consultas analíticas geralmente exigem cálculos que abrangem várias linhas enquanto ainda retornam detalhes de linhas individuais. As funções de agregação tradicionais agregam linhas em grupos, perdendo informações de nível de linha. As funções de janela resolvem esse desafio executando cálculos em um conjunto de linhas relacionadas à linha atual, sem recolher o conjunto de resultados.
Entender a sintaxe da função de janela
As funções de janela calculam valores em uma "janela" de linhas definidas pela OVER cláusula. Ao contrário das funções de agregação regulares, as funções de janela não agrupam linhas em uma única linha de saída. Em vez disso, eles calculam valores entre linhas relacionadas, preservando todas as linhas originais no resultado.
A sintaxe geral de uma função de janela é:
function_name(arguments) OVER (
[PARTITION BY partition_expression]
[ORDER BY order_expression [ASC | DESC]]
[ROWS | RANGE frame_specification]
)
Os OVER componentes da cláusula controlam como a janela é definida:
- PARTITION BY: divide linhas em grupos (partições) para o cálculo
- ORDER BY: Determina a ordem lógica das linhas em cada partição
- ROWS/RANGE: define os limites de quadro relativos à linha atual
A consulta a seguir demonstra uma função de janela simples que calcula um total acumulado dos valores de pedidos por cliente.
SELECT
CustomerID,
SalesOrderID,
OrderDate,
TotalDue,
SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RunningTotal
FROM SalesLT.SalesOrderHeader
ORDER BY CustomerID, OrderDate;
Observação
Quando você especifica ORDER BY na OVER cláusula sem uma especificação de quadro, o quadro padrão é RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW para funções de agregação. Isso cria cálculos cumulativos.
Usar funções de classificação
As funções de classificação atribuem números sequenciais a linhas com base em sua posição dentro de uma partição. O SQL Server fornece quatro funções de classificação. Cada função lida com vínculos de forma diferente:
ROW_NUMBER() atribui um número sequencial exclusivo a cada linha, sem duplicatas mesmo para valores vinculados:
SELECT
ProductID,
Name,
ListPrice,
ROW_NUMBER() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;
O conjunto de resultados tem esta aparência:
ProductID Name ListPrice PriceRank
--------- ------------------------- --------- ---------
749 Road-150 Red, 62 3578.27 1
750 Road-150 Red, 44 3578.27 2
751 Road-150 Red, 48 3578.27 3
771 Mountain-100 Silver, 38 3399.99 4
Essa consulta classifica todos os produtos por preço do mais alto para o mais baixo. Cada produto recebe um número exclusivo, independentemente de vários produtos compartilharem o mesmo preço.
RANK() atribui a mesma classificação a valores vinculados e, em seguida, ignora os números para considerar os vínculos:
SELECT
ProductID,
Name,
ListPrice,
RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;
O conjunto de resultados tem esta aparência:
ProductID Name ListPrice PriceRank
--------- ------------------------- --------- ---------
749 Road-150 Red, 62 3578.27 1
750 Road-150 Red, 44 3578.27 1
751 Road-150 Red, 48 3578.27 1
771 Mountain-100 Silver, 38 3399.99 4
Quando dois produtos têm preços idênticos, ambos recebem a mesma classificação. A classificação do próximo produto reflete o número total de produtos classificados mais alto, criando lacunas na sequência.
DENSE_RANK() atribui a mesma classificação a valores vinculados, mas não ignora números:
SELECT
ProductID,
Name,
ListPrice,
DENSE_RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;
O conjunto de resultados tem esta aparência:
ProductID Name ListPrice PriceRank
--------- ------------------------- --------- ---------
749 Road-150 Red, 62 3578.27 1
750 Road-150 Red, 44 3578.27 1
751 Road-150 Red, 48 3578.27 1
771 Mountain-100 Silver, 38 3399.99 2
Por exemplo RANK(), os valores vinculados compartilham a mesma classificação. No entanto, DENSE_RANK() continua com o próximo número consecutivo, para que você possa usá-lo para contar níveis de preço distintos.
NTILE(n) distribui linhas em um número especificado de grupos aproximadamente iguais:
SELECT
ProductID,
Name,
ListPrice,
NTILE(4) OVER (ORDER BY ListPrice DESC) AS PriceQuartile
FROM SalesLT.Product
WHERE ListPrice > 0;
O conjunto de resultados tem esta aparência:
ProductID Name ListPrice PriceQuartile
--------- --------------------------- --------- -------------
749 Road-150 Red, 62 3578.27 1
771 Mountain-100 Silver, 38 3399.99 1
722 LL Road Frame - Black, 58 337.22 2
859 Half-Finger Gloves, S 24.49 4
Essa consulta divide os produtos em quatro grupos com base no preço. Os produtos mais caros estão no quartil 1, e os mais baratos estão no quartil 4. Utilize NTILE() para análise de percentis ou distribuição uniforme.
A combinação PARTITION BY com funções de classificação permite classificações por grupo:
SELECT
pc.Name AS Category,
p.Name AS Product,
p.ListPrice,
ROW_NUMBER() OVER (
PARTITION BY p.ProductCategoryID
ORDER BY p.ListPrice DESC
) AS CategoryPriceRank
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > 0;
O conjunto de resultados tem esta aparência:
Category Product ListPrice CategoryPriceRank
--------------- ------------------------- --------- -----------------
Road Bikes Road-150 Red, 62 3578.27 1
Road Bikes Road-150 Red, 44 3578.27 2
Mountain Bikes Mountain-100 Silver, 38 3399.99 1
Mountain Bikes Mountain-100 Black, 38 3374.99 2
Essa consulta classifica os produtos em cada categoria separadamente. A classificação é reiniciada em 1 para cada categoria, para que você possa identificar o produto mais caro em cada categoria filtrando para CategoryPriceRank = 1.
Dica
Use ROW_NUMBER() quando precisar exatamente de uma linha por classificação (como localizar o N superior por grupo). Use RANK() ou DENSE_RANK() quando precisar preservar informações de vínculo para fins de geração de relatórios.
Aplicar funções de agregação em janelas
Funções de agregação padrão, como SUM, AVG, COUNTMINe MAX podem ser usadas como funções de janela adicionando a OVER cláusula. Isso permite que você calcule agregações enquanto mantém a retenção dos detalhes das linhas individuais.
A consulta a seguir demonstra como calcular totais em execução e agregações cumulativas:
SELECT
SalesOrderID,
OrderDate,
TotalDue,
SUM(TotalDue) OVER (ORDER BY OrderDate, SalesOrderID) AS RunningTotal,
AVG(TotalDue) OVER (ORDER BY OrderDate, SalesOrderID) AS RunningAverage,
COUNT(*) OVER (ORDER BY OrderDate, SalesOrderID) AS OrderNumber
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate, SalesOrderID;
O conjunto de resultados tem esta aparência:
SalesOrderID OrderDate TotalDue RunningTotal RunningAverage OrderNumber
------------ ---------- --------- ------------ -------------- -----------
71774 2008-06-01 972.785 972.785 972.785 1
71776 2008-06-01 87.083 1059.868 529.934 2
71780 2008-06-01 42452.65 43512.518 14504.172 3
71782 2008-06-01 43962.79 87475.308 21868.827 4
Importante
Ao usar funções de janela de agregação sem ORDER BY na cláusula OVER, a função calcula toda a partição. Adicionar ORDER BY cria um cálculo em execução desde o início da partição até a linha atual.
Definir quadros de janela com ROWS e RANGE
Os quadros de janela permitem especificar exatamente quais linhas relativas à linha atual devem ser incluídas no cálculo. A ROWS cláusula conta linhas físicas, enquanto RANGE agrupa linhas com valores iguais.
Limites de quadro podem ser especificados usando:
-
UNBOUNDED PRECEDING: desde o início da partição -
n PRECEDING:nlinhas antes da linha atual -
CURRENT ROW: a linha atual -
n FOLLOWING:nlinhas após a linha atual -
UNBOUNDED FOLLOWING: para o final da partição
A consulta a seguir calcula uma média móvel nos últimos três pedidos:
SELECT
SalesOrderID,
OrderDate,
TotalDue,
AVG(TotalDue) OVER (
ORDER BY OrderDate
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS MovingAvg3Orders
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate;
O conjunto de resultados tem esta aparência:
SalesOrderID OrderDate TotalDue MovingAvg3Orders
------------ ---------- --------- ----------------
71774 2008-06-01 972.785 972.785
71776 2008-06-01 87.083 529.934
71780 2008-06-01 42452.65 14504.172
71782 2008-06-01 43962.79 28834.174
Essa consulta calcula uma média móvel de ordem 3 incluindo linha atual e as duas linhas anteriores. Para a primeira linha, apenas um valor está disponível, portanto, a média é TotalDueigual a . Ao chegar à terceira linha, a janela inclui todas as três linhas.
Usar funções analíticas
As funções analíticas permitem acessar dados de outras linhas sem usar autojunções ou subconsultas. Essas funções são úteis para análise de série temporal, detecção de tendência e comparação de valores atuais com valores históricos ou futuros. Ao contrário das funções de janela de agregação que calculam resumos, as funções analíticas recuperam valores específicos de linhas específicas na janela.
LAG() e LEAD() acessam valores de linhas anteriores ou subsequentes, da seguinte forma:
SELECT
SalesOrderID,
OrderDate,
TotalDue,
LAG(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS PreviousOrderTotal,
LEAD(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS NextOrderTotal,
TotalDue - LAG(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS ChangeFromPrevious
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate;
O conjunto de resultados tem esta aparência:
SalesOrderID OrderDate TotalDue PreviousOrderTotal NextOrderTotal ChangeFromPrevious
------------ ---------- --------- ------------------ -------------- ------------------
71774 2008-06-01 972.785 0 87.083 972.785
71776 2008-06-01 87.083 972.785 42452.65 -885.702
71780 2008-06-01 42452.65 87.083 43962.79 42365.567
71782 2008-06-01 43962.79 42452.65 0 1510.14
LAG() recupera um valor de uma linha anterior, enquanto LEAD() recupera de uma linha a seguir. O segundo parâmetro especifica quantas linhas olhar para trás ou para frente (o padrão é 1) e o terceiro parâmetro fornece um valor padrão quando nenhuma linha existe (como para a primeira linha com LAG()). Use essas funções para calcular alterações de período a período, identificar tendências ou detectar anomalias em dados sequenciais.
FIRST_VALUE() e LAST_VALUE() retornar valores da primeira ou última linha no quadro:
SELECT
ProductID,
Name,
ListPrice,
ProductCategoryID,
FIRST_VALUE(Name) OVER (
PARTITION BY ProductCategoryID
ORDER BY ListPrice DESC
) AS MostExpensiveInCategory,
LAST_VALUE(Name) OVER (
PARTITION BY ProductCategoryID
ORDER BY ListPrice DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS LeastExpensiveInCategory
FROM SalesLT.Product
WHERE ListPrice > 0;
O conjunto de resultados tem esta aparência:
ProductID Name ListPrice ProductCategoryID MostExpensiveInCategory LeastExpensiveInCategory
--------- ------------------------- --------- ----------------- ------------------------ ------------------------
749 Road-150 Red, 62 3578.27 5 Road-150 Red, 62 LL Road Frame - Red, 58
750 Road-150 Red, 44 3578.27 5 Road-150 Red, 62 LL Road Frame - Red, 58
722 LL Road Frame - Red, 58 337.22 5 Road-150 Red, 62 LL Road Frame - Red, 58
771 Mountain-100 Silver, 38 3399.99 6 Mountain-100 Silver, 38 Mountain-500 Black, 52
FIRST_VALUE() retorna o valor da primeira linha na janela ordenada, que nesse caso é o produto mais caro por categoria.
LAST_VALUE() retorna a opção menos cara, mas requer um quadro explícito para incluir todas as linhas. Essas funções ajudam você a comparar cada linha com valores de parâmetro de comparação, como o valor mais alto, mais baixo ou de linha de base em um grupo.
Observação
LAST_VALUE() requer uma especificação de quadro explícita para incluir linhas após a linha atual. Sem ele, o quadro padrão inclui apenas linhas até a linha atual, retornando LAST_VALUE() o valor da linha atual.
PERCENT_RANK() e CUME_DIST() calculam a posição relativa dentro de uma partição:
SELECT
Name,
ListPrice,
PERCENT_RANK() OVER (ORDER BY ListPrice) AS PercentRank,
CUME_DIST() OVER (ORDER BY ListPrice) AS CumulativeDistribution
FROM SalesLT.Product
WHERE ListPrice > 0
ORDER BY ListPrice;
O conjunto de resultados tem esta aparência:
Name ListPrice PercentRank CumulativeDistribution
------------------------- --------- ----------- ----------------------
Patch Kit/8 Patches 2.29 0.0 0.0081
Road Tire Tube 3.99 0.0081 0.0162
Touring Tire Tube 4.99 0.0162 0.0243
Road-150 Red, 62 3578.27 0.9919 1.0
PERCENT_RANK() retorna um valor entre 0 e 1 indicando qual percentual de linhas tem valores mais baixos (0 significa mais baixo, um significa mais alto).
CUME_DIST() mostra a distribuição cumulativa, indicando qual porcentagem de linhas tem valores menores ou iguais à linha atual. Use essas funções para análise percentil, identificação de exceções ou criação de relatórios de distribuição.
Para obter mais informações sobre funções de janela, consulte funções de janela (Transact-SQL) e funções de classificação.