Pular para o conteúdo principal

Funções analíticas no SQL Server–Parte 2–LEAD (Plus: CTE)

Aplicação prática para funções analíticas e Common Table Expressions (CTE)

Dando seguimento na proposta de mostrar as funções analíticas no MSSQL neste post será apresentada uma aplicação prática para a função LEAD.

É recomendável verificar a primeira parte – função LAG – deste blog para poder ambientar-se no assunto.

Como assunto adicional será apresentado o recurso Common Table Expressions (CTE) durante a elaboração da aplicação prática.

A função LEAD age de forma oposta a LAG, ou seja, enquanto esta última traz resultados de um subconjunto anterior a um critério que seja passado, LEAD mostra a soma (ou média, ou agrupamento desejado) para um subconjunto posterior ao da linha atual.

Considere a necessidade de se mostrar em uma consulta a soma das vendas por ano onde se deseje exibir o resultado de um ano e do ano posterior, para, por exemplo, se calcular a evolução percentual destas, LEAD vai possibilitar criar esta consulta sem a necessidade de usar sub consultas.

A sintaxe de sua utilização é parecida com a da função LAG:

LEAD(<função de agregação>, offset , default)
OVER (<critério de agrupamento e ordenação>)

Sendo que os parâmetros devem ser formados desta forma:



  • <função de agregação>: qualquer função de agregação do MSSQL (SUM, COUNT, AVG) para obter o resultado desejado.
  • offset: número de linhas à frente da atual para se obter um valor. Se deixado em branco irá usar o conjunto de resultados imediatamente posterior.
  • default: valor que será exibido caso um valor nulo seja obtido pela função de agregação.
  • <critério de agrupamento e ordenação>: cláusulas para particionamento (se aplicável) e ordenação dos dados para compor o resultado final. Especialmente a questão de ordenação afeta diretamente o resultado pois partindo da coluna que se está ordenando é que os resultados serão exibidos.

A documentação oficial e completa para a função LEAD pode ser encontrada aqui sendo recomendável sua leitura para melhor compreensão.


Aplicando a função


Para demonstrar uma utilização prática, no exemplo (baseado no banco de dados AdventureWorks2012) colocado abaixo, a consulta irá retornar a soma das vendas para cada ano distribuídas em uma coluna para o ano anterior, outra para o ano atual e outra para o ano seguinte:

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

Neste exemplo a função LEAD e a LAG estão sendo usadas, o que mostra que é possível ter uma ou mais destas em uma mesma consulta.


O resultado desta consulta está na figura abaixo.


image


Aprimorando a apresentação dos dados


Da forma como está já os resultados são exibidos corretamente, porém, se o objetivo for fazer uma análise do que estes dados querem dizer e assim obter alguma informação mais útil, é necessário trabalhar um pouco mais na consulta.


Considere então, apresentar a variação percentual entre as vendas do ano anterior (representado pela coluna nomeada como SalesInPreviousYear) e as do ano corrente (representada pela coluna nomeada como SalesInCurrentYear) e as desta última com as do ano seguinte (nomeada como SalesInNextYear).


Para isto deve se aplicar uma regra de três simples para obter o resultado tomando-se o cuidado de fazer tratamento para que não haja divisão por zero (o que vai acontecer, neste exemplo, sempre na primeira linha dos resultados). A consulta inicialmente fica desta forma:

SELECT YEAR(OrderDate) [Year]
,LAG(SUM(SubTotal), 1, 0) OVER (
ORDER BY YEAR(OrderDate)
) AS SalesInPreviousYear
,SUM(SubTotal) AS SalesInCurrentYear
,CASE LAG(SUM(SubTotal), 1, 0) OVER (
ORDER BY YEAR(OrderDate)
)
WHEN 0 THEN 0
ELSE (
SUM(SubTotal) - LAG(SUM(SubTotal), 1, 0) OVER (
ORDER BY YEAR(OrderDate)
)
) / LAG(SUM(SubTotal), 1, 0) OVER (
ORDER BY YEAR(OrderDate)
) * 100
END AS rate1
,LEAD(SUM(SubTotal), 1, 0) OVER (
ORDER BY YEAR(OrderDate)
) AS SalesInNextYear
,CASE SUM(SubTotal)
WHEN 0 THEN 0
ELSE (
(
LEAD(SUM(SubTotal), 1, 0) OVER (
ORDER BY YEAR(OrderDate)
) - SUM(SubTotal)
) / SUM(SubTotal) * 100
)
END AS Rate2
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)

