Início > Denali, T-SQL, Windows Functions > Windows Functions – Parte 5

Windows Functions – Parte 5

Sliding Window

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

OVER (

[ <PARTITION BY clause> ]

[ <ORDER BY clause> ]

[ <ROW or RANGE clause> ]

)

clip_image002

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)

clip_image003

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:

clip_image004

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)

clip_image005

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:

clip_image007

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)

clip_image008

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.

clip_image010

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)

clip_image011

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

clip_image013

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.

Anúncios
  1. Andre
    25 de agosto de 2011 às 19:45

    Ótimo exemplo, Fabiano. Estava lendo sobre isso hoje no SQL Server Central.
    Fico querendo ver isso usado na prática (em relatórios de BI, por exemplo).
    Parabéns pelo novo blog!

    Atenciosamente,

    Andre Guerreiro Neto
    MCITP/MCTS SQL Server 2008
    http://www.novosis.com.br

  1. No trackbacks yet.

Deixe um comentário

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

Logotipo 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 )

Foto do Google+

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

Conectando a %s

%d blogueiros gostam disto: