Por Kathi Kellenberger
I tinha usado ROW_NUMBER no meu código T-SQL durante anos sem perceber que é uma de um grupo de funções incrivelmente úteis chamadas funções de janela. Somente ao montar uma apresentação sobre as novas funções do T-SQL no lançamento do SQL Server 2012 é que percebi que havia tanto para falar que criei uma apresentação separada apenas sobre as novas funções de janela. Eventualmente uma apresentação transformou-se em duas apresentações, meu livro publicado pela Apress, e um curso Pluralsight. Eu tenho dito a qualquer pessoa que vai ouvir sobre essas grandes funções, e me encontro usando-as diariamente no trabalho.
As funções de janela permitem que você faça cálculos sobre um conjunto de linhas. Elas são semelhantes em funcionamento ao agrupamento com funções agregadas, mas permitem uma diferença importante: As consultas agregadas retornam uma linha por grupo, e os detalhes são eliminados nos resultados. Consultas envolvendo funções de janela retornam todas as linhas de detalhes junto com o resultado da expressão da função de janela. Você tem detalhes, e obtém o agregado também.
Quando eu falo no SQL Server e eventos de desenvolvedores sobre funções de janela, normalmente cerca de metade da sala está familiarizada com ROW_NUMBER. Um punhado de pessoas saberá mais do que isso. Isto é decepcionante já que estas funções úteis estão disponíveis há anos.
A minha função de janela favorita chama-se LAG. O LAG permite que você pegue qualquer coluna de outra linha do conjunto de resultados. Você pode fazer a mesma coisa com outros métodos, mas o desempenho é muito pior. A seguir estão duas consultas à base de dados AdventureWorks. Ambas as consultas dão o mesmo resultado. A primeira faz isso através de uma junção externa, enquanto a segunda tira vantagem do LAG.
–Lista os clientes com datas de pedidos e os dias desde o pedido anterior
SELECT CustomerID, OrderDate,
DATEDIFF(day,OAPPLY.PrevOrder, OrderDate) AS DaysSincePrevOrder
FROM Sales.SalesOrderHeader AS SOH
OUTER APPLY (
SELECT TOP(1) OrderDate AS PrevOrder
FROM Sales.SalesOrderHeader AS OA
WHERE OA.CustomerID = SOH.CustomerID
E OA.OrderDate < SOH.OrderDate
ORDER BY OrderDate) Como OAPPLY
ORDER BYDY, OrderDate;
–Produza os mesmos resultados usando LAG
SELECT CustomerID, OrderDate,
DATEDIFF(day,LAG(OrderDate) OVER(PARTITION BYDY ORDER BYDATE),OrderDate) AS DaysSinceLastOrder
FROM Sales.SalesOrderHeader
ORDER BY CustomerID, OrderDate;
A segunda consulta usando a função LAG é mais simples de escrever e tem um melhor desempenho. O plano de execução real mostra que a primeira consulta leva 99% dos recursos do lote. A segunda consulta leva apenas 1% dos recursos. Aqui estão os dois planos de execução, e você pode ver por si mesmo:
Se você não está familiarizado com a sintaxe da função window, a consulta do LAG pode ser um pouco confusa. Todas as funções de janela terão uma cláusula OVER que define a janela, ou conjunto de linhas, para o cálculo. Não quero comparar datas de pedidos entre clientes, então usei a opção PARTITION BY para dividir as linhas em grupos por CustomerID:
LAG(OrderDate) OVER(PARTITION BY CustomerID …
A cláusula OVER também tem uma opção ORDER BY. Isto é necessário no caso do LAG, para que as filas sejam alinhadas na ordem correta para que o motor pegue os respectivos valores de suas filas corretas. A minha invocação final do LAG tem a seguinte aparência:
LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate)
LAG é apenas uma função de muitas funções de janela. Estas funções não permitem que você faça nada que seja impossível sem elas, mas definitivamente tornam a codificação mais fácil. Você também verá que muitas vezes suas consultas são mais rápidas quando formuladas usando estas novas funções.
I acabo usando funções de janela diariamente. Elas facilitam a minha vida e melhoram o desempenho das minhas consultas. Eu adoro usá-las, e você também deveria.
Sobre o Autor
Kathi Kellenberger é um consultor com Linchpin People. Ela gosta de escrever e falar sobre tópicos do SQL Server, tendo escrito mais de duas dúzias de artigos, contribuído para quatro livros, e apresentado em muitos eventos do SQL Server. Em seu tempo livre, Kathi gosta de passar tempo com a família e amigos, cantando e pedalando.
Saiba ainda mais sobre funções de janela e seu poder na resolução de problemas difíceis de consulta do livro de Kathi Kellenberger Expert T-SQL Window Functions in SQL Server (ISBN 9781484211045). Os outros livros de Kathi incluem Beginning SQL Server Reporting Services (2016) e Beginning T-SQL (2014).
Deixe uma resposta