Uma introdução às funções da janela SQL

O recurso muito poderoso que você adora odiar (mas precisa saber)

As funções da janela SQL fornecem alguns recursos extremamente poderosos e úteis. Mas para muitos, por serem tão estranhos ao SQL padrão, são difíceis de aprender e entender, têm uma sintaxe estranha – e muitas vezes são evitados.

As funções de janela podem ser explicadas simplesmente como funções de cálculo semelhantes à agregação, mas onde a agregação normal por meio da cláusula GROUP BY combina oculta as linhas individuais que estão sendo agregadas, as funções de janela têm acesso a linhas individuais e podem adicionar alguns dos atributos dessas linhas na conjunto de resultados.

Neste tutorial de funções de janela SQL, vou apresentar as funções de janela, explicar os benefícios e quando você as usaria e fornecer exemplos reais para ajudar com os conceitos.

Uma janela para seus dados

Um dos recursos mais usados ​​e importantes no SQL é a capacidade de agregar ou agrupar linhas de dados de maneiras específicas. Em alguns casos, no entanto, o agrupamento pode se tornar extremamente complexo, dependendo do que for necessário.

Você já quis percorrer os resultados de sua consulta para obter uma classificação, uma lista x top ou similar? Você já teve algum projeto de análise em que queria preparar seus dados para uma ferramenta de visualização, mas achou quase impossível ou tão complexo que não valeu a pena?

As funções da janela podem facilitar as coisas. Depois de obter o resultado de sua consulta - ou seja, após a cláusula WHERE e qualquer agregação padrão, as funções de janela atuarão nas linhas restantes (a janela de dados) e obterão o que você deseja.

Algumas das funções de janela que veremos incluem:

• OVER
• COUNT()
• SUM()
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LEAD()
• LAG()

Extremamente fácil

A cláusula OVER é o que especifica uma função de janela e deve sempre ser incluída na instrução. O padrão em uma cláusula OVER é todo o conjunto de linhas. Como exemplo, vamos examinar uma tabela de funcionários em um banco de dados da empresa e mostrar o número total de funcionários em cada linha, juntamente com as informações de cada funcionário, incluindo quando eles começaram na empresa.

 SELECT COUNT(*) OVER() As NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

O acima, como muitas funções de janela, também pode ser escrito de uma maneira não-janela mais familiar - o que, neste exemplo simples, não é tão ruim:

 SELECT (SELECT COUNT(*) FROM Employee) as NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Mas agora, digamos que desejamos mostrar o número de funcionários que começaram no mesmo mês que o funcionário na linha. Precisaremos restringir ou restringir a contagem apenas a esse mês para cada linha. Como isso é feito? Usamos a cláusula window PARTITION , assim:

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started),YEAR(date_started)) As NumPerMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname FROM Employee ORDER BY date_started;

As partições permitem filtrar a janela em seções por um determinado valor ou valores. Cada seção é frequentemente chamada de moldura da janela.

Para ir mais longe, digamos que não apenas queríamos descobrir quantos funcionários começaram no mesmo mês, mas queremos mostrar em que ordem eles começaram naquele mês. Para isso, podemos usar a familiar ORDER BY . No entanto, dentro de uma função de janela, ORDER BY age de maneira um pouco diferente do que no final de uma consulta.

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started), YEAR(date_started) ORDER BY date_started) As NumThisMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Nesse caso, ORDER BY modifica a janela para que vá do início da partição (neste caso, o mês e ano de início do funcionário) até a linha atual. Assim, a contagem é reiniciada em cada partição.

Classificar

As funções de janela podem ser muito úteis para fins de classificação. Anteriormente, vimos que o uso da função de agregação COUNT nos permitia ver em que ordem os funcionários ingressaram na empresa. Também poderíamos ter usado funções de classificação de janela, como ROW_NUMBER() , RANK() e DENSE_RANK() .

As diferenças podem ser vistas depois que adicionamos um novo funcionário no mês seguinte e removemos a partição:

 SELECT ROW_NUMBER() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As StartingRank, RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As EmployeeRank, DENSE_RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As DenseRank, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Você pode ver as diferenças. ROW_NUMBER() fornece uma contagem sequencial dentro de uma determinada partição (mas com a ausência de uma partição, ela passa por todas as linhas). RANK() fornece a classificação de cada linha com base na ORDER BY . Ele mostra empates e, em seguida, pula a próxima classificação. DENSE_RANK também mostra empates, mas continua com o próximo valor consecutivo como se não houvesse empate.

Outras funções de classificação incluem:

• CUME_DIST – Calcula a classificação relativa da linha atual dentro de uma partição
• NTILE – Divide as linhas para cada partição da janela da forma mais igual possível
• PERCENT_RANK – Classificação percentual da linha atual

Observe também neste exemplo que você pode ter várias funções Window em uma única consulta - e tanto a partição quanto a ordem podem ser diferentes em cada uma!

Linhas e intervalos e quadros, oh meu Deus

Para definir ou limitar ainda mais seu quadro de janela dentro da cláusula OVER() , você pode usar ROWS e RANGE . Com a cláusula ROWS , você pode especificar as linhas incluídas em sua partição como aquelas anteriores ou posteriores à linha atual.

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

Neste exemplo, o quadro da janela vai da primeira linha para a linha atual menos 1 e o tamanho da janela continua a aumentar para cada linha.

O alcance funciona um pouco diferente e podemos obter um resultado diferente.

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

O intervalo incluirá as linhas no quadro da janela que possuem os mesmos valores ORDER BY da linha atual. Assim, é possível obter duplicatas com RANGE se o ORDER BY não for único.

Alguns descrevem ROWS como um operador físico enquanto RANGE é um operador lógico. Em qualquer caso, os valores padrão para ROWS e RANGE são sempre UNBOUNDED PRECEDING AND CURRENT ROW .

O quê mais?

A maioria das funções de agregação padrão funciona com funções de janela. Já vimos COUNT nos exemplos. Outros incluem SUM , AVG , MIN , MAX , etc.

Com funções de janela, você também pode acessar registros anteriores e subsequentes usando LAG e LEAD e FIRST_VALUE e LAST_VALUE . Por exemplo, digamos que você queira mostrar em cada linha um valor de vendas do mês atual e a diferença entre o valor de vendas do mês anterior. Você pode fazer algo assim:

 SELECT id, OrderMonth, OrderYear, product, sales, sales - LAG(sales,1) OVER (PARTITION BY product ORDER BY OrderYear, OrderMonth) As sales_change FROM sales_products WHERE sale_year = 2019;

Basicamente, as funções da janela SQL são muito poderosas

Embora esta seja uma introdução rápida às funções da janela SQL, esperamos que desperte seu interesse em ver tudo o que elas podem fazer. Aprendemos que as funções de janela executam cálculos semelhantes às funções de agregação, mas com o benefício adicional de terem acesso aos dados nas linhas individuais, o que as torna bastante poderosas. Eles sempre contêm a cláusula OVER e podem conter PARTITION BY , ORDER BY e uma série de funções de agregação ( SUM , COUNT , etc.) e outras funções posicionais ( LEAD , LAG ). Também aprendemos sobre molduras de janelas e como elas encapsulam seções de dados.

Observe que diferentes tipos de SQL podem implementar funções de janela de maneira diferente e algumas podem não ter implementado todas as funções ou cláusulas de janela. Certifique-se de verificar a documentação da plataforma que você está usando.

Se, como desenvolvedor SQL, você estiver interessado em ajustar o desempenho do seu banco de dados SQL, confira Ajuste de desempenho do banco de dados SQL para desenvolvedores .

Feliz janela!

Para obter mais informações sobre implementações específicas, consulte:

• Documentação de funções de janela do PostgreSQL para uma implementação do PostgreSQL.
• SELECT - OVER Clause (Transact-SQL) da Microsoft.
• Window Functions no SQL Server para uma ótima visão geral das implementações do SQL Server e sua parte 2.