Funções de janela (window functions)

p009_chinese_192x108As funções de janela (window functions) auxiliam de forma intuitiva na resolução de uma variedade de tarefas. Neste contexto, a janela se refere a um conjunto de linhas cujo conteúdo é definido na cláusula OVER.

Esta publicação contém a primeira e a segunda partes deste artigo, tratando das funções de janela de classificação e de posicionamento. O artigo está em ampliação gradual, à medida que mais funções de janela são acrescentadas. Para ser avisado das novas ampliações basta clicar no botão SEGUIR que está na lateral direita, logo abaixo de SEGUIR BLOG VIA EMAIL.

1. Introdução

As funções de janela (window functions) auxiliam de forma intuitiva na resolução de uma variedade de tarefas. Neste contexto, a janela se refere a um conjunto de linhas cujo conteúdo é definido na cláusula OVER.

O conceito de funções de janela (window functions) foi definido na especificação ISO SQL:2003 e ampliado na especificação SQL:2008. Entretanto, até o momento no SQL Server somente foi implementado subconjunto da especificação de funções de janela.

1.1. Cláusula OVER

É através da cláusula OVER que é possível definir o que conterá cada janela na qual será aplicada a função. A sintaxe básica é

OVER (   
      [ <PARTITION BY clause> ]  
      [ <ORDER BY clause> ]   
      [ <ROW or RANGE clause> ]  
     )

em que os três elementos possibilitam o particionamento (PARTITION BY), ordenação (ORDER BY) e framing (ROW ou RANGE) do conteúdo da janela.

PARTITION BY. O primeiro elemento

<PARTITION BY clause> ::=  
PARTITION BY value_expression , ... [ n ]

divide o conjunto de dados em n partes e a função de janela é então aplicada separadamente a cada partição. Este elemento está disponível em todas as funções de janela.

Por value_expression normalmente se utiliza coluna de tabela que faça parte da cláusula FROM, mas pode também ser uma expressão. Podem ser utilizadas uma ou mais colunas, cujo conteúdo definirá o que ficará em cada janela.

ORDER BY. O segundo elemento

<ORDER BY clause> ::=  
ORDER BY order_by_expression  
    [ COLLATE collation_name ]   
    [ ASC | DESC ]   
    [ ,...n ]

possibilita definir a ordem lógica em que as linhas da janela serão manuseadas pela função. O detalhe é que é possível definir o agrupamento (COLLATE) que será utilizado para a ordenação.

ROW ou RANGE. Finalizando, o terceiro elemento

<ROW or RANGE clause> ::=  
{ ROWS | RANGE } <window frame extent>

é denominado de window framing e é de entendimento complexo, comparando-se com os dois elementos anteriores. Atua como uma subdivisão da janela, limitando as linhas dentro da partição com a especificação de pontos iniciais e finais na janela. Segundo a documentação, “Isso é feito pela especificação de um intervalo de linhas em relação à linha atual por associação lógica ou associação física. (…) A cláusula ROWS limita as linhas dentro de uma partição especificando um número fixo de linhas antes ou depois da linha atual. Alternativamente, a cláusula RANGE limita as linhas logicamente dentro de uma partição especificando um intervalo de valores em relação ao valor na linha atual”.

Detalhando <window frame extent> temos

<window frame extent> ::= 
{ <window frame preceding> 
  | <window frame between> 
} 

<window frame preceding> ::= 
{ 
    UNBOUNDED PRECEDING 
  | <unsigned_value_specification> PRECEDING 
  | CURRENT ROW 
}

<window frame between> ::= 
  BETWEEN <window frame bound> AND <window frame bound>

A sintaxe completa de window framing encontra-se na documentação da cláusula OVER (vide item Referências, ao final).

É preciso ficar atento que, se ROWS/RANGE não for especificado mas ORDER BY for especificado, RANGE UNBOUNDED PRECEDING AND CURRENT ROW é usado como definição da janela. Isso só se aplica a funções que podem aceitar a especificação de ROWS/RANGE como opcional.

1.2. Tipos de funções

No SQL Server as funções nativas são agrupadas nos seguintes tipos:

  • agregação: executam um cálculo considerando-se o conjunto de linhas da janela e retornam um único valor. Todas as funções de agregação são determinísticas, o que significa que elas sempre retornam o mesmo valor quando são executadas nos mesmos valores de entrada;
  • analíticas: computam um valor com base no conjunto de linhas da janela. São utilizadas para calcular médias móveis, totais acumulados etc.;
  • classificação: retornam um valor para cada linha da janela;
  • posicionamento: são funções que retornam valor baseado no posicionamento da linha em relação às demais da janela.

