Use as funções RANK, AGGREGATE e OFFSET

  • 6 minutos

Em operações de janela, você pode usar funções agregadas como SUM, MIN e MAX para operar em um conjunto de linhas definidas pela cláusula OVER e seus argumentos.

As funções de janela podem ser categorizadas como:

  • Funções de agregação. Como SUM, AVG e COUNT, que operam em uma janela e retornam um valor escalar.
  • Funções de classificação. Como RANK, ROW_NUMBER e NTILE. As funções de classificação exigem uma ordem de classificação e retornam um valor de classificação para cada linha em uma partição.
  • Funções analíticas. Como CUME_DIST, PERCENTILE_CONT ou PERCENTILE_DISC. As funções analíticas calculam a distribuição de valores na partição.
  • Funções de deslocamento. Como GAL, LEAD e LAST_VALUE. As funções de deslocamento retornam valores de outras linhas em relação à posição da linha atual.

Funções de agregação

As funções agregadas retornam totais, médias ou contagens de coisas. As funções agregadas executam um cálculo e retornam um único valor. Com exceção de COUNT(*), as funções agregadas não contam valores NULL.

Considere o código a seguir, que aplica algumas funções agregadas comuns aos preços dos produtos na tabela de produtos:

SELECT Name, ProductNumber, Color, SUM(Weight) 
OVER(PARTITION BY Color) AS WeightByColor
FROM SalesLT.Product
ORDER BY ProductNumber;

Isso retorna uma coluna chamada WeightByColor que contém o peso total para todos os produtos da mesma cor como mostrado no conjunto de resultados parciais abaixo.

A screenshot showing results from the OVER and PARTITION BY Color clause.

Funções de classificação

As funções de classificação atribuem um número a cada linha, dependendo de sua posição dentro de uma ordem especificada. A ordem é especificada usando a cláusula ORDER BY.

Considere o código a seguir, que aplica todas as quatro funções de classificação aos produtos na tabela de produtos.

SELECT productid, name, listprice 
    ,ROW_NUMBER() OVER (ORDER BY productid) AS "Row Number"  
    ,RANK() OVER (ORDER BY listprice) AS PriceRank  
    ,DENSE_RANK() OVER (ORDER BY listprice) AS "Dense Rank"  
    ,NTILE(4) OVER (ORDER BY listprice) AS Quartile  
FROM SalesLT.Product

Isso retorna uma coluna para cada uma das funções, com o número de classificação apropriado.

A screenshot showing results from ranking functions.

Funções analíticas

As funções analíticas calculam um valor com base em um grupo de linhas. As funções analíticas são usadas para calcular médias móveis, totais em execução e resultados top-N. Estas funções incluem:

  • CUME_DIST
  • FIRST_VALUE
  • PERCENT_RANK
  • PERCENTILE_CONT
  • PERCENTIL_DISC

Funções OFFSET

As funções de deslocamento permitem que você retorne um valor de linhas subsequentes ou anteriores dentro de um conjunto de resultados.

As funções de deslocamento operam em uma posição que é relativa à linha atual ou relativa ao limite inicial ou final da moldura da janela. As funções de deslocamento são:

  • LAG e LEAD - operam em um deslocamento para a linha atual e exigem a cláusula ORDER BY.
  • FIRST_VALUE e LAST_VALUE - opere em um deslocamento da moldura da janela. A sintaxe da função GAL é mostrada abaixo. A função LEAD funciona da mesma forma.
LAG (scalar_expression [,offset] [,default])  
    OVER ( [ partition_by_clause ] order_by_clause )

No exemplo de código a seguir, a função de deslocamento LEAD retorna o valor de orçamento do ano seguinte:

SELECT [Year], Budget, LEAD(Budget, 1, 0) OVER (ORDER BY [Year]) AS 'Next'
    FROM dbo.Budget
    ORDER BY [Year];

A sintaxe para LAST_VALUE é mostrada abaixo. FIRST_VALUE funciona da mesma maneira.

LAST_VALUE ( [ scalar_expression ] )  
OVER ( [ partition_by_clause ] order_by_clause rows_range_clause )

A sintaxe é semelhante a LAG e LEAD, com a adição da cláusula rows/range.