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:
- OrderDate – correspondente à data do registro da venda
- 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:
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.