Blog – Fabiano Neves Amorim

Sliding Window

Para começar, vejamos a sintaxe da clausula OVER().

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

Um ponto importante nas windows functions e que aparentemente pode ser um pouco confuso, é a clausula window frame, também conhecida como windowing clause, window size ou sliding window.

Eu vou chamar de window frame que é como a Microsoft chama está clausula.

Na window frame conseguimos especificar qual é o subset de linhas que uma window function irá trabalhar. É possível dizer qual é a parte superior e a parte inferior de um frame. A sintaxe de um window frame é a seguinte:

[ROWS | RANGE] BETWEEN <Start expr> AND <End expr>

Onde:

<Start expr> pode ser:

• UNBOUNDED PECEDING: A window inicia na primeira linha da partição.
• CURRENT ROW: A window inicia na linha atual.
• <unsigned integer literal> PRECEDING ou FOLLOWING

<End expr> pode ser:

• UNBOUNDED FOLLOWING: A window termina na última linha da partição.
• CURRENT ROW: A window termina na linha atual.
• <unsigned integer literal> PRECEDING ou FOLLOWING

Quando não especificado, o window frame padrão é “range between unbounded preceding and current row”, ou seja, a linha superior é a primeira linha da partição atual (unbounded preceding), e a linha inferior é a linha atual (current row).

Para exemplificar toda essa teoria, vamos considerar os seguintes dados da tabela orders do banco NorthWind.

   1:  USE NorthWind

   2:  GO

   3:  SELECT OrderID, CustomerID

   4:  FROM Orders

   5:  WHERE CustomerID IN (1,2)

O resultado da consulta acima retorna 2 clientes e seus respectivos pedidos. Separei os pedidos em 2 windows particionadas por CustomerID, sendo, CustomerID = 1 e CustomerID = 2.

Ainda que conceitualmente essa não seja uma visualização exata da lógica de o que é uma window, essa é uma forma mais simples de entender o é uma window de linhas.

Uma representação mais correta do que é uma window é a seguinte:

Considerando que a primeira window tem 6 linhas, temos 6 windows que coexistem. Como elas coexistem posso facilmente dizer até onde uma window coexiste com a outra utilizando o frame.

Sei que isso não é simples de entender, mas vejamos alguns exemplos.

First_Value()

Digamos que eu queira pegar o primeiro pedido (OrderID) de cada partição acima. Eu teria uma consulta mais ou menos assim:

   1:  SELECT OrderID, CustomerID,

   2:         FIRST_VALUE(OrderID) OVER(PARTITION BY CustomerID ORDER BY OrderID) AS FirstOrderID

   3:  FROM Orders

   4:  WHERE CustomerID IN (1,2)

Lembre-se de que quando não especificado, o window frame default é “range between unbounded preceding and current row”. Ou seja, a consulta acima é igual à consulta abaixo.

   1:  SELECT OrderID, 

   2:         CustomerID,

   3:         FIRST_VALUE(OrderID) OVER(PARTITION BY CustomerID 

   4:                                   ORDER BY OrderID

   5:                                   ROWS BETWEEN UNBOUNDED PRECEDING 

   6:                                   AND CURRENT ROW) AS FirstOrderID

   7:    FROM Orders

   8:   WHERE CustomerID IN (1,2)

Uma ilustração de como o frame e a função FIRST_VALUE() funciona seria algo assim:

Na figura acima podemos observar que o primeiro valor (FIRST_VALUE) da linha 1 é 10643, para a segunda linha o valor continua sendo 10643 já que o frame que especifica a linha superior é sem precedentes (UNBOUNDED PRECEDING).

Last_Value()

A function LAST_VALUE() pode ser um pouco confusa, mas se entendermos o frame veremos que o que ela faz por “padrão” está correto.

É muito comum testar a function e não obter os resultados esperados, algumas pessoas até acharam que ela estava com problemas como podemos ver aqui.

Vamos ver o que a function retorna para o mesmo exemplo e dados da tabela de Orders.

   1:  SELECT OrderID, 

   2:         CustomerID,

   3:         LAST_VALUE(OrderID) OVER(PARTITION BY CustomerID ORDER BY OrderID) AS LastOrderID

   4:    FROM Orders

   5:   WHERE CustomerID IN (1,2)

Como podemos ver o resultado não foi o esperado, na verdade eu quero retornar o último pedido (OrderID) para cada CustomerID. E o SQL Server acabou retornando o mesmo valor de OrderID.

Vamos usar a mesma ilustração que fizemos com o FIRST_VALUE() para processar o LAST_VALUE(). Lembre-se de que não especifiquei o window frame, ou seja, ele esta usando o default.

Se eu deixar o padrão para o frame que diz qual é a linha inferior, ele vai usar current row. E isso não é o que queremos. Na verdade o correto seria usar um frame dizendo que o valor da linha inferior é unbounded following ou seja, seguinte ilimitado, neste caso o limite é até o fim da partição pois estou utilizando a clausula partition by.

   1:  SELECT OrderID, 

   2:         CustomerID,

   3:         LAST_VALUE(OrderID) OVER(PARTITION BY CustomerID 

   4:                                  ORDER BY OrderID

   5:                                  ROWS BETWEEN UNBOUNDED PRECEDING

   6:                                  AND UNBOUNDED FOLLOWING) AS LastOrderID

   7:    FROM Orders

   8:   WHERE CustomerID IN (1,2)

Agora conseguimos retornar os dados esperados. Vejamos como isso fica na nossa ilustração:

O window frame vai de unbounded preceding até unbounded following, ou seja, quando leio o ultimo valor (LAST_VALUE) para uma linha, a window contempla realmente o primeiro e último valor da minha partição.

Conclusão

Nos próximos artigos sobre windows functions vou continuar a explorar o window frame utilizando algumas variações interessantes.

Apesar de conseguir fazer muita coisa, ainda não temos suporte completo ao window frame, pois o Denali não suporta a clausula INTERVAL para especificar um frame temporal utilizando datas. Por ex:

• RANGE INTERVAL ‘nn’ DAY PRECEDING
• RANGE INTERVAL ‘nn’ SECONDS FOLLOWING
• RANGE INTERVAL ‘nn’ MONTH PRECEDING

That’s all folks.