Pular para o conteúdo principal

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.

Postagens mais visitadas deste blog

Como gerar scripts para exportar dados no SQL Server 2008

Uma das tarefas mais comuns no trabalho com desenvolvimento de software que consome dados em bancos como o SQL Server 2008 é a necessidade de em algum momento precisarmos exportar os dados de um banco para outro. Quer seja para realizar testes ou fazer simulações existem várias maneiras de se fazer isto. Neste post eu quero demonstrar um recurso do SQL Server Management Studio (SSMS) que permite realizar esta tarefa rapidamente.Para os que estão acostumados a usar esta ferramenta, já devem saber que é possível gerar scripts para o schema e também transferir os dados entre dois bancos distintos. Isto pode ser feito se o SSMS puder conectar-se com as duas bases, de origem e destino. No exemplo que vou dar, o objetivo é gerar o script apenas para uma tabela do banco de dados de exemplo da Microsoft – Northwind.1. Iniciando o assistenteO assistente deve ser iniciado clicando com o botão direito do mouse sobre o banco onde se encontra a tabela a qual iremos gerar o script. Deve se clicar n…

Pivot dinâmico com SQL Server

Passo a passo para usar pivoteamento dinâmicoOs bancos de dados bem configurados e definidos armazenam os dados de forma a otimizar o acesso, evitando duplicidade e garantindo a integridade. Porém, em muitas situações isto pode dificultar a apresentação de forma adequada sendo necessário preparar os dados usando vários recursos entre os quais, fazer o pivoteamento.Se você não precisou ainda usar ou não sabe o que é consiste em transformar cada linha de uma determinada coluna em colunas de uma nova consulta.Assim, considere uma tabela que armazene as notas bimestrais de um boletim. Uma possível estrutura para esta tabela seria algo assim:ColunaTipo de dado/TamanhoDisciplinanvarchar(50)BimestreIntNotanumeric(5,2)Uma consulta select nesta tabela com alguns dados traria um resultado parecido com o abaixo:Porém pode ser que para apresentar estes dados em um relatório seja necessário transformar cada bimestre em uma coluna e agrupar as notas nestas colunas para que fique dessa forma:Isto po…

Desabilitando o auto commit no SSMS (SQL Server Management Studio)

(Ou, como prevenir desastres e manter o emprego a salvo…)Neste post vai uma pequena mas tremendamente útil dica para desabilitar o auto commit da aplicação SQL Server Management Studio (SSMS) que é usada por dez entre dez usuários do banco de dados SQL Server para fazer consultas, alterações e executar scripts no banco de dados. (Preferências à parte, realmente muita gente usa),A primeira e mais importante notícia é que, diferentemente da ferramenta do Oracle, este editor de scripts do SQL Server vem com o recurso de auto commit ativado por padrão, assim, qualquer instrução DML (alteração dos dados com update, insert e delete) ou DDL (alteração no banco como create, drop, alter, etc.) será imediatamente enviada ao banco e persistida.Isto pode ser altamente crítico pois se estiver executando as instruções em um banco de dados de produção não haverá muitas formas de desfazer se é que haverá.Inicialmente, pode se evitar muitos acidades executando estas instruções dentro de um bloco BEGIN…