Uma visão geral das funções de janela do MySQL

As funções de janela do MySQL são muito úteis quando você deseja criar relatórios significativos com SQL. Neste artigo, demonstraremos as funções de janela mais comuns no MySQL e explicaremos como usá-las.

O MySQL 8.0 introduziu um novo recurso: funções de janela. Essas funções são muito úteis para analistas de dados e para qualquer pessoa que crie relatórios usando o MySQL. Com elas, você pode calcular facilmente médias móveis, somas cumulativas e outros cálculos sobre subconjuntos específicos de seus dados. E você pode fazer isso sem criar consultas SQL complexas ou tabelas temporárias.

Se você é um usuário regular de SQL, talvez já tenha se deparado com funções de janela em seu trabalho. Embora muitos bancos de dados já tenham essas funções há algum tempo, o MySQL ficou para trás até 2018. Com o MySQL 8, a plataforma está agora mais rápida!

Neste artigo, exploraremos os benefícios das funções de janela no MySQL e como elas podem aprimorar sua análise de dados. Se você quiser praticar o uso das funções de janela, confira nosso curso interativo Funções de Janela (Window Functions) em SQL in MySQL 8. Ele oferece mais de 200 exercícios interativos sobre as funções de janela do MySQL.

O que são Funções de Janela (Window Functions) em SQL no MySQL?

Uma função de janela SQL executa cálculos em um conjunto de linhas da tabela que estão relacionadas à linha atual. Esse conjunto de linhas é chamado de janela ou quadro de janela - é daí que vem o termo "funções de janela".

Vamos começar com um exemplo simples. Imagine que você gostaria de calcular a soma dos valores em todas as linhas, mas quer que o resultado seja mostrado em cada linha. Você pode precisar dessas informações para comparar valores individuais com o total durante a análise de dados.

Isso é muito fácil se você souber como usar as funções de janela! O resultado de sua consulta teria a seguinte aparência:

Você deve ter notado que as funções de janela são semelhantes às funções de agregação. Ambas calculam um valor agregado para um determinado grupo de linhas. Entretanto, diferentemente da cláusula GROUP BY, as funções de janela no SQL não comprimem as linhas. Em vez disso, a tabela resultante mostra os valores individuais e agregados. Isso pode ser útil em relatórios em que você precisa trabalhar com os valores agregados e não agregados ao mesmo tempo.

Como definir uma função de janela: A cláusula OVER()

As funções de janela são definidas usando a cláusula OVER():

SELECT …,
   <window_function> OVER(...),
   …
FROM …

A cláusula OVER() diz ao banco de dados para usar uma função de janela. A forma mais simples da moldura da janela é quando os colchetes são deixados vazios, assim: OVER(). Isso significa que a janela consiste em todas as linhas da tabela.

Cláusulas adicionais podem ser incluídas na cláusula OVER() para definir melhor a janela. Neste artigo, vamos nos concentrar nas cláusulas PARTITION BY e ORDER BY. Há outras cláusulas que podem ser usadas em OVER(), mas não as abordaremos neste artigo. Se você quiser se aprofundar ainda mais, confira nosso curso Funções de Janela (Window Functions) em SQL in MySQL 8. Ou você pode visitar este artigo sobre as funções de janela do MySQL que fornece alguns ótimos exemplos de como usá-las em suas consultas.

Exemplos de consultas de função de janela no MySQL

Vamos examinar alguns exemplos de consultas para entender melhor onde e como você pode usar as funções de janela.

Em nosso cenário de exemplo, temos um site que permite que os usuários participem de testes. Há várias categorias de testes e o número máximo de pontos que os participantes do teste podem obter é 100.

Para armazenar as pontuações dos participantes, esse site usa a tabela participant tabela. Ela tem as seguintes colunas:

• id - O ID do participante, que também é a chave primária (PK) da tabela.
• name - O nome do participante.
• quiz_score - A pontuação do participante.
• quiz_date - A data em que o questionário foi tentado.
• quiz_category - A categoria do questionário.

Aqui você pode ver algumas linhas da tabela:

Agora que você já está familiarizado com os dados, vamos começar a usar as funções de janela! Talvez você ache esta Folha de dicas do SQL Funções de Janela (Window Functions) em SQL útil como um guia de referência rápida à medida que formos analisando os exemplos.

Exemplo 1: Cláusula OVER() vazia - Cálculo de uma estatística para todas as linhas

Digamos que gostaríamos de retornar a pontuação de cada participante, a categoria do questionário que ele tentou e a pontuação mais alta já obtida em todos os questionários. Podemos fazer isso usando uma cláusula OVER() vazia. Dessa forma, nossa janela incluirá todas as linhas da consulta.

Aqui está a consulta que executamos:

SELECT 
  name,
  quiz_score,
  quiz_category,
  MAX(quiz_score) OVER() AS max_score
FROM participant;

E o resultado será o seguinte:

A função MAX() foi aplicada a todas as linhas da nossa consulta. Você pode ver que a pontuação mais alta foi 100 e ela é exibida para todas as linhas junto com as pontuações individuais.

Você pode usar a cláusula vazia OVER() com outras funções, como COUNT(), SUM(), AVG(), entre outras. Isso permite que você calcule uma estatística geral para todas as linhas da consulta; você pode comparar essa estatística geral com o valor de cada linha individual.

Você pode ler mais sobre o uso da cláusula OVER() no MySQL em nosso artigo Qual é a cláusula do MySQL OVER?

Exemplo 2: OVER() com ORDER BY - Cálculo de uma classificação

Quando usada na cláusula OVER(), ORDER BY determina a ordem em que as linhas são ordenadas no quadro da janela. Vejamos um exemplo:

Podemos usar essa consulta para criar uma classificação das pontuações do questionário:

SELECT 
  name, 
  quiz_score,
  quiz_category,
  RANK() OVER(ORDER BY quiz_score DESC) AS rank
FROM participant;

A função de janela RANK() atribui uma classificação a cada linha em uma partição; essa classificação é baseada no valor de uma expressão especificada. A primeira linha recebe a classificação 1, a segunda linha recebe a classificação 2, etc.

Mais especificamente, a função RANK() atribui uma classificação exclusiva a cada valor distinto da expressão dentro da partição. As linhas com o mesmo valor terão a mesma classificação, e a próxima classificação será ignorada. Por exemplo, se duas linhas tiverem o mesmo valor e receberem uma classificação de 1, a próxima classificação atribuída será 3, pulando a classificação 2. Você pode ler mais sobre as funções de janela de classificação no SQL em nosso blog.

Aqui, usamos a função RANK() para calcular a classificação da pontuação do questionário de cada participante. A cláusula OVER() com a cláusula ORDER BY determina a ordem em que a função RANK() é aplicada. Nesse caso, a cláusula ORDER BY é definida como quiz_score DESC, o que significa que as pontuações do questionário são ordenadas em ordem decrescente (da mais alta para a mais baixa) antes que a classificação seja calculada. A primeira linha (com o valor mais alto) recebe a classificação 1, a segunda linha recebe a classificação 2, etc.

Aqui está o que o código retorna:

Muito bem! Conseguimos atribuir uma classificação a cada participante.

Use a cláusula OVER (ORDER BY) no MySQL quando quiser aplicar uma função às linhas em uma ordem específica. Isso pode ser útil ao calcular totais em execução, médias móveis e criar várias classificações.

Exemplo 3: OVER() com PARTITION BY - Cálculo de uma estatística para cada partição

Vamos fazer mais uso da coluna category. Lembra-se do primeiro exemplo de consulta que fizemos? Para cada participante, exibimos sua pontuação, a categoria do questionário que ele tentou e a pontuação mais alta já obtida em todos os questionários. Desta vez, gostaríamos de fazer algo semelhante. Entretanto, em vez de mostrar a pontuação mais alta já obtida em todos os testes, mostraremos a pontuação mais alta já obtida na categoria do teste.

Para isso, precisaremos da cláusula OVER() com PARTITION BY. Particionar dados no SQL significa dividir um conjunto de linhas em grupos menores com base em uma ou mais colunas especificadas. É um pouco semelhante à cláusula GROUP BY, mas as funções de janela não reduzem as linhas.

Podemos usar essa consulta:

SELECT 
  name,
  quiz_score,
  quiz_category,
  MAX(quiz_score) OVER(PARTITION BY quiz_category) 
    AS max_score_in_category
FROM participant;

A cláusula PARTITION BY com a cláusula OVER() determina a partição de dados sobre a qual a função MAX() é aplicada. Nesse caso, a cláusula PARTITION BY é definida como quiz_category, o que significa que os dados são divididos em partições com base na categoria do questionário. Isso é exatamente o que queríamos!

Aqui está o resultado:

Para cada participante, exibimos sua pontuação individual e a pontuação mais alta em sua categoria. Parece justo, pois o teste de música poderia ser mais difícil do que o teste de ciências!

OVER (PARTITION BY) A cláusula "cumulative sum" no MySQL é útil para calcular somas cumulativas ou valores médios, criar classificações dentro de grupos, identificar os melhores ou piores desempenhos e muito mais. Essa cláusula oferece flexibilidade e funcionalidade avançada para consultas SQL, permitindo análise e manipulação de dados poderosas em subconjuntos de dados. Você pode saber mais sobre o uso do SQL PARTITION BY com OVER em outro lugar do nosso blog.

Isso foi fácil, certo? Vamos tentar algo mais complicado!

Exemplo 4: OVER() com PARTITION BY e ORDER BY - Cálculo de uma estatística para cada partição em uma ordem específica

Podemos usar PARTITION BY e ORDER BY ao mesmo tempo? Claro que sim! Essa combinação é útil em muitas situações.

No MySQL, o uso da cláusula OVER() com PARTITION BY e ORDER BY permite executar cálculos e análises em partições específicas de dados e, ao mesmo tempo, controlar a ordem em que os cálculos são aplicados em cada partição.

A cláusula PARTITION BY divide o conjunto de resultados em partições distintas com base em colunas ou expressões especificadas. Cada partição é tratada separadamente para o cálculo ou a análise.

A cláusula ORDER BY, quando usada com OVER(), determina a ordem em que os dados são processados em cada partição. Ela especifica a coluna ou expressão pela qual os dados devem ser classificados.

Vamos ver essa dupla em ação. Neste exemplo, calcularemos a média cumulativa das pontuações do questionário por categoria. Uma média cumulativa é a média de um conjunto de valores até um determinado ponto.

Essa é a consulta que usaremos:

SELECT
  name,
  quiz_date,
  quiz_score,
  quiz_category,
  ROUND(
    AVG(quiz_score) 
OVER(PARTITION BY quiz_category ORDER BY quiz_date)
  ) AS cumulative_avg
FROM participant;

Gostaríamos de ver a média acumulada das pontuações do questionário por categoria. Para conseguir isso, usamos PARTITION BY quiz_category, exatamente como fizemos da última vez. Além disso, faz sentido que a média cumulativa seja calculada da data mais antiga para a mais recente, portanto, usamos ORDER BY quiz_date. Isso significa que os dados dentro de cada partição são classificados pela data do questionário em ordem crescente (do maior para o menor) antes de a função AVG() ser aplicada.

Esta é a aparência do resultado da consulta:

Ao usar PARTITION BY e ORDER BY juntos dentro da cláusula OVER(), o cálculo da média é aplicado separadamente para cada categoria de questionário. Dentro de cada categoria, ela é calculada na ordem das datas do questionário. Isso significa que, para cada linha no conjunto de resultados, o cálculo da média considera apenas as linhas dentro da mesma categoria de questionário e as ordena por data. A coluna cumulative_avg refletirá a pontuação média até a linha atual para cada categoria de questionário, levando em conta a ordem das datas dos questionários.

Pronto para praticar o MySQL Funções de Janela (Window Functions) em SQL?

Como você pode ver, as funções de janela no MySQL são uma ferramenta muito poderosa que pode ajudá-lo a criar relatórios complexos. Você pode usar as funções de janela no MySQL para criar rankings e calcular métricas ano a ano, médias móveis e muito mais!

Neste artigo, apenas arranhamos a superfície de todas as maneiras pelas quais você pode usar as funções de janela. Se você quiser se aprofundar e explorar mais exemplos de como usá-las, confira estes artigos sobre funções de janela SQL e a cláusula MySQL OVER().

E para mais prática, lembre-se de conferir nosso curso Funções de Janela (Window Functions) em SQL in MySQL 8!