Como é de se esperar, este tipo de consultas repete demais algumas coisas como as chamadas para LAG e LEAD pelo simples motivo de não ser possível usar o nome que foram dados para as colunas que foram geradas. Por enquanto pelo menos, o MSSQL não permite fazer este recurso e assim, sempre que for necessário usar o resultado de uma coluna calculada para obter resultados em outras colunas - como o cálculo de percentuais - é necessário repetir toda a expressão que gera a coluna.


Para deixar a consulta ainda mais complexa, para validar se existem valores zerados e prevenir a divisão por estes também é necessário repetir a expressão completamente ao aplicar CASE.


Uma alternativa é usar consultas aninhadas, ou seja, fazer uma instrução SELECT sobre o resultado da consulta:

SELECT [Year]
,SalesInPreviousYear
,SalesInCurrentYear
,CASE SalesInPreviousYear
WHEN 0 THEN 0
ELSE (SalesInCurrentYear-SalesInPreviousYear)
/ SalesInPreviousYear * 100
END as Rate1
,SalesInNextYear
,CASE SalesInCurrentYear
WHEN 0 THEN 0
ELSE (SalesInNextYear-SalesInCurrentYear)
/ SalesInCurrentYear * 100
END as Rate2
FROM (
SELECT YEAR(OrderDate) [Year]
,LAG(SUM(SubTotal), 1, 0) OVER (
ORDER BY YEAR(OrderDate)
) AS SalesInPreviousYear
,SUM(SubTotal) AS SalesInCurrentYear
,LEAD(SUM(SubTotal), 1, 0) OVER (
ORDER BY YEAR(OrderDate)
) AS SalesInNextYear
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
) AS TempTable

Usando CTE


A solução apresentada é boa e resolve muitos problemas e deixa a consulta mais legível facilitando sua compreensão. Porém é possível deixar ainda mais legível, evitando o uso deste tipo de expressão com a utilização de CTE - Common Table Expression - que basicamente permitem definir a consulta que gerará a tabela com os resultados sobre os quais se deseja fazer a sub consulta. Mais aplicações sobre este assunto podem ser consultados neste link: USANDO EXPRESSÕES DE TABELA COMUNS fornecido pela documentação oficial.


Usando a CTE basicamente se define a consulta de base inicialmente começando com WITH e em seguida, executa-se a instrução SELECT sobre o resultado desta consulta:

WITH TableCTE
AS (
SELECT YEAR(OrderDate) [Year]
,LAG(SUM(SubTotal), 1, 0) OVER (
ORDER BY YEAR(OrderDate)
) AS SalesInPreviousYear
,SUM(SubTotal) AS SalesInCurrentYear
,LEAD(SUM(SubTotal), 1, 0) OVER (
ORDER BY YEAR(OrderDate)
) AS SalesInNextYear
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
)
SELECT [Year]
,FORMAT(SalesInPreviousYear, '##,###,##0.00') AS SalesInPreviousYear
,FORMAT(SalesInCurrentYear, '##,###,##0.00') AS SalesInCurrentYear
,CASE SalesInPreviousYear
WHEN 0 THEN 0
ELSE (SalesInCurrentYear-SalesInPreviousYear)/SalesInPreviousYear*100
END AS Rate1
,FORMAT(SalesInNextYear, '##,###,##0.00') AS SalesInNextYear
,CASE SalesInCurrentYear
WHEN 0 THEN 0
ELSE (SalesInNextYear-SalesInCurrentYear)/SalesInCurrentYear*100
END AS Rate2
FROM TableCTE
ORDER BY [Year]

A utilização deste tipo de codificação além de facilitar a leitura do código (e consequentemente agilizar manutenções que possam ser necessárias) também não afeta de modo algum o plano de execução ou o seu desempenho. O mais importante é lembrar da sintaxe básica para compor a CTE que deve iniciar-se com WITH <nome da tabela temporária> AS (< SELECT para obter dados>).


Finalizando esta consulta os resultados numéricos foram formatados para facilitar sua leitura com a função FORMAT que como poderá ser verificado no link é bem versátil.


Esta consulta gera o resultado que está exibido abaixo:


image

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…