Arquivos

Archive for the ‘Windows Functions’ Category

Novo Artigle Simple Talk

24 de novembro de 2011 Deixe um comentário

Galera, acabou de sair meu novo artigo no Simple-Talk, novamente estou falando sobre windows functions no SQL Server 2012…

http://www.simple-talk.com/sql/learn-sql-server/window-functions-in-sql-server-part-2-the-frame/

Pra quem perdeu a primeira parte segue o link:

http://www.simple-talk.com/sql/learn-sql-server/window-functions-in-sql-server/

Votos com 5 estrelinhas são bem vindos Smile

Abraços

Material palestra PASSLatam 24 Horas, Windows Functions

20 de outubro de 2011 Deixe um comentário

Conforme combinado segue o link para download do material que usei na minha sessão do PASS24 horas.

https://skydrive.live.com/embedicon.aspx/Publica/PASS%2024%20Hours^J%20Windows%20Functions.zip?cid=52eff7477e74caa6&sc=documents

Abs.

24 horas PASS Latam–Evento online e gratuito

17 de outubro de 2011 Deixe um comentário

Galera, nessa semana acontecerá mais um evento organizado pelo PASS. O PASS LATAM 24 horas irá iniciar no dia 19 e vai até dia 20.

Serão 24 horas de palestras sobre SQL Server todas com foco na nova versão do SQL Server 2012.

O evento conta com a participação de vários brasileiros, eu novamente irei palestrar sobre Windows Functions.

Para ver a agenda com todas as palestras e fazer a inscrição basta seguir as instruções do seguinte link:

https://www323.livemeeting.com/lrs/8000181573/Registration.aspx?pageName=b7tqr85g08m9br8r

Opcionalmente você pode ver a grade no Blog no Nogare: http://www.diegonogare.net/blog/post/24hr-of-PASS-Latam-e28093-Evento-gratuito-em-04-e-05Out.aspx

Nos vemos por lá:

Abs.

Windows Functions – Parte 6

5 de setembro de 2011 Deixe um comentário

Windows Functions – Parte 5

24 de agosto de 2011 1 comentário

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.

Windows Functions – Parte 4

24 de agosto de 2011 Deixe um comentário
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

image

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

image

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

image

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
image
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
image

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

image

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
image
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

image

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.

Video, WebCast Denali + Windows Functions

24 de agosto de 2011 Deixe um comentário

Galera, segue o vídeo da minha sessão sobre Windows Functions e Denali…

Abraços

Denali + Windows Functions

Seguir

Obtenha todo post novo entregue na sua caixa de entrada.

Junte-se a 65 outros seguidores