Funções analíticas no SQL Server–Parte 1-LAG

Funções analíticas são muito usadas em aplicações de B.I. e oferecem ótimos atalhos para realizar consultas complexas simplificando em muito as instruções SQL que são escritas e também diminuindo a utilização de subconsultas.

Em recente aula tive contato com as funções analíticas usadas no ORACLE e fiquei impressionado e ao mesmo tempo curioso para saber como seriam as funções equivalentes no SQL Server, já que é o banco com o qual eu mais trabalho.

Para a minha satisfação existe funções equivalentes no SQL Server ainda que com algumas diferenças.

Se você quiser conhecer um pouco mais sobre o assunto sugiro os seguintes links:

  • Funções analíticas – Descrevendo o seu funcionamento no SQL Server e com links para as principais.
  • Função LAG – Descreve o funcionamento da função LAG que é demonstrada neste post.

O propósito da função LAG

A função LAG facilita a tarefa de colocar na linha atual de um resultado de uma consulta o valor de uma consulta correspondente à linha imediatamente anterior. Para detalhes sobre os parâmetros e outros exemplos consulte o link que coloquei acima.

Neste post eu quero mostrar uma utilização que possa servir de base para considerar capacidades da função LAG.

Para executar o exemplo deste post estou usando o banco de dados de demonstração AdventureWorks2012 da Microsoft que pode ser obtido aqui. Este banco é para o SQL Server 2012. Eu estou trabalhando com uma versão Express deste banco. Note que esta e outras funções analíticas só estão disponíveis a partir da versão 2012 do SQL Server.

Neste banco existe a tabela SalesOrderHeader. Ela está vinculada com o schema Sales que neste banco de dados agrupa tabelas vinculadas com vendas dentro do banco.

A estrutura desta tabela contém vários campos incluindo a data da venda e o subtotal da venda. Entre os muitos outros campos estão incluídos a revisão do registro, data de envio, status,  e muitos outros que não são interessantes para o exemplo.

Para deixar bastante simples vou centralizar em dois campos:

  1. OrderDate – correspondente à data do registro da venda
  2. SubTotal – somatória dos itens que estão armazenados na tabela Sales.SalesOrderDetail.

A consulta

O objetivo é ter um comparativo da evolução da somatória dos produtos nas vendas ano a ano mostrando o ano de referência, a somatória do ano anterior e a somatória do ano atual.

Esta consulta fica como abaixo:

SELECT YEAR(OrderDate) [Year]
    ,LAG(SUM(SubTotal),1,0) OVER (ORDER BY YEAR(OrderDate)) AS SalesInPreviousYear
    ,SUM(SubTotal) AS SalesInCurrentYear
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)

Observe que não foi necessário o uso de subconsultas.


Inicialmente a consulta extrai o ano da venda para poder agrupar a somatória do subtotal que está armazenado. Este campo recebe um alias para sua representação no resultado final.


A linha seguinte é onde é feito o uso da função LAG. Neste exemplo ela está sendo aplicada sobre a somatória da coluna SubTotal. O segundo argumento da função indica para trazer apenas o resultado imediatamente inferior para obter apenas a somatória das vendas do ano anterrior. No terceiro parâmetro é informado que, caso o resultado seja nulo, a função deve devolver o valor zero.


A palavra reservada OVER faz com que a função use a ordenação pela coluna OrderDate que teve o ano extraído para poder gerar os dados agrupados pelo ano. É dentro desta cláusula que a função LAG trabalha considerando como a janela para obter o seu resultado.


Indo em frente, para poder obter a soma da coluna SubTotal para o registro do ano atual é usada a função SUM.


Finalizando a instrução SQL segue o andamento normal recebendo o nome da tabela que irá usar para obter os dados e executando o agrupamento por causa da função SUM que foi usada.


O resultado desta consulta deve ficar parecido com o que está abaixo:


MSSQL-LAG-1


Como é esperado, o conteúdo da coluna SalesInPreviousYear mostra o conteúdo da coluna SalesInCurrentYear do registro anterior. No caso da primeira linha, este é igual a zero pois não há linha anterior.


Este exemplo bem simples serve como ponto de partida para desdobramentos desta função. Note que a função LAG foi aplicada sobre uma função de agregação.


É possível isto e muito mais além de trabalhar com joins e views.


Em breve coloco mais dicas sobre as funções analíticas no SQL Server.


Até a próxima.