terça-feira, 11 de outubro de 2011

Novas funções do SQL Server Denali

Alguns dos novos recursos encontrados no SQL Server Denali são as funções Lead e Lag, a seguir uma breve apresentação das duas.

LEAD
                Esta função retorna o conteúdo de uma coluna existente no próximo registro da tabela ou de outro registro subsequente.
                Considerando uma tabela com totais de salários de uma empresa mês a mês, conforme exemplo abaixo:
Create Table TotalSalarios
       (MesAno Datetime not null primary key,
        TotalSalario  decimal(11,2));

Insert into TotalSalarios Values('01-jan-2011', 89500),
                                ('01-feb-2011', 91200),
                                ('01-mar-2011', 93200),
                                ('01-apr-2011', 97200),
                                ('01-may-2011', 101250),
                                ('01-jun-2011', 103000),
                                ('01-jul-2011', 103000),
                                ('01-aug-2011', 105020);

Sintaxe:

Lead(Expressão, Offset, default) Over ([partition_by_clause] order_by_clause)

Expressão:   Valor que sera retornado pela função.
Offset:      Número de linhas para frente de onde será retornado o valor.
Default:     Valor que deverá ser retornado quando o a expressão for nula.
Partition_by_Clause: Critério de agrupamento que deverá ser utilizado pelo SQL.
Order_by_clause:     Ordem que deverá ser aplicada antes da geração da função.

                Abaixo exemplo para apresentar o valor do registro atual e do próximo em uma mesma consulta, sem utilizar uma subquery, opção nas versões anteriores do SQL Server:
Select MesAno, TotalSalario,
       Lead(TotalSalario ,1,totalsalario) over (order by mesano) as TotalProxMes
  from TotalSalarios

Resultado

MesAno
TotalSalario
TotalProxMes
2011-01-01
89500.00
91200.00
2011-02-01
91200.00
93200.00
2011-03-01
93200.00
97200.00
2011-04-01
97200.00
101250.00
2011-05-01
101250.00
103000.00
2011-06-01
103000.00
103000.00
2011-07-01
103000.00
105020.00
2011-08-01
105020.00
105020.00

LAG
                Similar à função anterior a LAG retorna valores de linhas anteriores.

Sintaxe:

Lag(Expressão, Offset, default) Over ([partition_by_clause] order_by_clause)
      
Expressão:   Valor que sera retornado pela função.
Offset:      Número de linhas para trás de onde será retornado o valor.
Default:     Valor que deverá ser retornado quando o a expressão for nula.
Partition_by_Clause: Critério de agrupamento que deverá ser utilizado pelo SQL.
Order_by_clause:     Ordem que deverá ser aplicada antes da geração da função.
 
Abaixo exemplo para apresentar o valor do registro atual, o valor do mês anterior e o percentual da variação de valor
 
Select MesAno, TotalSalario,
       Lag(TotalSalario ,1,totalsalario) over (order by mesano) as TotalMesAnt,
       Round(TotalSalario * 100 / Lag(TotalSalario ,1,totalsalario)
             over (order by mesano),2) as IndiceVariacao
  from TotalSalarios
 
Resultado

MesAno
TotalSalario
TotalMesAnt
IndiceVariacao
2011-01-01
89500.00
89500.00
100.00000000000000
2011-02-01
91200.00
89500.00
101.90000000000000
2011-03-01
93200.00
91200.00
102.19000000000000
2011-04-01
97200.00
93200.00
104.29000000000000
2011-05-01
101250.00
97200.00
104.17000000000000
2011-06-01
103000.00
101250.00
101.73000000000000
2011-07-01
103000.00
103000.00
100.00000000000000
2011-08-01
105020.00
103000.00
101.96000000000000