Qual é a cláusula do MySQL OVER?

Se você quiser aprender as funções de janela no MySQL, você precisa entender a cláusula OVER. Neste artigo, usamos exemplos do mundo real para explicar o que é a cláusula MySQL OVER, como ela funciona e porque ela é tão incrível.

Em 2018, o MySQL introduziu uma nova funcionalidade: funções de janela, que são acessadas através da cláusula OVER. As funções de janela são um recurso super poderoso disponível em quase todos os bancos de dados SQL. Elas realizam um cálculo específico (por exemplo, soma, contagem, média, etc.) em um conjunto de linhas; este conjunto de linhas é chamado de "janela" e é definido pela cláusula OVER do MySQL.

Neste artigo, explicaremos como utilizar a cláusula MySQL OVER em diferentes cenários. Isto também lhe introduzirá a várias funções de janela. Espero que, depois de ler este artigo, você esteja convencido de que vale a pena aprender as funções de janela. Você também saberá o básico de como aplicá-las em suas consultas!

Funções de Janela (Window Functions) em SQL: Uma característica muito requisitada

As funções Window estão disponíveis na maioria dos principais bancos de dados há bastante tempo, mas até 2018 não estavam disponíveis no MySQL. Para manter o MySQL atualizado, as funções de janela foram introduzidas no MySQL 8.02. Se você planeja trabalhar com o MySQL versão 8, vale a pena aprender as funções de janela e a cláusula OVER, pois elas são muito poderosas.

Quando você usaria as funções de janela? Muitas vezes elas são úteis, por exemplo, calculando o salário médio de um determinado grupo de funcionários. Neste caso, a definição do grupo é o ponto central; você não quer a média de todos os salários dos funcionários, e se você enganar o grupo, o resultado será errado. A definição de um grupo de registros é o motivo da cláusula OVER: ela dita onde a função de janela irá funcionar.

Ao passarmos por este artigo, criaremos alguns exemplos de consulta com base em um banco de dados contendo registros para um grupo de agricultores que produzem laranjas. Os fazendeiros compartilham seus dados de produção, que são armazenados no orange_production tabela que você vê abaixo :

O caso de uso mais simples é uma cláusula OVER vazia; isto significa que a janela de registros é o conjunto completo de registros retornados pela consulta. Por exemplo, se nossos agricultores quiserem ter um relatório de cada registro agrícola junto com o total da produção de laranja em 2017, nós escreveríamos esta consulta:

SELECT farmer_name, 
       kilos_produced, 
       SUM(kilos_produced) OVER() total_produced
 FROM  orange_production 
WHERE  crop_year = 2017

Aqui, a cláusula OVER constrói uma janela que inclui todos os registros devolvidos pela consulta - em outras palavras, todos os registros para o ano 2017. O resultado é:

Como funciona uma moldura de janela deslizante

Esse foi um exemplo muito simples da cláusula do MySQL OVER. A janela dos registros era estática (a janela era a mesma para todas as linhas retornadas pela consulta). Entretanto, um dos pontos fortes da cláusula OVER é ser capaz de criar uma janela dinâmica de registros (também chamada de janela deslizante). A janela de janelas deslizantes ou dinâmicas significa que a janela de registros pode ser diferente para cada linha retornada pela consulta. Além disso, a janela é criada com base na linha atual na consulta, de modo que as linhas na janela podem mudar quando a linha atual mudar.

Vejamos um exemplo de uma janela deslizante. Suponhamos que nossos agricultores queiram ver sua própria produção junto com a produção total da mesma variedade laranja.

SELECT farmer_name, 
       orange_variety, 
	 crop_year,
       kilos_produced, 
       SUM(kilos_produced) OVER(PARTITION BY orange_variety) AS total_same_variety
FROM orange_production 

A cláusula OVER(PARTITION BY orange_variety) cria janelas agrupando todos os registros com o mesmo valor na coluna orange_variety. Isto nos dá duas janelas: 'Golden' e 'SuperSun'. Na tabela abaixo, cada janela é mostrada em uma cor diferente:

Agora você pode ver o resultado da consulta:

Observe que a coluna Total Same Variety (na extrema direita) inclui a produção para todos os anos. Talvez cada agricultor prefira comparar sua produção com a produção total para a mesma variedade no mesmo ano. Esta comparação permite que eles vejam sua participação na taxa de produção. Para fazer isso, precisamos acrescentar a coluna crop_year à cláusula PARTITION BY. A consulta será a seguinte:

SELECT farmer, 
       orange_variety, 
	 crop_year,
       kilos_produced, 
       SUM(kilos_produced) OVER(PARTITION BY orange_variety, crop_year)                                                                                                                    AS total_same_variety_year
   FROM orange_production 

A cláusula OVER(PARTITION BY orange_variety, crop_year) cria janelas agrupando todos os registros com o mesmo valor nas colunas orange_variety e crop_year. Abaixo, usamos novamente cores diferentes para mostrar as janelas de linhas criadas por esta cláusula OVER:

E os resultados da consulta são:

Se você estiver interessado em ir mais fundo nas funções da janela MySQL, sugiro LearnSQL.com.br's Funções de Janela (Window Functions) em SQL curso, onde você pode encontrar uma descrição completa deste tópico e vários exemplos. Se você estiver usando funções de janela com freqüência, esta folha de consulta - um guia rápido super completo para funções de janela - é muito útil.

Mas vamos prosseguir com nossa própria exploração de OVER e funções de janela no MySQL primeiro.

Aprendendo a Cláusula MySQL OVER pelo Exemplo

Nesta seção, vamos explorar vários exemplos de consultas que mostram diferentes usos da cláusula OVER no MySQL.

Primeiro, usaremos a subcláusula ORDER BY na cláusula OVER. ORDER BY irá gerar uma janela com os registros ordenados por um critério definido. Algumas funções (como SUM(), LAG(), LEAD(), e NTH_VALUE()) podem retornar resultados diferentes, dependendo da ordem das linhas dentro da janela. Vamos supor que o Agricultor Pierre queira saber sua produção acumulada ao longo dos anos:

SELECT farmer, 
       crop_year,
       kilos_produced, 
       SUM(kilos_produced) OVER(ORDER BY crop_year) cumulative_previous_years
 FROM  orange_production
WHERE  farmer = ‘Pierre’

A função da janela SUM(kilos_produced) OVER(ORDER BY crop_year) funciona em uma janela ordenada. E considera apenas as linhas atuais e anteriores (ou seja, valores atuais e anteriores de crop_year ). Podemos ver o resultado deste SUM() cumulativo na tabela de resultados:

No próximo exemplo, combinaremos duas subcláusulas (PARTITION BY e ORDER BY) na cláusula OVER. Suponha que os agricultores queiram um relatório mostrando o total produzido por cada agricultor a cada ano e o total dos anos anteriores. Em seguida, precisamos dividir pela coluna farmer e encomendar por crop_year:

SELECT farmer, 
       crop_year,
       kilos_produced, 
       SUM(kilos_produced) OVER(PARTITION BY farmer ORDER BY crop_year) cumulative_kilos_produced
   FROM orange_production

A imagem a seguir mostra as janelas divididas por farmer_name em cores diferentes; observe que dentro de cada janela, as linhas são ordenadas por crop_year.

A expressão SUM(kilos_produced) OVER(PARTITION BY farmer ORDER BY crop_year) ordena as linhas na partição (que é baseada no valor farmer ) usando os valores crop_year. Você pode ver isto na tabela de resultados:

Para mais informações sobre a combinação das cláusulas PARTITION BY e ORDER BY, veja o artigo SQL Funções de Janela (Window Functions) em SQL por Explicação.

Usando Funções Posicionais em Janelas Ordenadas

Portanto, agora você pode ver a importância de poder pedir filas dentro de uma janela. Na verdade, algumas funções de janela MySQL funcionam apenas em janelas ordenadas. Por exemplo, a função LAG() nos permite obter um valor de coluna da linha anterior (relacionada à linha atual) em uma janela ordenada.

Digamos que queremos um relatório para mostrar a produção do ano anterior em comparação com a produção do ano atual. Para isso, usamos a função LAG() em uma janela encomendada por crop_year:

SELECT farmer, 
       crop_year,
       kilos_produced AS current_year_production, 
       LAG(kilos_produced) OVER(PARTITION BY farmer 
ORDER BY crop_year)AS previous_year_production
  FROM orange_production

A função LAG() pode retornar qualquer valor de coluna da linha anterior para a linha atual, como mostra o resultado da consulta a seguir:

A função LEAD() retorna qualquer valor de coluna da linha após a linha atual. Além disso, a função NTH_VALUE() retorna a linha em qualquer posição especificada em uma janela ordenada (por exemplo, primeira, segunda, última). Estas funções da janela MySQL ajudam a criar facilmente relatórios complexos.

Vamos tentar uma operação matemática usando as funções de janela do MySQL. Suponha que os fazendeiros queiram um relatório com o delta de produção relacionado ao ano anterior. Podemos utilizar a função LAG() para calcular a diferença entre as produções do ano anterior e as do ano atual:

SELECT farmer, 
       crop_year,
       kilos_produced current_year_production, 
       kilos_produced - LAG(kilos_produced) OVER(PARTITION BY farmer 
                         ORDER BY crop_year) AS production_delta
   FROM orange_production

A expressão kilos_produced - LAG(kilos_produced) OVER(PARTITION BY farmer ORDER BY crop_year) calcula o delta de produção entre o ano anterior e o atual, como podemos ver nos resultados:

Para aqueles leitores que querem ir mais fundo nas funções posicionais, sugiro o artigo Funções comuns da janela SQL: Funções Posicionais, que tem vários exemplos deste interessante tipo de função de janela.

Mais sobre o MySQL OVER e Funções de Janela (Window Functions) em SQL

Neste artigo, cobrimos vários exemplos de utilização da cláusula MySQL OVER, desde usos muito simples até usos bastante complexos. Saber como OVER funciona e que linhas ele inclui na janela é fundamental para o uso das funções de janela.

Há muitas funções de janela do MySQL que você pode experimentar: AVG(), MIN(), MAX(), LAG(), LEAD() e NTH_VALUE(). Todas elas utilizam a cláusula OVER da mesma forma que acabamos de explicar.

Finalmente, para aqueles leitores que querem aprender mais sobre as funções de janela do MySQL, eu recomendo o curso interativo LearnSQL Funções de Janela (Window Functions) em SQL. Você pode ler mais sobre isso em nosso post Curso SQL do Mês - Funções de Janela (Window Functions) em SQL. Você encontrará explicações detalhadas e muitos exemplos usando diferentes funções de janela no curso. E lembre-se - quando você aumenta suas habilidades no MySQL, você aumenta seus ativos!