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.

Windows Functions no SQL Server Denali

Vamos ver neste artigo como funcionam as Windows Functions que estarão disponíveis no SQL Server Denali.

As windows functions apresentadas abaixo estão disponíveis no CTP 3 do SQL Server Denali, você pode fazer o download para avaliação clicando no link abaixo:

https://www.microsoft.com/betaexperience/pd/SQLDCTP3CTA/enus/default.aspx

Base para testes

Para testar as windows functions iremos utilizar uma tabela de testes chamada Tab1. O código para criação da tabela é o seguinte:

USE TempDB
GO

IF OBJECT_ID('Tab1') IS NOT NULL
DROP TABLE Tab1
GO
CREATE TABLE Tab1 (Col1 Int)
GO
INSERT INTO Tab1 VALUES(5), (5), (3) , (1)
GO

LEAD()

A function LEAD é utilizada para ler o valor da próxima linha, ou linha de baixo. Quando a próxima linha não existir NULL é retornado.

Exemplo:

-- LEAD
SELECT Col1,
LEAD(Col1) OVER(ORDER BY Col1) AS "LEAD"
FROM Tab1

Como podemos observar a coluna LEAD retorna a próxima linha, no caso do último registro o valor NULL é retornado.

Por padrão a próxima linha é retornada, mas nós podemos alterar este comportamento para que função retorne o valor de N linhas seguintes, por exemplo:

-- LEAD
SELECT Col1,
LEAD(Col1, 2) OVER(ORDER BY Col1) AS "LEAD"
FROM Tab1

No resultado acima, solicitei que a função retornasse o valor de 2 linhas seguintes.

LAG()

A function LAG() é similar a function LEAD(), porém ao invés de retornar o próximo valor ela retorna o valor anterior. Por exemplo:

-- LAG
SELECT Col1,
LAG(Col1) OVER(ORDER BY Col1) AS "LAG()"
FROM Tab1

Podemos ver que o valor anterior foi retornado, quando a linha não contém valor anterior NULL é retornado.

Você pode estar se pensando que uma alternativa a function LAG() seria usar a function LEAD() com o valor do OffSet negativo certo? Ou seja, ao invés de pegar 1 valor pra frente pega -1.

Vejamos um exemplo:

SELECT Col1,
LEAD(Col1, -1) OVER(ORDER BY Col1) AS "LEAD() Como LAG()"
FROM Tab1

Msg 8730, Level 16, State 1, Line 1

Offset parameter for Lag and Lead functions cannot be a negative value.

Como podemos ver, não é possível usar valores negativos para as funções LEAD() e LAG().

FIRST_VALUE()

Como o nome praticamente diz FIRST_VALUE() retorna o primeiro valor de um frame. Por exemplo:

-- FIRST_VALUE
SELECT Col1,
FIRST_VALUE(Col1) OVER(ORDER BY Col1) AS "FIRST_VALUE()"
FROM Tab1

LAST_VALUE()

Como o nome praticamente diz LAST_VALUE() retorna o primeiro valor de um frame. Por exemplo:

-- LAST_VALUE
SELECT Col1,
LAST_VALUE(Col1) OVER(ORDER BY Col1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "LAST_VALUE()"
FROM Tab1

Para pegar o último valor da window acima precisei especificar qual era o frame, vou explicar melhor como isso funciona no próximo post.

PERCENT_RANK()

No primeiro artigo sobre windows functions mostrei o comportamento da function RANK(). A function PERCENT_RANK() é bem similar a function RANK() porém o resultado é o seguinte:

Exemplo:

-- PERCENT_RANK
SELECT Col1,
PERCENT_RANK() OVER(ORDER BY Col1) AS "PERCENT_RANK()"
FROM Tab1

O mesmo efeito era possível no SQL Server 2005, utilizando a seguinte fórmula:

• (RANK() – 1) / (NúmeroDeLinhas – 1)

Abaixo podemos visualizar um código para simular o uso da function PERCENT_RANK() no SQL Server 2005:

-- Fake PERCENT_RANK
SELECT Col1,
(RANK() OVER(ORDER BY Col1) - 1.) / ((SELECT COUNT(*) FROM Tab1) - 1.) AS "Fake PERCENT_RANK()"
FROM Tab1

CUME_DIST()

A function CUME_DIST() também é utilizada para calcular a posição de um valor em um rank. Vejamos um exemplo:

-- CUME_DIST()
SELECT Col1,
CUME_DIST() OVER(ORDER BY Col1) AS "CUME_DIST()"
FROM Tab1

O mesmo resultado pode ser visualizado utilizando a seguinte fórmula:

• COUNT(*) OVER (ORDER BY Col1) / COUNT(*) OVER ()

Conclusão

Ainda falta mostrar as functions de distribuition PERCENTILE_CONT e PERCENTILE_DISC, e explicar o que é e como usar o frame de uma window.

É isso ai pessoal, fique de olho para o próximo artigo sobre Windows Functions.