1.3. Banco de dados NorthWind

Para testes deste artigo foi utilizado o banco de dados NorthWind. Caso você não o conheça, no apêndice deste artigo está a explicação sobre como obtê-lo.

2. Funções de classificação

O SQL Server possui atualmente 4 funções de classificação (ranking): ROW_NUMBER, NTILE, RANK e DENSE_RANK. Essas funções retornam um valor para cada linha da janela, sendo que dependendo da função usada algumas linhas podem receber o mesmo valor que outras.

Nas funções de classificação o elemento PARTITION BY é opcional mas o elemento ORDER BY é obrigatório. Quando não é definido o critério de particionamento, todo o conjunto de dados é utilizado na forma de uma única partição.

2.1. ROW_NUMBER

Provavelmente é a função de janela mais utilizada, principalmente para instalações com versão do SQL Server anterior a 2012, pois com o uso dessa função é possível resolver diversos casos clássicos de SQL. O funcionamento dela é bem simples: gera uma sequência numérica no conjunto de linhas da janela, em ordem definida pelo elemento ORDER BY.

-- código #2.1
with
MaisVendidos as (
SELECT ProductID,
       sum (Quantity) as QtdVenda
  from dbo.[Order Details]
  group by ProductID
),
Classificado as (
SELECT seq= row_number() over (order by QtdVenda desc),
       MV.ProductID, P.ProductName, MV.QtdVenda
  from MaisVendidos as MV
       inner join dbo.Products as P on P.ProductID = MV.ProductID
)
SELECT top (15) *
  from Classificado
  order by seq;

No código anterior, na cte (common expression table) Mais_Vendidos são calculadas quantas unidades foram vendidas de cada produto. A relação parcial dos produtos mais vendidos é a seguinte:

p009_codigo #2.1a

Na cte Classificado no resultado da cte Mais_Vendidos é acrescentada a coluna seq, contendo sequência numérica crescente dos produtos a partir do mais vendido e em ordem decrescente até o menos vendido. Finalizando, é emitida a relação dos 15 primeiros produtos mais vendidos:

p009_codigo #2.1b

Uma aplicação típica da função ROW_NUMBER é quando se solicita o último pedido de um cliente. Considerando-se o banco de dados NorthWind, temos:

-- código #2.2
with
Pedido_Cliente as (
SELECT *,
       seq= row_number() over (partition by CustomerID
                               order by OrderDate desc)
  from dbo.Orders
)
SELECT P.CustomerID as [Id do cliente],
       C.CompanyName as [Denominação do cliente],
       convert(char(10), P.OrderDate, 103) as [Data do pedido]
  from Pedido_Cliente as P
       inner join Customers as C on C.CustomerID = P.CustomerID
  where P.seq = 1;

No código acima observamos a presença do elemento PARTITION BY, que foi utilizado para criar n janelas, com cada janela contendo as linhas de um mesmo cliente. A seguir o elemento ORDER BY ordena o conteúdo de cada janela em ordem decrescente pela data do pedido.

O resultado parcial dessa consulta é

p009_codigo #2.2

2.2. RANK

A função de janela RANK retorna a classificação de cada linha na janela. As funções ROW_NUMBER e RANK geram resultados similares: enquanto a função ROW_NUMBER numera todas as linhas em sequência (por exemplo 1, 2, 3, 4, 5, a função RANK fornece o mesmo valor numérico para empates (por exemplo 1, 2, 2, 4, 5) .

Na relação final do item anterior, observe que para os produtos de códigos 2 e 71 (linhas 10 e 11) foi vendida a mesma quantidade de itens: 1.057. E o mesmo ocorre com os produtos de códigos 41 e 76 (linhas 13 e 14), com 981 itens vendidos, cada. Assim, o valor da coluna seq deveria ser o mesmo para os produtos com o mesmo valor da coluna QtdVendida. E é com a função de janela RANK que obteremos essa sequência numérica:

-- código #2.3
with
MaisVendidos as (
SELECT ProductID,
       sum (Quantity) as QtdVenda
  from dbo.[Order Details]
  group by ProductID
),
Classificado as (
SELECT seq= rank() over (order by QtdVenda desc),
       MV.ProductID, P.ProductName, MV.QtdVenda
  from MaisVendidos as MV
       inner join dbo.Products as P on P.ProductID = MV.ProductID
)
SELECT top (15) *
  from Classificado
  order by seq;

A única diferença entre os códigos #2.1 e #2.3 é que a função de janela row_number() foi substituída por rank(). O resultado da execução do código #2.3 é o seguinte:

p009_codigo #2.3

Se duas ou mais linhas empatarem em uma classificação, cada linha empatada receberá a mesma classificação. Portanto, a função RANK nem sempre retorna inteiros consecutivos. Isto pode ser observado para os produtos 2 e 71 (linhas 10 e 11), que tiveram a mesma quantidade de itens vendidos e estão com a mesma classificação. E o mesmo ocorre com os produtos 41 e 76 (linhas 13 e 14).

2.3. DENSE_RANK

Esta função retorna a posição de cada linha dentro de uma janela, sem nenhum intervalo nos valores de classificação.

Uma observação atenta no resultado do código #2.3 nos faz perceber que ocorre um salto no valor da coluna seq: da linha 11 para a linha 12 o valor saltou de 10 para 12. Entretanto, o que queremos é que itens que tenham o mesmo valor na coluna QtdVenda tenham a mesma classificação e que não haja saltos na classificação. Então vamos recorrer à função de janela DENSE_RANK.

-- código #2.4
with
MaisVendidos as (
SELECT ProductID,
       sum (Quantity) as QtdVenda
  from dbo.[Order Details]
  group by ProductID
),
Classificado as (
SELECT seq= dense_rank() over (order by QtdVenda desc),
       MV.ProductID, P.ProductName, MV.QtdVenda
  from MaisVendidos as MV
       inner join dbo.Products as P on P.ProductID = MV.ProductID
)
SELECT top (15) *
  from Classificado
  order by seq;

Ao executar o código anterior temos o seguinte resultado:

p009_codigo #2.4

Se duas ou mais linhas tiverem o mesmo valor de classificação na mesma janela, cada uma dessas linhas receberá a mesma classificação; os números retornados pela função DENSE_RANK não têm lacunas e sempre têm valores de classificação consecutivos.

Nas linhas 12 e 15 observamos que não há mais saltos na coluna seq. Obtivemos finalmente o resultado desejado. E nessas tentativas e erros foi possível conhecer, na prática, as funções de janela ROW_NUMBER, RANK e DENSE_RANK.

2.4. NTILE

NTILE é a próxima função de janela que abordaremos neste artigo. Ela divide um conjunto de dados ordenado em n partes e numera as partes sequencialmente. Desta forma, todas as linhas de cada parte possuem o mesmo valor.

Considerando-se ainda o mesmo conjunto de dados utilizado nos itens anteriores, caso se queira conhecer a décima parte inicial dos produtos mais vendidos, temos:

-- código #2.5
with
MaisVendidos as (
SELECT ProductID,
       sum (Quantity) as QtdVenda
  from dbo.[Order Details]
  group by ProductID
),
Classificado as (
SELECT seq= ntile(10) over (order by QtdVenda desc),
       MV.ProductID, P.ProductName, MV.QtdVenda
  from MaisVendidos as MV
       inner join dbo.Products as P on P.ProductID = MV.ProductID
)
SELECT ProductID, ProductName, QtdVenda
  from Classificado
  where seq = 1;

O código anterior gera o seguinte resultado:

p009_codigo #2.5

Alguém conhece o “Guaraná Fantástica”? Ele é o sétimo produto mais vendido, na lista acima…

3. Funções de posicionamento (offset)

As funções de posicionamento (offset) retornam valores selecionados dentro de uma janela, considerando-se ou a linha corrente ou a janela toda. São elas: FIRST_VALUE, LAST_VALUE, LEAD e LAG. As duas primeiras funções o próprio nome já explica o que retornam; quanto às duas seguintes, LEAD acessa dados de linha anterior à linha corrente e LAG de linha posterior. Mas isto será visto em detalhes no decorrer deste capítulo.

3.1. FIRST_VALUE

Esta função retorna o primeiro valor de uma janela cujo conjunto de dados esteja ordenado logicamente. Está disponível a partir da versão 2012 do SQL Server.

A sintaxe básica é

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

No item 2.1 consta o código #2.2, que retorna o último pedido de um cliente mas utilizando a função ROW_NUMBER. Aquele era o procedimento usual até a versão 2008 R2 do SQL Server. Agora, o mesmo resultado é possível de obter de forma mais simples e, principalmente, mais rápida.

-- código #3.1
SELECT C.CustomerID as [Id do cliente],
       C.CompanyName as [Denominação do cliente],
       convert(char(10), 
               (SELECT top(1) first_value (O.OrderDate)
                              over (partition by O.CustomerID
                                    order by O.OrderDate desc
                  from dbo.Orders as O
                  where O.CustomerID = C.CustomerID),
               103) as [Data do pedido]
  from dbo.Customers as C;

O resultado parcial (15 linhas iniciais) é o seguinte:

p009_codigo #3.1

O código #3.1 utiliza subconsulta correlacionada, na lista de colunas do SELECT.

3.2. LAST_VALUE

Esta função retorna o último valor de uma janela cujo conjunto de dados esteja ordenado logicamente. Está disponível a partir da versão 2012 do SQL Server.

A sintaxe básica é

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

Na cláusula OVER somente a definição da ordenação que é obrigatória, mas se não for definido o framing o comportamento é o seguinte, segundo a documentação da cláusula OVER: “Se ROWS/RANGE não for especificado, mas ORDER BY for especificado, RANGE UNBOUNDED PRECEDING AND CURRENT ROW é usado como padrão para definição da janela”.

No código #3.1 o último cliente foi encontrado ordenando logicamente cada janela por ordem decrescente de cliente. Mas o mesmo resultado pode ser obtido ao ordenar em ordem crescente, mas então utilizando a função LAST_VALUE para obter a última linha de cada janela.

-- código #3.2
SELECT C.CustomerID as [Id do cliente],
       C.CompanyName as [Denominação do cliente],
       convert(char(10), 
               (SELECT top(1) last_value (O.OrderDate)
                                over (partition by O.CustomerID
                                      order by O.OrderDate asc
                                      rows between unbounded preceding 
                                           and unbounded following)
                  from dbo.Orders as O
                  where O.CustomerID = C.CustomerID),
               103) as [Data do pedido]
  from dbo.Customers as C;

O resultado parcial (15 linhas iniciais) é o seguinte:

p009_codigo #3.2

No caso do exemplo da função LAST_VALUE foi necessário definir o framing. Ou seja, o conteúdo da janela foi definido pela cláusula PARTITION BY em conjunto com ROWS (framing). Você se lembra qual o motivo? Se não, dê uma pausa e releia o item 1.1, que trata da cláusula OVER.

Como se percebe, utilizar LAST_VALUE ou FIRST_VALUE depende tão somente do critério de ordenação lógica da janela. Entretanto, é preciso ficar atento aos índices existentes para evitar que internamente seja necessário ordenar o conjunto de dados de acordo com os critérios de PARTITION BY / ORDER BY. Por exemplo, se há índice pelas colunas CustomerID e OrderDate, em ordem crescente, e o índice atende às necessidades da consulta, é preferível utilizar a função LAST_VALUE para se obter a data do último pedido.

3.3. LEAD

Esta função retorna dados de alguma linha adiante (normalmente a próxima) de uma janela cujo conjunto de dados esteja ordenado logicamente.

A sintaxe básica da função é

LEAD ( scalar_expression [ ,offset ] , [ default ] ) 
     OVER ( [ partition_by_clause ] order_by_clause )

onde:

  • scalar_expression: uma expressão que utiliza dados de linha adiante. Usualmente é o nome de uma coluna;
  • offset: deslocamento, em número de linhas, adiante da linha corrente; usualmente é o valor 1;
  • default: valor a retornar quando o resultado de scalar_expression for NULL.

Para exemplificar aplicação desta função vamos supor que seja necessário conhecer qual o intervalo médio de dias entre pedidos, para cada cliente. Utilizando a função LEAD podemos obter a data do pedido seguinte e então calcular a diferença de dias entre os dois pedidos. Fazendo isso para cada pedido, ao final basta calcular o valor médio dos intervalos, para cada cliente.

-- código #3.3
with
Intervalo_Pedido as (
SELECT CustomerID,
       datediff (day,
                 OrderDate,
                 lead (OrderDate, 1)
                     over (partition by CustomerID
                           order by OrderDate asc)
                ) as Intervalo
  from dbo.Orders
)
SELECT CustomerID,
       avg (cast (Intervalo as float)) as [Intervalo médio]
  from Intervalo_Pedido
  where Intervalo is not null
  group by CustomerID;

O resultado parcial (15 linhas iniciais) é o seguinte:

p009_codigo #3.3

Se não houvesse a função LEAD, como poderíamos resolver o problema? Uma solução usual é através da função ROW_NUMBER.

-- código #3.4
with
Pedido_Numerado as (
SELECT *,
       seq= row_number() over (partition by CustomerID
                               order by OrderDate asc) 
  from dbo.Orders
),
Intervalo_Pedido as (
SELECT P1.CustomerID,
       datediff (day, P1.OrderDate, P2.OrderDate) as Intervalo
  from Pedido_Numerado as P1
       inner join Pedido_Numerado as P2 
                  on P2.CustomerID = P1.CustomerID
                     and P2.seq = (P1.seq +1)
)
SELECT CustomerID, 
       avg (cast (Intervalo as real)) as [Intervalo médio]
  from Intervalo_Pedido
  group by CustomerID;

Como exercício de compreensão, compare os planos de execução dos códigos #3.3 e #3.4, bem como as estatísticas de IO.

3.4. LAG

Esta função retorna dados de alguma linha anterior (normalmente a primeira) de uma janela cujo conjunto de dados esteja ordenado logicamente.

A sintaxe básica da função é

LAG ( scalar_expression [ ,offset ] , [ default ] ) 
    OVER ( [ partition_by_clause ] order_by_clause )

onde:

  • scalar_expression: uma expressão que utiliza dados de linha anterior. Usualmente é o nome de uma coluna;
  • offset: deslocamento, em número de linhas, anterior à linha corrente; usualmente é o valor 1;
  • default: valor a retornar quando o resultado de scalar_expression for NULL.

Vamos utilizar o mesmo exemplo anterior, só que substituindo a função LEAD pela LAG. O resultado final deve ser o mesmo.

-- código #3.5
with
Intervalo_Pedido as (
SELECT CustomerID,
       datediff (day, 
                 lag (OrderDate, 1)
                     over (partition by CustomerID
                           order by OrderDate asc),
                 OrderDate
                ) as Intervalo
  from dbo.Orders
)
SELECT CustomerID, 
       avg (cast (Intervalo as real)) as [Intervalo médio]
  from Intervalo_Pedido
  where Intervalo is not null
  group by CustomerID;

Ou seja, temos um exemplo que tanto pode ser resolvido pela função LEAD quanto pela LAG. Mas nem sempre é assim, sendo necessário avaliar qual das duas nos fornecerá a informação necessária.

É preciso ficar atento aos índices existentes para evitar que internamente seja necessário ordenar o conjunto de dados de acordo com os critérios de PARTITION BY / ORDER BY.

4. Referências

A. Apêndices

A.1. Banco de dados NorthWind

Para testes neste artigo foi utilizado o banco de dados NorthWind, que é da época do SQL Server 2000. É um banco de dados pequeno e o suficiente para demonstração das funções de janela. Os bancos de dados de amostra recentes, como Adventure Works e Wide World Importers, demandam muito espaço físico.

Para obtê-lo, acesse a página “Northwind and pubs Sample Databases for SQL Server 2000” e clique no botão Download; o arquivo SQL2000SampleDb.msi será gravado na pasta escolhida. A seguir faça clique duplo sobre o arquivo e siga as instruções (next, next, close) para que o roteiro (script) de criação do banco de dados seja extraído na pasta SQL Server 2000 Sample Databases da unidade C:. A seguir, rode o Management Studio, abra o arquivo instnwnd.sql e rode o roteiro. Ele cria o banco de dados NorthWind completo.

Segue o diagrama do banco de dados para facilitar a compreensão do conteúdo do banco de dados.

p009_Northwind


PDF
Este artigo também está disponível em arquivo no formato PDF, o que possibilita tanto visualizar online quanto obter cópia (download) para leitura offline e até mesmo impressão. Clique no texto abaixo para acessar.

Funções de janela (window functions)

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

Este site utiliza o Akismet para reduzir spam. Saiba como seus dados em comentários são processados.

Crie seu site com o WordPress.com
Comece agora
%d blogueiros gostam disto: