O que são funções de janela do SQL?

Precisa melhorar seu jogo de análise de dados? Aprenda essas funções de janela SQL aqui e você levará suas habilidades de análise para o próximo nível. Inclui exercícios práticos e explicações detalhadas!

Quando ouvi falar pela primeira vez sobre as funções de janela do SQL, pensei que se tratava de um estranho casamento entre o SQL e o Windows. Eu estava errado. As funções de janela do SQL não têm nada a ver com esse famoso sistema operacional. Elas são funções SQL que fazem cálculos no conjunto de linhas relacionadas à linha atual. Esse conjunto de linhas é chamado de janela ou quadro de janela - daí o nome da função.

Você também pode ouvir falar de funções de janela no SQL, funções analíticas ou funções OVER(). Todos esses são apenas nomes alternativos para as funções de janela do SQL - um conjunto extremamente útil de ferramentas para análise de dados.

Neste artigo, mostraremos o que você pode fazer com as funções de janela e como. Começarei com um breve histórico das funções de janela do SQL e explicarei por que elas têm esse nome. Em seguida, orientarei você pela sintaxe e mostrarei como ela funciona com vários exemplos. Depois de praticar a sintaxe, estaremos prontos para exemplos reais de funções de janela da vida de um analista de dados. E aqui está a parte mais interessante: após cada exemplo, há um exercício para você resolver e aprender por meio da codificação.

No entanto, a principal fonte de seu conhecimento sobre esse assunto deve ser o nosso Funções de Janela (Window Functions) em SQL curso. Seus 218 exercícios interativos abrangem detalhadamente as funções de janela do SQL. Em outras palavras, você aprenderá sobre frames de janela e as cláusulas OVER(), PARTITION BY e ORDER BY. Tudo isso é necessário para agregar, classificar e analisar dados usando funções de janela.

Breve histórico do SQL Funções de Janela (Window Functions) em SQL

As funções de janela foram introduzidas pela primeira vez no banco de dados Oracle8i, que foi lançado em 1998. No entanto, elas foram incluídas no padrão SQL cinco anos depois, com o SQL:2003.

Em seguida, a Microsoft as incluiu no SQL Server 2005. Outros sistemas de gerenciamento de banco de dados (DBMS) as seguiram; o PostgreSQL as suporta desde que o PostgreSQL 8.4 foi lançado em 2009; o MariaDB as incluiu na versão 10.2 (2016) e o MySQL as adicionou à versão 8 em 2018.

As funções de janela são um recurso bastante novo no SQL. Por esse motivo, elas não fazem parte do currículo normal de SQL. Ao aprendê-las, você estará à frente da curva em comparação com muitos usuários de SQL.

O que é uma Window?

Um conjunto de linhas relacionadas à linha atual é chamado de janela ou quadro de janela. Daí o nome dessas funções: seu resultado é baseado em um quadro de janela deslizante.

Por exemplo, você pode calcular uma soma cumulativa como mostrado abaixo:

date	sales	cumulative_sum
2023-10-01	4,241	4,241
2023-10-02	2,389	6,630
2023-10-03	1,580	8,210
2023-10-04	3,395	11,605
2023-10-05	1,265	12,870

A janela para a soma cumulativa de 2023-10-04 está destacada em verde. Ela inclui a linha atual (para 2023-10-04) e todas as linhas anteriores. Portanto, a soma cumulativa é calculada como a soma de todas as vendas anteriores e atuais: 4.241 + 2.389 + 1.580 + 3.395 = 11.605. (Observe que a linha destacada com pontos vermelhos não está incluída na janela ou na soma).

Quando passamos para a próxima linha, a janela também se move: agora ela incluirá todas as linhas anteriores (verde) e a linha atual (pontilhado vermelho). Agora a soma cumulativa é 4.241 + 2.389 + 1.580 + 3.395 + 1.265 = 12.870.

Portanto, a janela é o conjunto de linhas relacionadas à linha atual que são usadas em cálculos para essa linha. A janela muda (desliza) à medida que percorremos as linhas; graças a essas imagens de uma janela deslizante, obtemos o nome dessas funções.

Sintaxe da função de janela do SQL

A sintaxe das funções de janela é a seguinte:

SELECT column_1,
       column_2,
	 <window_function> OVER(PARTITION BY … ORDER BY … ) AS column_alias
FROM table;

Veja a seguir o que cada parte faz:

• <window_function> - Especifica a função a ser aplicada a essa janela.
• OVER() - Define a janela (conjunto de linhas) e indica que se trata de uma função de janela; sem essa cláusula, não se trata de uma função de janela.
• <window_frame> - Define o tamanho do quadro da janela (opcional).
• PARTITION BY - Divide a janela em grupos menores chamados partições (opcional); se omitido, todo o conjunto de resultados será uma partição.
• ORDER BY - Classifica as linhas dentro do quadro da janela (opcional), ou seja, decide em que ordem a operação da janela será executada; se omitida, a ordem das linhas dentro da partição é arbitrária.

Cláusulas adicionais podem definir melhor a janela. Sua sintaxe é a seguinte:

[<ROWS or RANGE clause> BETWEEN <lower_bound> AND <upper_bound>]

A cláusula ROWS define a janela em termos do número fixo de linhas em relação à linha atual.

A cláusula RANGE faz o mesmo. Mas também leva em consideração o cálculo de todas as linhas com os mesmos valores nas colunas especificadas na cláusula ORDER BY como a linha atual.

Os limites da janela podem ser definidos como

• UNBOUNDED PRECEDING - Todas as linhas antes da linha atual.
• n PRECEDING - Um número definido de linhas antes da linha atual.
• CURRENT ROW - Inclui a linha atual.
• n FOLLOWING - Um número definido de linhas após a linha atual.
• UNBOUNDED FOLLOWING - Todas as linhas após a linha atual.

Vamos ver agora como isso funciona na prática.

Exemplos de conjunto de dados e sintaxe

Usaremos a tabela album_catalogue em todos esses exemplos. Você mesmo pode criá-la usando este script. Um instantâneo de dados é mostrado abaixo:

id	album_title	album_length	album_genre	artist	copies_sold	sales_period
1	Wednesday Morning, 3 A.M	0:31:38	Folk	Simon & Garfunkel	1043	2022_1Q
2	EnRoute: John Scofield Trio LIVE	1:13:48	Jazz	John Scofield Trio	512	2022_1Q
3	Nasty Gal	0:39:15	Funk	Betty Davis	809	2022_1Q
4	The New Folk Sound of Terry Callier	0:37:41	Folk	Terry Callier	903	2022_1Q
5	In a Silent Way	0:38:08	Jazz	Miles Davis	428	2022_1Q

O conjunto de dados é uma lista de álbuns com sua duração, gênero, artista e dados de vendas, incluindo o número de cópias vendidas e o período (trimestres). Os dados vão até o terceiro trimestre de 2023.

Primeiro, mostrarei vários exemplos, explicando cada parte crucial da sintaxe das funções de janelamento ao longo do caminho.

Exemplo de sintaxe nº 1: OVER ()

Você pode usar a função de janela SUM() somente com a cláusula OVER() para obter o total de vendas no quarto trimestre de 2022:

SELECT sales_period,
 album_title,
	 artist,
	 copies_sold,
	 SUM (copies_sold) OVER() AS sold_in_4Q_2022
FROM album_catalogue
WHERE sales_period = '2022_4Q';

Quero a soma das cópias vendidas, portanto, especifico essa coluna em SUM(). A cláusula OVER() é obrigatória. Se você quiser usar OVER() sem nenhuma das cláusulas opcionais, basta deixar os parênteses vazios.

Eu uso WHERE para gerar somente os dados do trimestre desejado.

Quando você escreve uma consulta como essa - com um OVER() vazio - todo o conjunto de resultados (colunas selecionadas, filtros aplicados etc.) é levado em consideração ao realizar os cálculos da função de janela. Aqui, o resultado mostra as vendas individuais de cada álbum vendido no quarto trimestre de 2022. Ele também mostra o total de vendas de todos os álbuns vendidos nesse período.

sales_period	album_title	artist	copies_sold	sold_in_4q_2022
2022_4Q	Wednesday Morning, 3 A.M	Simon & Garfunkel	809	7,403
2022_4Q	EnRoute: John Scofield Trio LIVE	John Scofield Trio	612	7,403
2022_4Q	Nasty Gal	Betty Davis	369	7,403
2022_4Q	The New Folk Sound of Terry Callier	Terry Callier	214	7,403
2022_4Q	In a Silent Way	Miles Davis	65	7,403
2022_4Q	Cold Sweat	James Brown	209	7,403
2022_4Q	The Freewheelin' Bob Dylan	Bob Dylan	246	7,403
2022_4Q	My Favorite Things	John Coltrane	377	7,403
2022_4Q	A Whole New Thing	Sly and the Family Stone	816	7,403
2022_4Q	Five Leaves Left	Nick Drake	400	7,403
2022_4Q	Head Hunters	Herbie Hancock	409	7,403
2022_4Q	In the Right Place	Dr. John	912	7,403
2022_4Q	Blue	Joni Mitchell	412	7,403
2022_4Q	Concierto	Jim Hall	612	7,403
2022_4Q	Dirty Mind	Prince	941	7,403

Com a ajuda de SUM() e OVER(), posso mostrar as vendas de cada álbum individual e o total trimestral.

Exemplo de sintaxe nº 2: OVER (ORDER BY)

Você pode adicionar cláusulas adicionais dentro da cláusula OVER() para alterar a definição do quadro da janela. Uma dessas cláusulas é ORDER BY. A cláusula ORDER BY define a classificação das linhas em um quadro de janela: as linhas podem ser processadas pela função de janela em uma determinada ordem.

Vejamos um exemplo. Você pode calcular a soma cumulativa adicionando ORDER BY à consulta anterior. Neste exemplo, quero ver como o álbum "In the Right Place" é vendido ao longo do tempo e o número cumulativo de álbuns vendidos até um determinado período de tempo. Aqui está a consulta:

SELECT sales_period,
	 album_title,
	 artist,
	 copies_sold,
	 SUM (copies_sold) OVER(ORDER BY sales_period ASC) AS cumulative_sum
FROM album_catalogue
WHERE album_title = 'In the Right Place';

A mesma coluna copies_sold está em SUM(). Desta vez, OVER() contém a cláusula ORDER BY. Você deseja mostrar as vendas cumulativas do primeiro ao último trimestre. É por isso que você precisa de sales_period e ASC em ORDER BY.

sales_period	album_title	artist	copies_sold	cumulative_sum
2022_1Q	In the Right Place	Dr. John	222	222
2022_2Q	In the Right Place	Dr. John	208	430
2022_3Q	In the Right Place	Dr. John	94	524
2022_4Q	In the Right Place	Dr. John	912	1436
2023_1Q	In the Right Place	Dr. John	912	2348
2023_2Q	In the Right Place	Dr. John	56	2404
2023_3Q	In the Right Place	Dr. John	562	2966

Em cada linha, você pode ver as vendas de cada trimestre e a soma cumulativa, ou seja, a soma do trimestre atual e de todos os trimestres anteriores. Por exemplo, o álbum vendeu 94 cópias no terceiro trimestre de 2022. O total de vendas em 2022 até então (ou em três trimestres) é: 222 + 208 + 94 = 524.

Exemplo de sintaxe nº 3: OVER (PARTITION BY)

Outra cláusula que você pode usar em OVER() é PARTITION BY. PARTITION BY é usada para dividir a janela em segmentos menores com base em alguns critérios. Por exemplo, você pode listar os álbuns, seus dados de vendas para o quarto trimestre de 2022 e as vendas por gênero para esse trimestre:

SELECT album_title,
	 artist,
	 copies_sold,
	 album_genre,
	 SUM (copies_sold) OVER(PARTITION BY album_genre) AS sales_by_genre
FROM album_catalogue
WHERE sales_period = '2022_4Q';

Novamente, usamos a mesma função de janela SUM(). Desta vez, porém, usamos PARTITION BY para dividir a janela em segmentos menores com base no gênero do álbum. Todo o resto permanece o mesmo.

A consulta retorna o resultado abaixo. Trata-se de uma análise dos dados de vendas de álbuns por gênero no último trimestre de 2022.

album_title	artist	copies_sold	album_genre	sales_by_genre
Wednesday Morning, 3 A.M	Simon & Garfunkel	809	Folk	2,081
The Freewheelin' Bob Dylan	Bob Dylan	246	Folk	2,081
Five Leaves Left	Nick Drake	400	Folk	2,081
The New Folk Sound of Terry Callier	Terry Callier	214	Folk	2,081
Blue	Joni Mitchell	412	Folk	2,081
Dirty Mind	Prince	941	Funk	3,247
Nasty Gal	Betty Davis	369	Funk	3,247
Cold Sweat	James Brown	209	Funk	3,247
A Whole New Thing	Sly and the Family Stone	816	Funk	3,247
In the Right Place	Dr. John	912	Funk	3,247
Head Hunters	Herbie Hancock	409	Jazz	2,075
EnRoute: John Scofield Trio LIVE	John Scofield Trio	612	Jazz	2,075
In a Silent Way	Miles Davis	65	Jazz	2,075
Concierto	Jim Hall	612	Jazz	2,075
My Favorite Things	John Coltrane	377	Jazz	2,075

Por exemplo, a soma cumulativa dos álbuns folk é 809 + 246 + 400 + 214 + 412 = 2.081.

Exemplo de sintaxe nº 4: OVER (ORDER BY PARTITION BY)

Você também pode usar tanto PARTITION BY quanto ORDER BY em OVER(). As linhas são divididas em segmentos com PARTITION BY e processadas em uma determinada ordem por ORDER BY.

Usando a consulta abaixo, posso mostrar todos os dados analíticos do álbum e calcular a soma cumulativa de cada álbum separadamente:

SELECT sales_period,
	 album_title,
	 artist,
	 copies_sold,
	 SUM (copies_sold) OVER(PARTITION BY album_title ORDER BY sales_period ASC) AS cumulative_sum_by_album
FROM album_catalogue;

Calculei isso com a ajuda da função de janela SUM(), como fiz anteriormente. Particionei a janela por álbum. Isso significa que a soma será acumulada até que a função atinja a última linha de um determinado álbum. Quando chega a outro álbum, ela reinicia e começa a acumular a soma desde o início.

Também uso ORDER BY para instruir a função a acumular a soma do primeiro ao último trimestre.

sales_period	album_title	artist	copies_sold	cumulative_sum_by_album
2022_1Q	A Whole New Thing	Sly and the Family Stone	674	674
2022_2Q	A Whole New Thing	Sly and the Family Stone	257	931
2022_3Q	A Whole New Thing	Sly and the Family Stone	666	1,597
2022_4Q	A Whole New Thing	Sly and the Family Stone	816	2,413
2023_1Q	A Whole New Thing	Sly and the Family Stone	816	3,229
2023_2Q	A Whole New Thing	Sly and the Family Stone	302	3,531
2023_3Q	A Whole New Thing	Sly and the Family Stone	123	3,654
2022_1Q	Blue	Joni Mitchell	589	589
2022_2Q	Blue	Joni Mitchell	184	773
2022_3Q	Blue	Joni Mitchell	256	1,029
2022_4Q	Blue	Joni Mitchell	412	1,441
2023_1Q	Blue	Joni Mitchell	412	1,853
2023_2Q	Blue	Joni Mitchell	99	1,952
2023_3Q	Blue	Joni Mitchell	995	2,947
…	…	…	…	…
2022_1Q	Wednesday Morning, 3 A.M	Simon & Garfunkel	1,043	1,043
2022_2Q	Wednesday Morning, 3 A.M	Simon & Garfunkel	437	1,480
2022_3Q	Wednesday Morning, 3 A.M	Simon & Garfunkel	184	1,664
2022_4Q	Wednesday Morning, 3 A.M	Simon & Garfunkel	809	2,473
2023_1Q	Wednesday Morning, 3 A.M	Simon & Garfunkel	809	3,282
2023_2Q	Wednesday Morning, 3 A.M	Simon & Garfunkel	325	3,607
2023_3Q	Wednesday Morning, 3 A.M	Simon & Garfunkel	612	4,219

Você pode ver que a soma acumulada para "A Whole New Thing" é 3.654. O próximo álbum ("Blue") começa com a próxima linha, portanto, a acumulação é reiniciada: a soma acumulada é a mesma que as vendas individuais do álbum no primeiro trimestre de 2022. Em seguida, ela se acumula até chegar ao próximo álbum. O resultado vai até o último álbum, que é "Wednesday Morning, 3 A.M." em nosso caso.

Mostrei a você as formas mais comuns de definir quadros de janelas com esses exemplos. Mas essas não são as únicas maneiras. Você também pode usar as cláusulas ROW ou RANGE com a sintaxe e os limites que explicamos anteriormente. Não se preocupe. Você verá o uso prático disso em exemplos do mundo real.

Usei apenas uma função, SUM(), em todos esses exemplos. Essa é uma das muitas funções de janela; vamos revisar rapidamente algumas das outras.

Quais são as mais comuns Funções de Janela (Window Functions) em SQL?

As funções de janela mais comuns podem ser divididas em três categorias:

1. Agregado Funções de Janela (Window Functions) em SQL:

• COUNT() - Conta o número de linhas em uma janela.
• SUM() - Totaliza determinados valores em uma janela.
• AVG() - Calcula a média de determinados valores em uma janela.
• MIN() - Localiza o menor valor em uma janela.
• MAX() - Encontra o maior valor em uma janela.

2. Classificação Funções de Janela (Window Functions) em SQL:

• ROW_NUMBER() - Classifica os valores sequencialmente, com classificações diferentes para os valores empatados.
• RANK() - Classifica os valores usando a mesma classificação para os valores empatados; pula a próxima classificação após os empates (por exemplo, 1, 2, 2, 4).
• DENSE_RANK() - Classifica os valores usando a mesma classificação para os valores empatados; não pula a próxima classificação após os empates (por exemplo, 1,2,2,3,4).

3. Analítico Funções de Janela (Window Functions) em SQL:

• LEAD() - Obtém dados de um deslocamento definido (ou seja, um número declarado de linhas) após a linha atual.
• LAG() - Obtém dados de um deslocamento definido (ou seja, um número declarado de linhas) antes da linha atual.

Há mais funções de janela que podem ser úteis. Confira-as em nossa Folha de dicas SQL Funções de Janela (Window Functions) em SQL gratuita .

Exemplos de funções de janela SQL do mundo real

Até agora, concentrei-me mais na sintaxe das funções de janela do SQL. Agora, mostrarei os usos práticos mais comuns das funções de janela e como elas podem ajudar os analistas de dados em seu trabalho.

Esses exemplos usarão o mesmo conjunto de dados usado anteriormente.

Exemplo nº 1: Porcentagem do total

Vamos mostrar informações sobre cada álbum e suas vendas no primeiro trimestre de 2023. Além disso, mostraremos as vendas trimestrais por cada gênero. Em seguida, vamos calcular quanto cada álbum (como porcentagem) contribui para as vendas por gênero.

SELECT album_title,
	 artist,
	 copies_sold,
	 album_genre,
	 SUM(copies_sold) OVER (PARTITION BY album_genre) AS sales_by_genre,
	 (copies_sold*1.0/ SUM(copies_sold) OVER (PARTITION BY album_genre))*100.0 AS percent_of_genre_sales
FROM album_catalogue
WHERE sales_period = '2023_1Q'
ORDER BY album_genre, copies_sold DESC;

Para obter as vendas por gênero, uso novamente a função de janela SUM(). Na cláusula OVER(), uso apenas PARTITION BY. Dessa forma, posso dividir a janela pelo gênero do álbum.

Na próxima linha de código, divido as cópias vendidas (de cada álbum) e divido pelas vendas por gênero. Para fazer isso, basta copiar o cálculo da linha anterior. Em seguida, multiplique o quociente por 100 para obter a porcentagem. Você notará que eu também multipliquei copies_sold por 1,0. Isso serve para converter números inteiros em valores decimais.

Filtre o trimestre desejado usando WHERE. Por fim, ordene o resultado em ordem alfabética por gênero e, em seguida, de forma decrescente por cópias vendidas.

Aqui está o resultado:

album_title	artist	copies_sold	album_genre	sales_by_genre	percent_of_genre_sales
Wednesday Morning, 3 A.M	Simon & Garfunkel	809	Folk	2,081	38.88
Blue	Joni Mitchell	412	Folk	2,081	19.80
Five Leaves Left	Nick Drake	400	Folk	2,081	19.22
The Freewheelin' Bob Dylan	Bob Dylan	246	Folk	2,081	11.82
The New Folk Sound of Terry Callier	Terry Callier	214	Folk	2,081	10.28
Dirty Mind	Prince	941	Funk	3,247	28.98
In the Right Place	Dr. John	912	Funk	3,247	28.09
A Whole New Thing	Sly and the Family Stone	816	Funk	3,247	25.13
Nasty Gal	Betty Davis	369	Funk	3,247	11.36
Cold Sweat	James Brown	209	Funk	3,247	6.44
EnRoute: John Scofield Trio LIVE	John Scofield Trio	612	Jazz	2,075	29.49
Concierto	Jim Hall	612	Jazz	2,075	29.49
Head Hunters	Herbie Hancock	409	Jazz	2,075	19.71
My Favorite Things	John Coltrane	377	Jazz	2,075	18.17
In a Silent Way	Miles Davis	65	Jazz	2,075	3.13

Vamos verificar o cálculo da primeira linha. O álbum de Simon & Garfunkel vendeu 809 cópias. O total de vendas de álbuns folk nesse trimestre foi de 2.081. Portanto, a porcentagem das vendas individuais no total de vendas do gênero é 809/2.081*100 = 38,88%.

A soma das porcentagens de cada gênero deve ser 100%. Vamos verificar isso em um gênero folclórico: 38,88% + 19,80% + 19,22% + 11,82% + 10,28% = 100%.

Função de janela vs. função agregada vs. GROUP BY

Estou novamente usando a função de agregação como uma função de janela. Eu poderia ter usado uma função agregada simples SUM() com GROUP BY para obter as vendas de cada gênero no trimestre especificado. Qual é a diferença, então?

Uma função de janela permite mostrar dados analíticos e agregados (vendas individuais com vendas por gênero e o quociente desses valores), enquanto uma função agregada usada com GROUP BY recolheria a linha individual e mostraria somente o valor agregado (a soma das vendas do trimestre).

Resolva este exercício para praticar

Usando funções de janela, reescreva a consulta acima para que ela mostre a venda média por gênero. Além disso, mostre o quanto as vendas de cada álbum estão acima ou abaixo da média do gênero (em porcentagem). Mostre somente as vendas do terceiro trimestre de 2023. Mostre o título do álbum, o artista, as cópias vendidas e o gênero do álbum. Classifique o resultado de forma ascendente por gênero e vendas de álbuns individuais.

Solução:

SELECT album_title,
	 artist,
	 copies_sold,
	 album_genre,
	 AVG(copies_sold) OVER (PARTITION BY album_genre) AS average_sales_by_genre,
	   ((copies_sold/AVG(copies_sold) OVER (PARTITION BY album_genre))-1)*100 AS pct_from_average	   
FROM album_catalogue
WHERE sales_period = '2023_3Q'
ORDER BY album_genre, copies_sold;

Saída:

album_title	artist	copies_sold	album_genre	average_sales_by_genre	pct_from_average
The New Folk Sound of Terry Callier	Terry Callier	283	Folk	561.6	-49.61
Five Leaves Left	Nick Drake	321	Folk	561.6	-42.84
The Freewheelin' Bob Dylan	Bob Dylan	597	Folk	561.6	6.30
Wednesday Morning, 3 A.M	Simon & Garfunkel	612	Folk	561.6	8.97
Blue	Joni Mitchell	995	Folk	561.6	77.17
A Whole New Thing	Sly and the Family Stone	123	Funk	533.4	-76.94
Dirty Mind	Prince	169	Funk	533.4	-68.32
In the Right Place	Dr. John	562	Funk	533.4	5.36
Nasty Gal	Betty Davis	808	Funk	533.4	51.48
Cold Sweat	James Brown	1005	Funk	533.4	88.41
Concierto	Jim Hall	263	Jazz	464	-43.32
My Favorite Things	John Coltrane	302	Jazz	464	-34.91
EnRoute: John Scofield Trio LIVE	John Scofield Trio	404	Jazz	464	-12.93
Head Hunters	Herbie Hancock	542	Jazz	464	16.81
In a Silent Way	Miles Davis	809	Jazz	464	74.35

Exemplo nº 2: dados de classificação

Neste exemplo, usarei uma função de janela para classificar os dados. Quero mostrar cada título de álbum distinto e sua duração e classificá-los por duração. O álbum mais longo será classificado em primeiro lugar.

SELECT *, 
	 RANK() OVER (ORDER BY album_length DESC) AS album_length_rank
FROM (SELECT DISTINCT album_title,
	       album_length
	FROM album_catalogue) AS distinct_album;

Vamos começar explicando a subconsulta: nós a usamos para selecionar álbuns distintos e suas durações.

Em seguida, usamos a consulta principal para selecionar todos os dados da subconsulta. Agora, use a função de janela RANK() para classificar os álbuns. Você também pode usar outras funções de classificação, dependendo de seus dados e tarefas.

Para que a classificação funcione como você deseja, use a cláusula ORDER BY em OVER(). Especifique a coluna pela qual você deseja classificar e em que ordem. Nesse caso, é de forma decrescente por comprimento.

Aqui está a classificação:

album_title	album_length	album_length_rank
EnRoute: John Scofield Trio LIVE	1:13:48	1
The Freewheelin' Bob Dylan	0:44:14	2
Head Hunters	0:41:52	3
Five Leaves Left	0:41:43	4
My Favorite Things	0:40:25	5
Nasty Gal	0:39:15	6
In a Silent Way	0:38:08	7
Concierto	0:38:02	8
A Whole New Thing	0:38:01	9
The New Folk Sound of Terry Callier	0:37:41	10
Blue	0:36:15	11
Cold Sweat	0:33:43	12
In the Right Place	0:33:22	13
Wednesday Morning, 3 A.M	0:31:38	14
Dirty Mind	0:30:14	15

Resolva este exercício para praticar

Classifique cada álbum único por suas vendas dentro de seu gênero. Mostre somente os dados do primeiro trimestre de 2023. Mostre o título do álbum, suas vendas, o gênero e a classificação. Se houver álbuns com o mesmo número de vendas, classifique-os igualmente e não pule a próxima classificação.

Solução:

SELECT *, 
	 DENSE_RANK() OVER (PARTITION BY album_genre ORDER BY copies_sold DESC) AS album_sales_rank
FROM (SELECT DISTINCT album_title,
	       copies_sold,
	       album_genre
	FROM album_catalogue
	WHERE sales_period = '2023_1Q') AS distinct_album;

Saída:

album_title	copies_sold	album_genre	album_sales_rank
Wednesday Morning, 3 A.M	809	Folk	1
Blue	412	Folk	2
Five Leaves Left	400	Folk	3
The Freewheelin' Bob Dylan	246	Folk	4
The New Folk Sound of Terry Callier	214	Folk	5
Dirty Mind	941	Funk	1
In the Right Place	912	Funk	2
A Whole New Thing	816	Funk	3
Nasty Gal	369	Funk	4
Cold Sweat	209	Funk	5
EnRoute: John Scofield Trio LIVE	612	Jazz	1
Concierto	612	Jazz	1
Head Hunters	409	Jazz	2
My Favorite Things	377	Jazz	3
In a Silent Way	65	Jazz	4

Exemplo nº 3: Total em execução

Neste exemplo, mostrarei o período de vendas de um determinado álbum, o título, o artista e as cópias vendidas. Também adicionarei um total de cópias vendidas que incluirá três linhas: a linha atual e as duas anteriores. A soma deve ser calculada do primeiro ao último trimestre.

