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