Explicação das funções de janela do BigQuery

Manter-se atualizado com as tendências de análise de dados dá à sua organização - e ao seu currículo - a vanguarda. Neste artigo, daremos uma olhada nas funções de janela do BigQuery e como você pode usá-las para obter insights mais profundos sobre seus dados.

O BigQuery do Google, lançado em 2010, está ganhando força como uma opção popular entre as organizações que precisam analisar rapidamente grandes quantidades de informações e comparar seus próprios dados com dados estatísticos de domínio público.

Desde que o Google alinhou a linguagem de recuperação de dados do BigQuery para ficar em conformidade com o SQL padrão - e incluiu recursos avançados, como funções de janela SQL -, sua popularidade aumentou. Muitas organizações agora incluem as habilidades em BigQuery como um requisito obrigatório, e isso significa que as habilidades em SQL estão mais em demanda do que nunca. Este artigo explica por que o conhecimento de SQL é essencial para trabalhar com o BigQuery

O SQL continua ocupando seu lugar como a principal habilidade para quem precisa trabalhar com dados. Se você ainda não é um guru do SQL, talvez se interesse pela nossa trilha de aprendizadoSQL de A a Z . Ela inclui 7 cursos que o levam do iniciante absoluto ao especialista em SQL. O curso tem centenas de desafios de codificação do mundo real e leva cerca de 84 horas para ser concluído. Como você pode acessar os bancos de dados de amostra pelo navegador, não é necessário instalar nenhum software para começar.

O que é o BigQuery?

O Google BigQuery é um data warehouse de alta velocidade localizado na nuvem. Projetado especialmente para armazenar dados usados para análise, ele pode processar petabytes de dados em minutos. Se você ainda não entendeu o que são petabytes, um petabyte é um quatrilhão de bytes (ou um milhão de gigabytes).

Você paga pelo que usa no BigQuery, portanto, o custo de armazenar e analisar grandes quantidades de dados geralmente é muito menor do que investir em muitos discos rígidos. E como todos os tipos de dados estatísticos úteis de governos e organizações mundiais são armazenados publicamente no BigQuery, você pode acessá-los para obter insights sobre como o desempenho da sua organização pode ser aprimorado.

O que é SQL Funções de Janela (Window Functions) em SQL?

As funções de janela também são conhecidas como funções analíticas ou funções OVER. Elas foram adicionadas ao padrão SQL em 2003, e a maioria dos principais fornecedores de bancos de dados começou a implementá-las a partir de 2010. Portanto, são uma adição relativamente nova ao SQL.

Antes de as funções de janela serem incluídas no SQL, você podia listar linhas individuais ou calcular agregados, como totais e médias. Não era possível fazer as duas coisas com facilidade na mesma consulta, a menos que você escrevesse subconsultas complexas - e provavelmente lentas e ineficientes.

Isso significa que você poderia ter uma lista como esta ...

Student ID	Subject	Score
1	Math	63
1	Science	50
2	Math	59

... ou você poderia mostrar agregados como este:

Subject	Class Average
Math	52
Science	61
English	55
Overall Average	56

Nesse exemplo, se você quisesse saber como a pontuação de um aluno individual se comparava à média da turma, teria que examinar os dois relatórios lado a lado. Seria muito melhor se você pudesse ver a média da turma na mesma linha que a pontuação de um aluno, assim:

Student ID	Subject	Score	Class Average
1	Math	63	62
1	Science	50	61
2	Math	59	52

Como mencionei, você poderia fazer isso usando subconsultas. Mas as subconsultas são notoriamente lentas para serem executadas e podem tornar sua consulta muito complicada.

Esse é o tipo de coisa que as funções de janela permitem que você faça com facilidade e eficiência: incluir agregados juntamente com detalhes na mesma linha.

Por que elas são chamadas de Funções de Janela (Window Functions) em SQL?

Elas são chamadas de funções de janela porque, ao examinar uma linha individual, você também pode "olhar pela janela" e extrair informações de todo o conjunto de dados ou das linhas relacionadas à linha atual.

Como exemplo, vamos examinar uma tabela de notas dos alunos:

Student ID	Teacher ID	Subject	Grade
1	1	Math	63
2	1	Math	80
3	2	Math	60
4	2	Math	45
5	1	Math	52
6	1	Math	70
7	2	Math	65
1	2	Science	70
2	2	Science	62
3	2	Science	90
4	2	Science	30
5	2	Science	53
1	5	English	59
3	5	English	70
5	5	English	45
6	5	English	62
1	12	History	55
3	12	History	67
4	12	History	58

Conforme o exemplo anterior, queremos mostrar a média da turma ao lado de cada aluno, de modo que uma única linha tenha a seguinte aparência:

Student ID	Subject	Grade	Class Average
4	Math	45	62

Para isso, precisamos examinar a média de todos os outros alunos quando estivermos extraindo essa linha e mostrar o resultado junto com os outros dados.

No SQL, as funções de janela usam uma janela deslizante de linhas para extrair informações adicionais de todo o conjunto de dados ou de um subconjunto relacionado à linha atual.

No diagrama acima, a linha atual é um resultado de matemática e a janela inclui todas as notas de matemática.

Quando estivermos extraindo essa linha ...

Student ID	Subject	Grade	Class Average
1	Science	70	62

... precisaremos que a janela "deslize" para que possamos visualizar todos os resultados de ciências a fim de calcular a média de ciências.

As funções de janela têm muitos dos mesmos recursos que a cláusula GROUP BY, mas a diferença é que elas nos permitem visualizar agregados e detalhes lado a lado.

Sintaxe do BigQuery Funções de Janela (Window Functions) em SQL

A cláusula OVER()

A cláusula OVER() indica que você está usando uma função de janela. Você escreveria sua consulta como de costume e incluiria os agregados desejados junto com os nomes das outras colunas. Cada agregado é identificado com a cláusula OVER(). Quando você usa essa cláusula isoladamente, a "janela" é o conjunto de dados inteiro. Falarei sobre janelas deslizantes um pouco mais adiante neste artigo.

Por exemplo, se você quisesse extrair todos os resultados de matemática e mostrar a média da turma e a nota mais alta e mais baixa em relação à nota de cada aluno, sua consulta seria semelhante a esta:

SELECT 
  student_id,
  grade,
  AVG(grade) OVER() AS average,
  MIN(grade) OVER() AS lowest,
  MAX(grade) OVER() AS highest
FROM exam_results
WHERE subject = 'Math';

Seus resultados teriam a seguinte aparência:

student_id	grade	average	lowest	highest
1	63	62	45	80
2	80	62	45	80
3	60	62	45	80
4	45	62	45	80
5	52	62	45	80
6	70	62	45	80
7	65	62	45	80

A cláusula PARTITION BY

Essa cláusula usa uma janela deslizante. Em vez de uma janela que contém todo o conjunto de dados, ela inclui apenas uma partição (ou parte) do conjunto.

No exemplo anterior, incluí somente os resultados matemáticos, excluindo todos os outros usando a cláusula WHERE. Se você quisesse um relatório que mostrasse os resultados de todas as disciplinas, mas calculasse a média usando somente as linhas em que a disciplina correspondesse à linha atual, você usaria a cláusula PARTITION BY:

SELECT 
  student_id,
  subject,
  grade,
  AVG(grade) OVER(PARTITION BY subject) AS average
FROM exam_results;

Dê uma olhada nesta cópia com código de cores da tabela de notas dos alunos para ver como as partições funcionarão:

Student ID	Teacher ID	Subject	Grade
1	1	Math	63
2	1	Math	80
3	2	Math	60
4	2	Math	45
5	1	Math	52
6	1	Math	70
7	2	Math	65
1	2	Science	70
2	2	Science	62
3	2	Science	90
4	2	Science	30
5	2	Science	53
1	5	English	59
3	5	English	70
5	5	English	45
6	5	English	62
1	12	History	55
3	12	History	67
4	12	History	58

Ao processar cada linha, as linhas incluídas na janela mudam com base no valor da coluna subject. Isso significa que a média é calculada somente para a partição do conjunto de dados em que o assunto corresponde à linha atual. Você pode visualizá-la da seguinte forma:

Os resultados seriam os seguintes:

Student ID	Subject	Grade	Class Average
1	Math	63	62
2	Math	80	62
3	Math	60	62
4	Math	45	62
5	Math	52	62
6	Math	70	62
7	Math	65	62
1	Science	70	61
2	Science	62	61
3	Science	90	61
4	Science	30	61
5	Science	53	61
1	English	59	59
3	English	70	59
5	English	45	59
6	English	62	59
1	History	55	60
3	History	67	60
4	History	58	60

A cláusula ORDER BY

A cláusula ORDER BY dentro da função OVER() usa um tipo diferente de janela deslizante. Quando você usa OVER(ORDER BY column_name), a janela inclui somente as linhas em que o valor da coluna especificada é menor ou igual ao valor da coluna na linha atual.

A cláusula ORDER BY é útil para calcular totais em execução e médias móveis. Como exemplo, usarei uma tabela chamada monthly_transactions que contém transações de contas bancárias:

account_id	tran_date	transaction	value
1	2023-09-01	Opening Balance	500.00
1	2023-09-03	Deposit	137.45
1	2023-09-12	Withdrawal	-200.00
1	2023-09-18	Withdrawal	-250.00
2	2023-09-01	Opening Balance	1200.00
2	2023-09-14	Deposit	900.00
2	2023-09-20	Purchase	-318.90

A consulta abaixo listará as transações da ID de conta 1, mostrando um saldo em andamento.

SELECT 
  account_id,
  tran_date,
  transaction,
  value,
  SUM(value) OVER(ORDER BY tran_date) AS balance
FROM monthly_transactions
WHERE account_id = 1;

A inclusão da cláusula ORDER BY dentro da cláusula OVER controla uma janela deslizante.

Se desejar, você também pode usar a cláusula ORDER BY usual no final da consulta para controlar a ordem final das linhas no relatório. Elas não precisam ser mostradas na ordem original.

Por padrão, o uso de ORDER BY dentro da cláusula OVER faz com que a janela deslize de modo a exibir apenas as linhas em que a data é menor ou igual à data da linha atual. Há outras palavras-chave que podem alterar esse padrão, mas elas estão um pouco além do escopo deste artigo.

Você pode visualizar dessa forma:

Os resultados seriam os seguintes:

account_id	tran_date	transaction	value	balance
1	2023-09-01	Opening Balance	500.00	500.00
1	2023-09-03	Deposit	137.45	637.45
1	2023-09-12	Withdrawal	-200.00	437.45
1	2023-09-18	Withdrawal	-250.00	187.45
2	2023-09-01	Opening Balance	1200.00	1200.00
2	2023-09-14	Deposit	900.00	2100.00
2	2023-09-20	Purchase	-318.90	1781.10

Uso de ORDER BY com a cláusula PARTITION BY

Se quiser mostrar todas as contas com seus saldos correntes, você pode usar PARTITION BY e ORDER BY juntos:

SELECT 
  account_id,
  tran_date,
  transaction,
  value,
  SUM(value) OVER(PARTITION BY account_id ORDER BY tran_date) AS balance
FROM monthly_transactions
ORDER BY account_id, tran_date

A cláusula PARTITION BY fará com que a janela deslizante inclua somente as linhas em que account_id corresponda à linha atual. A cláusula ORDER BY fará com que a janela deslizante inclua somente linhas dentro dessa partição em que a data seja menor ou igual à data da linha atual.

Os resultados seriam os seguintes:

account_id	tran_date	transaction	value	balance
1	2023-09-01	Opening Balance	500.00	500.00
1	2023-09-03	Deposit	137.45	637.45
1	2023-09-12	Withdrawal	-200.00	437.45
1	2023-09-18	Withdrawal	-250.00	187.45
2	2023-09-01	Opening Balance	1200.00	1200.00
2	2023-09-14	Deposit	900.00	2100.00
2	2023-09-20	Purchase	-318.90	1781.10

Cada conta tem seu próprio saldo corrente separado.

SQL Funções de Janela (Window Functions) em SQL disponível no BigQuery

Você viu como as funções agregadas comuns do SQL, como SUM(), AVG(), MIN() e MAX() podem ser usadas em conjunto com a cláusula OVER para extrair agregados de uma janela de dados.

O Google BigQuery, assim como muitos outros dialetos SQL, tem funções adicionais que podem fornecer insights mais profundos sobre os dados. Aqui estão alguns exemplos.

RANK()

Essa função classifica o conjunto de dados do mais alto para o mais baixo em uma coluna especificada. Ela pode responder a perguntas como:

• Qual foi a posição de cada aluno na classe, com base nos resultados dos exames?
• Quais produtos foram mais lucrativos?
• Quais clientes gastaram mais dinheiro?
• Qual depósito recebeu o maior número de reclamações?

Usando a tabela de amostra que vimos anteriormente, vamos classificar os alunos por resultados de exames usando essa consulta:

SELECT 
  student_id,
  subject,
  grade,
  RANK() OVER(PARTITION BY subject ORDER BY grade desc) AS place
FROM exam_results
ORDER BY subject, grade DESC;

O resultado seria:

student_id	subject	grade	place
3	English	70	1
6	English	62	2
1	English	59	3
5	English	45	4
3	History	67	1
4	History	58	2
1	History	55	3
2	Math	80	1
6	Math	70	2
7	Math	65	3
1	Math	63	4
3	Math	60	5
5	Math	52	6
4	Math	45	7
3	Science	90	1
1	Science	70	2
2	Science	62	3
5	Science	53	4
4	Science	30	5

Ao calcular esses resultados, a cláusula PARTITION BY subject faz com que o SQL examine apenas os resultados da mesma matéria da linha atual. A cláusula ORDER BY grade DESC os classifica em ordem decrescente de nota. A função RANK() classifica os alunos nessa ordem.

Como o aluno 3 tem a nota mais alta em inglês, sua classificação é 1; o aluno 6, o próximo mais alto, tem a classificação 2 nessa matéria. O aluno 2 tem a nota mais alta em matemática e é classificado como 1.

DENSE_RANK()

DENSE_RANK() é usado com a mesma finalidade que RANK. A diferença entre eles pode ser melhor explicada observando-se esses resultados de consulta, que representam pontuações em uma competição de tiro.

Usando RANK, a consulta seria:

SELECT 
  competitor_no,
  score,
  RANK() OVER(ORDER BY score desc) AS rank
FROM match_results
ORDER BY score DESC;

Os resultados são:

Competitor No	Score	Rank
4	85	1
5	83	2
10	83	2
9	81	4
2	76	5
6	76	5
7	72	7
3	70	8
8	68	9
1	62	10

Os concorrentes 5 e 10 empataram em segundo lugar e ambos receberam a classificação 2. O concorrente 9 é o próximo mais alto e recebeu a classificação 4. O terceiro lugar não foi incluído.

Usando DENSE_RANK, a consulta é:

SELECT 
  competitor_no,
  score,
  DENSE RANK() OVER(ORDER BY score desc) AS rank
FROM match_results
ORDER BY score DESC;

Os resultados são:

Competitor No	Score	Rank
4	85	1
5	83	2
10	83	2
9	81	3
2	76	4
6	76	4
7	72	5
3	70	6
8	68	7
1	62	8

Os concorrentes 5 e 10 ainda estão classificados como 2, mas o terceiro lugar não foi excluído: O concorrente 9 agora tem uma classificação de 3.

Ambas as funções têm a mesma sintaxe. Se quiséssemos recodificar a consulta de notas dos alunos anteriores usando a função DENSE_RANK(), ela teria a seguinte aparência:

SELECT 
  student_id,
  subject,
  grade,
  DENSE_RANK() OVER(PARTITION BY subject ORDER BY grade desc) AS class_place
FROM exam_results
ORDER BY subject, grade DESC

Mas como não há valores empatados, o resultado seria o mesmo.

ROW_NUMBER()

A função ROW_NUMBER() funciona de forma semelhante às duas funções anteriores, mas as linhas são simplesmente numeradas em ordem. Se as linhas tiverem o mesmo valor, elas serão numeradas consecutivamente, dependendo de qual foi encontrada primeiro. Aqui estão os resultados da consulta da competição de tiro usando ROW_NUMBER() em vez de RANK() ou DENSE_RANK():

Competitor No	Score	Rank
4	85	1
5	83	2
10	83	3
9	81	4
2	76	5
6	76	6
7	72	7
3	70	8
8	68	9
1	62	10

LAG()

Essa função permite que você compare os dados da linha anterior do conjunto de resultados com os dados da linha atual. É ideal para comparações ano a ano, permitindo que você descubra tendências e identifique problemas de desempenho comercial.

LAG() e a função relacionada LEAD() só pode ser usada em conjunto com a cláusula OVER(ORDER BY).

Como exemplo, veja a tabela a seguir, que contém dados de vendas de uma pequena empresa:

year	sales_value	sales_quantity	profit
2019	54000	900	8000
2020	75000	1200	11000
2021	30000	450	1000
2022	60000	10000	7000

A consulta de amostra para comparar os números ano a ano é:

SELECT 
  year,
  sales_value,
  sales_quantity,
  profit,
  LAG(sales_value) OVER(ORDER BY year) as ly_value,
  LAG(sales_quantity) OVER(ORDER BY year) as ly_qty,
  LAG(profit) OVER(ORDER BY year) as ly_profit,
  profit - LAG(profit) OVER(ORDER BY year) as inc_dec
FROM annual_sales
ORDER BY year;

Os resultados dessa consulta são:

year	sales_value	sales_quantity	profit	ly_value	ly_qty	ly_profit	inc_dec
2019	54000	900	8000	NULL	NULL	NULL	NULL
2020	75000	1200	11000	54000	900	8000	3000
2021	30000	450	1000	75000	1200	11000	-10000
2022	60000	10000	7000	30000	450	1000	6000

Vamos dar uma olhada nessa linha da consulta e ver o que ela realmente fez:

LAG(sales_value) OVER(ORDER BY year) as ly_value

Nessa linha do resultado ...

year	sales_value	sales_quantity	profit	ly_value	ly_qty	ly_profit	inc_dec
2020	75000	1200	11000	54000	900	8000	3000

... nossa linha atual é para o ano de 2020. A função LAG() em conjunto com ORDER BY year faz com que o SQL examine a linha do ano anterior (2019) e extraia o valor de vendas dela sob o título ly_value.

Você notará que, na primeira linha, as colunas calculadas pela função LAG() contêm um valor nulo, pois não há registro anterior.

LEAD()

A função LEAD() é o inverso de LAG(): ela obtém dados da linha após a linha atual, em vez da anterior.

Para comparar o lucro entre o ano atual, o ano anterior e o ano seguinte usando a mesma tabela de amostra, a consulta seria

SELECT 
  year,
  profit,
  LAG(profit) OVER(ORDER BY year) as ly_profit,
  profit - LAG(profit) OVER(ORDER BY year) as ly_inc_dec,
  LEAD(profit) OVER(ORDER BY year) as ny_profit,
  LEAD(profit) OVER(ORDER BY year) - profit as ny_inc_dec
FROM annual_sales
ORDER BY year;

Os resultados dessa consulta seriam:

year	profit	ly_profit	ly_inc_dec	ny_profit	ny_inc_dec
2019	8000	NULL	NULL	11000	3000
2020	11000	8000	3000	1000	-10000
2021	1000	11000	-10000	7000	6000
2022	7000	1000	6000	NULL	NULL

As colunas ny_profit e ny_inc_dec usam a função LEAD(), que faz com que o SQL examine a próxima linha em sequência para extrair esses campos. Quando a linha atual for de 2019, esses dois campos serão extraídos da linha de 2020.

Isso deve ter lhe dado uma ideia de algumas das funções de janela SQL úteis disponíveis no BigQuery. Você encontrará uma lista completa de funções na documentação SQL do BigQuery.

Para obter mais exemplos de funções de janela, consulte o artigo Exemplos de funções de janela SQL. Você também pode encontrar um guia de referência rápida para a sintaxe das funções de janela SQL em nosso Funções de Janela (Window Functions) em SQL Cheat Sheet.

Usos práticos do BigQuery Funções de Janela (Window Functions) em SQL

No mundo real, há muitas maneiras pelas quais as funções de janela do BigQuery podem lhe fornecer insights para ajudar sua organização a ter um desempenho melhor. Elas são muito poderosas e permitem que você produza relatórios complexos com muita rapidez.

Aqui estão algumas ideias de como as funções de janela do BigQuery podem ser usadas:

• Classificar seus funcionários por desempenho para dar prêmios motivacionais. Confira este artigo para obtermais informações sobre linhas de classificação.
• Descobrir o desempenho de cada linha de produto em comparação com outros itens semelhantes.
• Saber como os produtos individuais afetam a média móvel de lucro. Você pode ler mais sobre o cálculo de médias móveis em SQL aqui.
• Comparar dados ano a ano para descobrir tendências. Saiba mais sobre como preparar comparações ano a ano em SQL neste artigo.
• Usar totais correntes para poder ver exatamente quantas vendas foram feitas em um determinado momento. Saiba mais sobre o cálculo de totais em execução no SQL aqui.

Próximas etapas com o BigQuery Funções de Janela (Window Functions) em SQL

Agora que você já viu o que as funções de janela do SQL podem fazer no BigQuery e em outros sistemas de gerenciamento de banco de dados, é hora de pensar em aprimorar suas habilidades nessa importante área.

Um bom lugar para começar é o curso SQL Funções de Janela (Window Functions) em SQL do site LearnSQL.com.br. Você aprenderá passo a passo como usar as técnicas que viu neste artigo, com uma explicação completa de cada tópico. Você também terá muita prática, com mais de 200 exercícios interativos para garantir que saiba como extrair informações complexas em situações do mundo real. A ajuda está disponível em caso de dúvidas, e você poderá acessar bancos de dados de amostra por meio do navegador. O curso leva cerca de 20 horas para ser concluído.

Se você realmente quiser se tornar um especialista, poderá praticar mais a solução de problemas complexos trabalhando no nosso Funções de Janela (Window Functions) em SQL Practice Set. Você terá 100 exemplos de relatórios complexos usando três bancos de dados diferentes.

Você também pode ler este artigo sobre como praticar funções de janela, e pode saber mais sobre a sintaxe do BigQuery aqui.

Se estiver procurando um emprego de alto nível em análise de dados ou ciência de dados, é muito provável que você seja solicitado a demonstrar seu conhecimento de funções de janela SQL na entrevista. Para ter uma ideia do tipo de perguntas que podem ser feitas a você (e como respondê-las), aqui está um artigo que discute as principais perguntas da entrevista sobre funções de janela SQL.

Dê os primeiros passos hoje para levar suas habilidades de análise de dados para o próximo nível!