Blog – Fabiano Neves Amorim

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