SELECT sales_period, 
	 album_title,
	 artist,
	 copies_sold,
	 SUM(copies_sold) OVER (ORDER BY sales_period ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS sales_running_total
FROM album_catalogue
WHERE album_title = 'In a Silent Way';

Novamente uso a função de janela SUM(). Em seguida, há um ORDER BY em OVER() para ordenar as vendas de modo que possamos somá-las em ordem crescente.

Em seguida, preciso definir o quadro da janela móvel. O total em execução deve incluir a linha atual e as duas linhas anteriores. Esses são os limites inferior e superior especificados na cláusula ROWS. O limite inferior são as duas linhas anteriores, ou seja, BETWEEN 2 PRECEDING. O limite superior é CURRENT ROW. Os dois limites são unidos em um quadro de janela usando a palavra-chave AND.

Quero mostrar o cálculo para o álbum "In a Silent Way" de Miles Davis, portanto, filtro os dados usando WHERE.

Aqui estão os totais em execução:

sales_period	album_title	artist	copies_sold	sales_running_total
2022_1Q	In a Silent Way	Miles Davis	428	428
2022_2Q	In a Silent Way	Miles Davis	1,053	1,481
2022_3Q	In a Silent Way	Miles Davis	19	1,500
2022_4Q	In a Silent Way	Miles Davis	65	1,137
2023_1Q	In a Silent Way	Miles Davis	65	149
2023_2Q	In a Silent Way	Miles Davis	218	348
2023_3Q	In a Silent Way	Miles Davis	809	1,092

Vamos verificar o resultado e explicar o que é um total em execução.

Um total em execução é semelhante a um total cumulativo (ou soma), mas não são a mesma coisa. O total acumulado lhe dará a soma da linha atual e de todas as linhas anteriores, ou seja, o quadro da janela aumenta a cada linha. Um total em execução é uma soma em um quadro de janela definido que permanece com o mesmo tamanho, mas se move a cada linha. Em nosso caso, a janela é definida como a linha atual e as duas linhas anteriores.

Dê uma olhada nos valores destacados. O total em execução para o primeiro trimestre de 2022 é 428, o mesmo que a venda individual. Não há linhas anteriores, portanto o total em execução inclui apenas a linha atual.

O próximo total em execução é 428 + 1.053 = 1.481. Ele soma a linha atual e a anterior, pois há apenas uma linha anterior.

O total em execução para o terceiro trimestre de 2022 é 428 + 1.053 + 19 = 1.500. Essa é a primeira vez que você obtém a janela inteira, ou seja, a linha atual e as duas linhas anteriores.

Ao passar para a próxima linha, a janela se moverá, mas seu tamanho permanecerá o mesmo. O total em execução para o trimestre seguinte é 428 + 1.053 + 19 + 65 = 1.137. Novamente, ele envolve a linha atual e as duas linhas anteriores, mas diferentes em comparação com o trimestre.

Resolva este exercício para praticar

Reescreva a consulta acima para que ela calcule o total acumulado do álbum "The New Folk Sound of Terry Callier". O total acumulado deve ser calculado do primeiro ao último trimestre. Ele deve incluir quatro trimestres: os dois anteriores, o trimestre atual e o seguinte. Além disso, mostre o período de vendas, o título do álbum, o artista e o número de cópias vendidas.

Solução:

SELECT sales_period, 
	 album_title,
	 artist,
	 copies_sold,
	 SUM(copies_sold) OVER (ORDER BY sales_period ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS sales_running_total
FROM album_catalogue
WHERE album_title = 'The New Folk Sound of Terry Callier';

Saída:

sales_period	album_title	artist	copies_sold	sales_running_total
2022_1Q	The New Folk Sound of Terry Callier	Terry Callier	903	2,575
2022_2Q	The New Folk Sound of Terry Callier	Terry Callier	418	2,789
2022_3Q	The New Folk Sound of Terry Callier	Terry Callier	1,254	3,003
2022_4Q	The New Folk Sound of Terry Callier	Terry Callier	214	2,641
2023_1Q	The New Folk Sound of Terry Callier	Terry Callier	214	2,506
2023_2Q	The New Folk Sound of Terry Callier	Terry Callier	541	1,252
2023_3Q	The New Folk Sound of Terry Callier	Terry Callier	283	1,038

Exemplo nº 4: Diferença entre trimestres

Neste último exemplo, mostrarei como usar as funções de janela para calcular a diferença de vendas entre os trimestres:

SELECT *,
	 LAG(quarterly_copies_sold) OVER (ORDER BY sales_period) AS previous_quarter_sales,
	 quarterly_copies_sold - LAG(quarterly_copies_sold) OVER (ORDER BY sales_period) AS quarterly_sales_difference
FROM (SELECT sales_period,
	SUM(copies_sold) AS quarterly_copies_sold
FROM album_catalogue
GROUP BY sales_period) AS quarterly_sales; 

Primeiro, escrevo uma subconsulta que calcula o total de vendas de cada trimestre. Uso a função de agregação SUM() e agrupo os resultados pelo período de vendas.

Em seguida, seleciono todos os dados da subconsulta na consulta principal.

Agora preciso obter as vendas do trimestre anterior. Escreverei a função de janela LAG(), que é usada para acessar os valores das linhas anteriores. O valor que desejo acessar é especificado na função. Nesse caso, são as cópias trimestrais vendidas da subconsulta. Ao definir o argumento offset, a função permite que eu defina até onde quero ir. Como não o defini, o deslocamento padrão é um. Em outras palavras, a função obterá os dados da linha/trimestre anterior. Mas se você quiser retroceder duas linhas/quartos, escreva LAG(quarterly_copies_sold, 2).

Também uso ORDER BY em OVER() para garantir que os valores dentro do quadro sejam classificados do trimestre mais antigo para o mais recente.

Esse uso da função de janela é para que fique mais claro o que farei na próxima linha de código. É aqui que o cálculo real da comparação entre as vendas do trimestre atual e do trimestre anterior é feito. Agora é simples: subtraia a função de janela definida acima da coluna quarterly_copies_sold.

Aqui está o resultado:

sales_period	quarterly_copies_sold	previous_quarter_sales	quarterly_sales_difference
2022_1Q	9,519	NULL	NULL
2022_2Q	7,581	9,519	-1,938
2022_3Q	4,273	7,581	-3,308
2022_4Q	7,403	4,273	3,130
2023_1Q	7,403	7,403	0
2023_2Q	4,956	7,403	-2,447
2023_3Q	7,795	4,956	2,839

Não há valores anteriores para 2022_1Q, pois não há trimestre anterior. As vendas trimestrais para 2022_2Q são 7.581. As vendas no trimestre anterior foram 9.519. O cálculo mostra que as vendas atuais estão 1.938 cópias (7.581 - 9.519) abaixo das vendas do trimestre anterior.

Você pode analisar o restante da produção da mesma forma.

Resolva este exercício para praticar

Reescreva a consulta acima para que ela mostre a diferença entre as vendas trimestrais em uma base anual - por exemplo, compare o primeiro trimestre de 2023 com o primeiro trimestre de 2022. Mostre o período de vendas, as cópias vendidas no trimestre, as vendas do mesmo trimestre no ano anterior e a diferença ano a ano entre os trimestres.

Solução:

SELECT *,
	 LAG(quarterly_copies_sold, 4) OVER (ORDER BY sales_period) AS year_over_year_sales,
	 quarterly_copies_sold - LAG(quarterly_copies_sold, 4) OVER (ORDER BY sales_period) AS year_over_year_difference
FROM (SELECT sales_period,
	   SUM(copies_sold) AS quarterly_copies_sold
FROM album_catalogue
GROUP BY sales_period) AS quarterly_sales; 

Saída:

sales_period	quarterly_copies_sold	year_over_year_sales	year_over_year_difference
2022_1Q	9,519	NULL	NULL
2022_2Q	7,581	NULL	NULL
2022_3Q	4,273	NULL	NULL
2022_4Q	7,403	NULL	NULL
2023_1Q	7,403	9,519	-2,116
2023_2Q	4,956	7,581	-2,625
2023_3Q	7,795	4,273	3,522

Se você quiser mais, aqui estão outros exemplos de função de janela. Para materiais práticos, dê uma olhada nestes 11 exercícios de funções de janela do SQL.

SQL Funções de Janela (Window Functions) em SQL: Uma janela para uma melhor análise de dados

Este foi um artigo bastante abrangente sobre as funções de janela do SQL. Você aprendeu as funções de janela e como funciona cada parte crucial de sua sintaxe.

Você também sabe que há várias categorias de funções de janela. As mais comumente usadas são as funções de janela agregadas, de classificação e analíticas. Os exemplos práticos mostraram como as funções de janela podem ser usadas em tarefas comuns de análise de dados.

Espero que você não tenha pulado os exercícios do artigo. Se o fez, mais uma vez recomendo que você os resolva. Somente com a prática você poderá realmente entender o que são as funções de janela do SQL.

O recurso mais rico para aprender e praticar é o nosso Funções de Janela (Window Functions) em SQL curso. É um curso interativo que tem mais de 200 exercícios práticos e abrange a sintaxe completa das funções de janela. Se você tiver entrevistas de emprego agendadas, não deixe de examinar essas perguntas de entrevista sobre funções de janela SQL. Boa sorte e continue aprendendo SQL!