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.

Pessoal meu amigo Felipe Ferreira está organizando uma série de WebCasts online para o Virtual PASS BR, se você não conhece o grupo Virtual PASS, não sabe o que está perdendo, filiados à PASS – Professional Association for SQL Server eles já tem 300 artigos e vídeos técnicos sobre SQL Server, vale a pena investir um tempo por lá…

Vou palestrar no primeiro dia(01/08/11) no horário das 21:00 as 22:00. Vou falar sobre Windows Functions com a palestra tema “Era uma vez um reino de trevas… Denali + Windows Functions… e viveram felizes para sempre!” portanto nos vemos por lá…

Segue o link para inscrição:

http://bit.ly/kQY8Zl

Abraços

Galera, ontem eu escrevi sobre windows functions e até cheguei a mostrar um exemplo como running aggregations podem ser facilmente executadas utilizando a clausula OVER() + ORDER BY.

Desde o SQL Server 2005 temos suporte a clausula OVER(), porém ela não esta totalmente implementada, na verdade o que temos é o seguinte:

A tabela acima foi criada com base no artigo que o Itzik escreveu em 2007, quando ele solicitou a inclusão das Windows Functions no SQL Server 2008, coisa que hoje sabemos que não aconteceu, você pode ler o artigo aqui.

Podemos observar que a clausula ORDER BY não é suportada nas functions de agregação.

Diferença de Performance

Para ilustrar o quão importante e poderoso é o suporte a Windows Functions vamos fazer um comparativo de performance entre SQL Server e PostGreSQL.

Ok eu sei que a comparação vai ser injusta já que o SQL Server não tem suporte a Windows Functions e o PostGreSQL tem (me sinto tão mal em dizer isso 😦 ). Mas mesmo assim o propósito é testar a função e não o banco de dados.

Para simular este teste isso criei uma tabela chamada tblLancamentos e inseri 200 mil linhas nela, depois criei a mesma tabela no PostgreSQL e inserir os mesmo registros nela. Segue abaixo o script que usei para fazer isso.

SET NOCOUNT ON;
GO

IF OBJECT_ID('tblLancamentos') IS NOT NULL
DROP TABLE tblLancamentos
GO

-- Tabela de Lançamentos para exemplificar o Subtotal
CREATE TABLE tblLancamentos (ID_Conta Integer,
DataLancamento Date,
ValorLancamento Float)
GO

CREATE CLUSTERED INDEX ixC ON tblLancamentos (ID_Conta, DataLancamento)
GO

-- Insere os registros
INSERT INTO tblLancamentos (ID_Conta, DataLancamento, ValorLancamento)
SELECT (ABS(CHECKSUM(NEWID())) / 10000000), CONVERT(VarChar, GetDate() - (ABS(CHECKSUM(NEWID())) / 1000000.),112), (ABS(CHECKSUM(NEWID())) / 1000000.)
GO 100000

INSERT INTO tblLancamentos (ID_Conta, DataLancamento, ValorLancamento)
SELECT (ABS(CHECKSUM(NEWID())) / 10000000), CONVERT(VarChar, GetDate() - (ABS(CHECKSUM(NEWID())) / 1000000.),112), (ABS(CHECKSUM(NEWID())) / 1000000.) * -1
GO 100000

Abaixo o script para inserir os dados no PostGreSQL:

SELECT IDENTITY(int, 1,1) ID,
'INSERT INTO tblLancamentos (ID_Conta, DataLancamento, ValorLancamento) ' +
'VALUES('+''''''+ CONVERT(Varchar, ID_COnta) +''''',' +
''''''+ CONVERT(Varchar, DataLancamento, 112) +''''',' +
''''''+ CONVERT(Varchar, ValorLancamento) +'''''' + ')' AS St
INTO #TMP
FROM tblLancamentos
GO
CREATE CLUSTERED INDEX IX ON #TMP (ID)
GO
declare @i Int = 0, @st Varchar(MAX)
select top 1 @i = id, @st = st
from #tmp
where id > @i
order by id
while @@rowCount > 0
begin
Set @st = 'EXEC (''' + @st + ''') AT LINKED_EDB'
print @I
exec (@st)
select top 1 @i = id, @st = st
from #tmp
where id > @i
order by id
end
GO

Primeiro para que fiquemos felizes, vamos comparar as mesmas soluções em ambos os bancos e ver que o SQL Server é MELHOR 😉

SELECT a.ID_Conta,
a.DataLancamento,
a.ValorLancamento,
(SELECT SUM(ValorLancamento)
FROM tblLancamentos AS b
WHERE a.ID_Conta = b.ID_Conta
AND a.DataLancamento >= b.DataLancamento) AS Saldo
FROM tblLancamentos AS a
ORDER BY a.ID_Conta, a.DataLancamento, a.ValorLancamento
GO

Podemos observar que o SQL Server levou 14 segundos para rodar a consulta:

No PostgreSQL o a consulta foi retornada em 35 segundos (bora arredondar pra cima né galera? ;-))

Agora a parte triste, vejamos o tempo ao utilizar a clausula OVER() com ORDER BY.

Para começo de conversa a consulta é muito mais simples:

SELECT ID_Conta,
DataLancamento,
ValorLancamento,
SUM(ValorLancamento) OVER(ORDER BY DataLancamento ASC) AS Saldo
FROM tblLancamentos AS a
ORDER BY a.ID_Conta, a.DataLancamento, a.ValorLancamento

O tempo no banco gratuito chamado ProsgreSQL? Bem… é… ummm, quer dizer… onde? quem? do que que eu estava falando mesmo? Aaaa deixa pra lá vai…

Galera, me desculpa mas agora vou precisar sair porque preciso levar minha vó na aula Jiu-jitsu, ela tem campeonato semana que vem e não podemos dar mole.

Abraços…

Para uma versão MUITO melhor que a formatação daqui do Blog, baixe o PDF do artigo aqui no meu SkyDrive:

https://skydrive.live.com/#!/?cid=52eff7477e74caa6&sc=documents&uc=1&id=52EFF7477E74CAA6!224

Pessoal, vou dar inicio a uma série de posts sobre Windows Functions no SQL Server, aqui vamos nós.

Windows Functions

Windows functions são um subconjunto de funções chamadas de “set functions” (funções de conjunto), ou seja, funções que são aplicadas em um conjunto de linhas. A palavra window (janela) se refere a um conjunto de linhas que a função trabalha.

Como sabemos SQL é uma linguagem utilizada por vários bancos de dados, no SQL Server temos uma variação do SQL que chamamos de Transact-SQL ou apenas T-SQL.

Windows Functions são funções que foram introduzidas no padrão SQL:2003 controlado pela ISO e mais detalhadas no padrão SQL:2008. Já há algum tempo vários bancos de dados possuem suporte a Windows Functions, bem como: Oracle, Teradata, DB2 e até bancos gratuitos como PostgreSQL e Firebird (ainda tentando implementar).

O interessante nas Windows functions é que com elas, conseguimos acessar os dados de detalhes de um agrupamento. Para exemplificar isso, vamos imaginar o seguinte cenário:

   1:  IF OBJECT_ID('TestAggregacao') IS NOT NULL

   2:    DROP TABLE TestAggregacao

   3:  GO

   4:  

   5:  CREATE TABLE TestAggregacao (ID Int, Valor Numeric(18,2))

   6:  GO

   7:   

   8:  INSERT INTO TestAggregacao (ID, Valor) 

   9:  VALUES(1, 50.3), (1, 123.3), (1, 132.9),

  10:        (2, 50.3), (2, 123.3), (2, 132.9), (2, 88.9),

  11:        (3, 50.3), (3, 123.3);

  12:  GO

A tabela contêm os seguintes dados:

   1:  SELECT * FROM TestAggregacao

Se agruparmos os dados por ID somando o Valor teríamos a seguinte consulta e resultado:

   1:  SELECT ID, SUM(Valor)

   2:    FROM TestAggregacao

   3:   GROUP BY ID;

   4:  GO

Abaixo temos uma representação das partições de dados ou sets onde a function de agregação (SUM) foi aplicada.

No azul temos a partição 1, verde temos a partição 2, e vermelho a partição 3. Como aplicamos o a função de agregação, perdemos o acesso a partição que tem os detalhes dos dados.

Por exemplo, digamos que eu tenha que escrever uma consulta que retorne o total de venda, a média de venda, e a quantidade de vendas por ID, e ainda visualizar os valores vendidos, uma tentativa seria escrever algo assim:

   1:  SELECT ID,

   2:         Valor,

   3:         SUM(Valor) AS "Soma",

   4:         AVG(Valor) AS "Média",

   5:         COUNT(Valor) AS "Quantidade"

   6:    FROM TestAggregacao

   7:   GROUP BY ID;

   8:  GO

Msg 8120, Level 16, State 1, Line 2

Column ‘TestAggregacao.Valor’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

A consulta acima não é válida, pois a partir do momento que agregamos os dados, não temos mais acesso aos detalhes.

A alternativa mais comum que vejo por ai é agregar os valores em uma subquery e depois fazer o join com a tabela novamente, algo parecido com o seguinte:

   1:  SELECT TestAggregacao.ID,

   2:         TestAggregacao.Valor,

   3:         TabSum."Soma",

   4:         TabAvg."Média",

   5:         TabCount."Quantidade"

   6:    FROM TestAggregacao

   7:   INNER JOIN (SELECT ID, SUM(Valor) AS "Soma"

   8:                 FROM TestAggregacao

   9:                GROUP BY ID) AS TabSum

  10:      ON TabSum.ID = TestAggregacao.ID

  11:   INNER JOIN (SELECT ID, AVG(Valor) AS "Média"

  12:                 FROM TestAggregacao

  13:                GROUP BY ID) AS TabAvg

  14:      ON TabAvg.ID = TestAggregacao.ID

  15:   INNER JOIN (SELECT ID, COUNT(Valor) AS "Quantidade"

  16:                 FROM TestAggregacao

  17:                GROUP BY ID) AS TabCount

  18:      ON TabCount.ID = TestAggregacao.ID

  19:  GO

Uma alternativa bem interessante é usar a clausula OVER() que faz é implementada em funções de agregação e permite que os dados dos detalhes agregados sejam acessados. Por exemplo:

   1:  SELECT ID,

   2:         Valor,

   3:         SUM(Valor) OVER() AS "Soma",

   4:         AVG(Valor) OVER() AS "Média",

   5:         COUNT(Valor) OVER() AS "Quantidade"

   6:    FROM TestAggregacao

   7:  GO

Utilizando a clausula OVER() junto a função de agregação (SUM, AVG e COUNT) conseguimos acessar os detalhes que foram agrupados, porém neste caso, não conseguimos o resultado desejado. Na verdade queremos que os agrupamentos sejam particionados por ID. Para isso podemos especificar a clausula PARTITION BY, por exemplo:

   1:  SELECT ID,

   2:         Valor,

   3:         SUM(Valor) OVER(PARTITION BY ID) AS "Soma",

   4:         AVG(Valor) OVER(PARTITION BY ID) AS "Média",

   5:         COUNT(Valor) OVER(PARTITION BY ID) AS "Quantidade"

   6:    FROM TestAggregacao

   7:  GO

Agora temos o mesmo resultado da consulta utilizando subqueries, porém temos uma expressão muito mais simples e elegante.

Na imagem abaixo, podemos observar que mesmo estando com os dados agregados conseguimos acessar as linhas das partições.

Pensando Set Based

O ponto chave em relação a Windows Functions é que elas operam em um Set. O SQL Server nunca foi muito bom em processar dados linha a linha, por isso que você ouve tanto que Cursores são ruins para performance, e que eles devem ser evitados. O processo linha a linha é extremamente ineficiente no SQL Server, cada instrução tem que ser controlada pelo SQL Server, e isso requer um custo.

Um exemplo interessante para mostrar como o SQL Server é extremamente ruim para fazer processamento linha a linha, é um loop de 100 milhões de linhas que no SQL Server requer uma média de 45 segundos para rodar, contra menos de 100 milissegundos para rodar o mesmo loop em um aplicativo win32.

Vejamos um exemplo:

Código T-SQL:

   1:  DECLARE @i Int = 0, @Tempo Time(3) = GetDate()

   2:  WHILE @i < 100000000

   3:  BEGIN

   4:    SET @i += 1;

   5:  END

   6:  

   7:  SELECT CONVERT(Time(3), GetDate() - @Tempo) AS "Tempo Gasto no Loop"

Código Delphi:

   1:  procedure TForm1.Button1Click(Sender: TObject);

   2:  Var

   3:    i : Integer;

   4:    Tempo : TDateTime;

   5:  begin

   6:    i := 0;

   7:    Tempo := Now();

   8:    while i < 100000000 do

   9:    begin

  10:      inc(i);

  11:    end;

  12:    ShowMessage(FormatDateTime('hh:mm:ss.zzz', Tempo - Now()))

  13:  end;

Fica uma dica, processamento em sets (conjunto de linhas) é MUITO melhor do que processamento linha a linha. Tente sempre evitar uso de cursores e loops no SQL Server.

Uma vez ouvi o Itzik Ben-Gan dizer, “não existe loop ou cursor que não rode em um código set-based equivalente, você é que não sabe como fazer”. É uma expressão forte, mas quem tem peito pra dizer pra ele que não é bem assim? . Ele pode dizer isso .

Windows Functions no SQL Server

Desde o SQL Server 2005 temos suporte a algumas windows functions são elas: Row_Number, Rank, Dense_Rank e NTILE. Neste artigo inicial veremos como estas funções funcionam, e como elas podem nos ajudar na construção de códigos T-SQL extremamente eficientes.

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:

   1:  USE TempDB

   2:  GO

   3:  IF OBJECT_ID('Tab1') IS NOT NULL

   4:    DROP TABLE Tab1

   5:  GO

   6:  CREATE TABLE Tab1 (Col1 Int)

   7:  GO

   8:   

   9:  INSERT INTO Tab1 VALUES(5), (5), (3) , (1)

  10:  GO

Row_Number()

A function ROW_NUMBER é utilizada para gerar uma sequência de números baseado em um set, em outras palavras, retorna o número de cada linha dentro do set.

Exemplo:

   1:  -- RowNumber

   2:  SELECT Col1, 

   3:         ROW_NUMBER() OVER(ORDER BY Col1 DESC) AS "ROW_NUMBER()"

   4:    FROM Tab1

A coluna chamada ROW_NUMBER() contêm um sequencial de gerado por Col1 em ordem descendente. A clausula OVER(ORDER BY Col1 DESC) é necessária para informar em qual ordem o ROW_NUMBER deverá ser gerado.

Rank() e Dense_Rank()

Retorno o a posição no ranking para cada linha dentro da partição. O ranking é da linha é calculado por 1 mais o número de ranks que vem antes da linha em questão.

É importante mencionar que a função RANK retorna o resultado com um GAP, e a função DENSE_RANK não gera o GAP, para entender esse GAP melhor vejamos um exemplo:

   1:  -- Rank

   2:  SELECT Col1, 

   3:         RANK() OVER(ORDER BY Col1 DESC) AS "RANK()"

   4:    FROM Tab1

   5:  GO

   6:   

   7:  -- Dense_Rank

   8:  SELECT Col1, 

   9:         DENSE_RANK() OVER(ORDER BY Col1 DESC) AS "DENSE_RANK"

  10:    FROM Tab1

Repare que no resultado da function RANK, temos os valores 1,1,3 e 4. Como o valor Col1 = “5” está duplicado, temos a mesma posição para ele no ranking, porém quando o valor 3 é calculado, a posição dele não é a 2, pois a posição “2” já é do 5, neste caso um GAP é gerado e a function atribui o próximo valor para o rank, que neste caso é o valor 3.

A diferença pode ser claramente observada no resultado da function DENSE_RANK() onde não temos GAPs e os valores são seguidos sequencialmente.

NTILE()

A function NTILE distribui de uma partição ordenada dos dados em um número específico de “buckets” ou grupos. Os grupos são numerados iniciando por 1.

Vejamos um exemplo:

   1:  -- NTILE

   2:  SELECT Col1, 

   3:         NTILE(3) OVER(ORDER BY Col1 DESC) AS "NTILE(3)"

   4:    FROM Tab1

4 linhas divididas por 3 é igual a 1, a linha que sobra é adicionada no grupo inicial. Vejamos um exemplo onde não temos sobra:

   1:  -- NTILE

   2:  SELECT Col1, 

   3:         NTILE(2) OVER(ORDER BY Col1 DESC) AS "NTILE(2)"

   4:    FROM Tab1

Testando o poder das Windows Functions

Windows functions podem ser utilizadas em milhares de cenários, vejamos alguns exemplos.

Exemplo 1

Digamos que eu precise construir uma consulta para identificar quem é o funcionário que ganha mais do que a média de ganho no seu departamento.

Vamos criar uma base para testes:

   1:  IF OBJECT_ID('Departamentos') IS NOT NULL

   2:    DROP TABLE Departamentos

   3:  GO

   4:  CREATE TABLE Departamentos (ID      Int IDENTITY(1,1) PRIMARY KEY,

   5:                              Nome_Dep VarChar(200))

   6:  GO

   7:   

   8:  INSERT INTO Departamentos(Nome_Dep) 

   9:  VALUES('Vendas'), ('TI'), ('Recursos Humanos')

  10:  GO

  11:   

  12:  IF OBJECT_ID('Funcionarios') IS NOT NULL

  13:    DROP TABLE Funcionarios

  14:  GO

  15:  CREATE TABLE Funcionarios (ID      Int IDENTITY(1,1) PRIMARY KEY,

  16:                             ID_Dep  Int,

  17:                             Nome    VarChar(200),

  18:                             Salario Numeric(18,2))

  19:  GO

  20:  

  21:  INSERT INTO Funcionarios (ID_Dep, Nome, Salario) 

  22:  VALUES(1, 'Fabiano', 2000), (1, 'Amorim', 2500), (1, 'Diego', 9000),

  23:        (2, 'Felipe', 2000), (2, 'Ferreira', 2500), (2, 'Nogare', 11999),

  24:        (3, 'Laerte', 5000), (3, 'Luciano', 23500), (3, 'Zavaschi', 13999)

  25:  GO

Os dados da tabela de Funcionarios são o seguinte:

Para escrever a consulta que mencionei eu poderia fazer o seguinte:

   1:  SELECT Departamentos.Nome_Dep,  

   2:         Funcionarios.Nome AS Funcionario,

   3:         Funcionarios.Salario,

   4:         AVG(Funcionarios.Salario) OVER(PARTITION BY Departamentos.Nome_Dep) "Média por Departamento",

   5:         Salario - AVG(Funcionarios.Salario) OVER(PARTITION BY Departamentos.Nome_Dep) "Diferença de Salário"

   6:    FROM Funcionarios

   7:   INNER JOIN Departamentos

   8:      ON Funcionarios.ID_Dep = Departamentos.ID

   9:   ORDER BY 5 DESC

Como podemos observar os Funcionários Luciano, Nogare e Diego ganham bem acima da média de seu departamento.

Utilizando a clausula OVER e particionando os dados por Departamento, consegui acessar a média e os detalhes do salário.

Exemplo 2

Outro cenário, BEM problemático é o calculo saldo de conta corrente, o famoso running totals. Vou utilizar como exemplo a tabela que o meu amigo MVP Gustavo Maia criou neste post para mostrar várias dicas de T-SQL:

   1:  IF OBJECT_ID('tblLancamentos') IS NOT NULL

   2:    DROP TABLE tblLancamentos

   3:  GO

   4:   

   5:  -- Tabela de Lançamentos para exemplificar o Subtotal

   6:  CREATE TABLE tblLancamentos (DataLancamento  Date,

   7:                               ValorLancamento Float)

   8:  GO

   9:   

  10:  -- Insere os registros

  11:  INSERT INTO tblLancamentos VALUES ('20080623',100)

  12:  INSERT INTO tblLancamentos VALUES ('20080624',-250)

  13:  INSERT INTO tblLancamentos VALUES ('20080625',380)

  14:  INSERT INTO tblLancamentos VALUES ('20080626',200)

  15:  INSERT INTO tblLancamentos VALUES ('20080627',-300)

  16:  GO

Uma forma para calcular o saldo atual é o seguinte:

   1:  SELECT DataLancamento, 

   2:         ValorLancamento,

   3:        (SELECT SUM(ValorLancamento) 

   4:           FROM tblLancamentos

   5:          WHERE DataLancamento <= QE.DataLancamento) AS Saldo

   6:  FROM tblLancamentos AS QE

Desta forma, fazemos o join com a própria tabela e calculamos o total. Outra alternativa muito melhor seria a seguinte:

EXEC ('SELECT DataLancamento, 
              ValorLancamento,
              SUM(ValorLancamento) OVER(ORDER BY DataLancamento) AS Saldo
       FROM tblLancamentos AS QE') AT LINKED_EDB

Na consulta acima estou utilizando a clausula OVER junto à função de agregação SUM, e solicitando que o calculo seja feito com base na ordem de DataLancamento.

Exemplo 3

Outro exemplo bem interessante é o seguinte, digamos que eu queira identificar o período que fiquei sem vender em um determinado mês baseado nas vendas feitas no mês, por ex:

   1:  USE TempDB

   2:  GO

   3:  IF OBJECT_ID('Pedidos') IS NOT NULL

   4:    DROP TABLE Pedidos

   5:  GO

   6:  CREATE TABLE Pedidos (Pedido_ID Integer, Data_Pedido Date)

   7:  GO

   8:  INSERT INTO Pedidos (Pedido_ID, Data_Pedido) VALUES('10369','19961202')

   9:  INSERT INTO Pedidos (Pedido_ID, Data_Pedido) VALUES('10371','19961203')

  10:  INSERT INTO Pedidos (Pedido_ID, Data_Pedido) VALUES('10372','19961204')

  11:  INSERT INTO Pedidos (Pedido_ID, Data_Pedido) VALUES('10374','19961205')

  12:  INSERT INTO Pedidos (Pedido_ID, Data_Pedido) VALUES('10375','19961206')

  13:  INSERT INTO Pedidos (Pedido_ID, Data_Pedido) VALUES('10377','19961209')

  14:  INSERT INTO Pedidos (Pedido_ID, Data_Pedido) VALUES('10378','19961210')

  15:  INSERT INTO Pedidos (Pedido_ID, Data_Pedido) VALUES('10379','19961211')

  16:  INSERT INTO Pedidos (Pedido_ID, Data_Pedido) VALUES('10381','19961212')

  17:  INSERT INTO Pedidos (Pedido_ID, Data_Pedido) VALUES('10382','19961213')

  18:  INSERT INTO Pedidos (Pedido_ID, Data_Pedido) VALUES('10384','19961216')

  19:  INSERT INTO Pedidos (Pedido_ID, Data_Pedido) VALUES('10385','19961217')

  20:  INSERT INTO Pedidos (Pedido_ID, Data_Pedido) VALUES('10387','19961218')

  21:  INSERT INTO Pedidos (Pedido_ID, Data_Pedido) VALUES('10388','19961219')

  22:  INSERT INTO Pedidos (Pedido_ID, Data_Pedido) VALUES('10389','19961220')

  23:  INSERT INTO Pedidos (Pedido_ID, Data_Pedido) VALUES('10391','19961223')

  24:  INSERT INTO Pedidos (Pedido_ID, Data_Pedido) VALUES('10392','19961224')

  25:  INSERT INTO Pedidos (Pedido_ID, Data_Pedido) VALUES('10394','19961225')

  26:  INSERT INTO Pedidos (Pedido_ID, Data_Pedido) VALUES('10395','19961226')

  27:  INSERT INTO Pedidos (Pedido_ID, Data_Pedido) VALUES('10397','19961227')

  28:  INSERT INTO Pedidos (Pedido_ID, Data_Pedido) VALUES('10398','19961230')

  29:  INSERT INTO Pedidos (Pedido_ID, Data_Pedido) VALUES('10399','19961231')

  30:  GO

  31:   

  32:  SELECT * FROM Pedidos

Como podemos observar, temos vários períodos onde não temos venda, um comando T-SQL para retornar o período desejado pode ser o seguinte:

   1:  WITH TempCTE

   2:  AS

   3:  (

   4:    SELECT Pedido_ID,

   5:           Data_Pedido AS Data_Pedido,

   6:           ROW_NUMBER() OVER(ORDER BY Data_Pedido) Rn

   7:      FROM Pedidos

   8:  )

   9:  SELECT DISTINCT 

  10:         DateAdd(d, 1, LinhaAtual.Data_Pedido) AS "PeriodoInicial",

  11:         DateAdd(d, -1, ProximaLinha.Data_Pedido) AS "PeriodoFinal"

  12:    FROM TempCTE AS LinhaAtual

  13:   INNER JOIN TempCTE AS ProximaLinha

  14:      ON LinhaAtual.rn + 1 = ProximaLinha.rn 

  15:  WHERE DateDiff(d, LinhaAtual.Data_Pedido, ProximaLinha.Data_Pedido) > 1

A consulta acima é bem complexa, mas retorna os dados desejados sem necessidade de tabelas auxiliares ou cursores.

A grande dificuldade é em conseguir pegar a linha seguinte a linha atual, para conseguir fazer isso tive que fazer um join com a própria tabela, lendo o ID subsequente (LinhaAtual.rn + 1) ao atual.

Opcionalmente poderíamos escrever ela assim:

EXEC ('WITH CTE_Temp
       AS
       (
       SELECT CASE 
                WHEN Data_Pedido + 1 = LEAD(Data_Pedido) OVER(ORDER BY Data_Pedido) THEN ''Sequencial''
                ELSE ''Não Sequencial''
              END AS Col1,
              LEAD(Data_Pedido) OVER(ORDER BY Data_Pedido) AS ProxData_Pedido, 
              * 
         FROM Pedidos
       )
       SELECT Data_Pedido + 1 AS PeriodoInicial, 
              ProxData_Pedido-1 AS PeriodoFinal
         FROM CTE_Temp
        WHERE Col1 = ''Não Sequencial''
          AND ProxData_Pedido-1 IS NOT NULL') AT LINKED_EDB

A consulta acima utiliza a Windows function LEAD para ler a próxima linha baseado na ordem de Data_Pedido.

Revelando o segredo

Como vocês devem ter percebido, alguns códigos que usei, como o uso da clausula OVER com ORDER BY e a function LEAD não existem no SQL Server.

Ai é que esta o “tchan da coisa”, no Denali (Próxima versão do SQL Server) teremos acesso a essas windows functions e muitas outras que eu irei mostrar nos próximos artigos.

Como:

• PERCENT_RANK

• LEAD

• LAG

• CUME_DIST

• FIRST_VALUE

• LAST_VALUE

• NTH_VALUE

• CUMULLATIVE AGGREGATES

Você pode estar se perguntando, como eu fiz para rodar e testar essas functions de dentro do SQL Server?

Na verdade criei um Linked Server no SQL para acessar e rodar os códigos no PostgreSQL que esta instalado na minha máquina.

É isso ai pessoal, espero que tenham gostado, e até o próximo artigo sobre Windows Functions.

Nos próximos posts veremos algumas novidades