Funções da Janela SQL vs. GROUP BY: Qual é a diferença?

Um equívoco muito comum entre os usuários de SQL é que não há muita diferença entre funções de janela SQL e funções agregadas ou a cláusula GROUP BY. Entretanto, as diferenças são muito significativas.

Talvez o único ponto semelhante entre GROUP BY e funções de janela é que ambas permitem executar uma função (como AVG, MAX, MIN, ou COUNT) em um grupo de registros. Eu diria que o poder especial das funções de janela é que elas nos permitem obter resultados que de outra forma seriam quase impossíveis de se obter.

Neste artigo, vamos rever o uso de funções de janela vs GROUP BY e funções de janela vs funções agregadas.

Revisão rápida de GROUP BY

A cláusula GROUP BY nos permite agrupar um conjunto de registros com base em alguns critérios e aplicar uma função (por exemplo, AVG ou MAX) a cada grupo, obtendo um resultado para cada grupo de registros. Vamos ver um exemplo.

Temos uma tabela chamada employee com um total de cinco funcionários e três departamentos:

fig1: a tabela de funcionários

Suponhamos que queremos obter o salário médio por departamento e o salário máximo para cada departamento. Devemos utilizar a seguinte consulta:

SELECT Department, 
       avg(salary) as average,
       max(salary) as top_salary
FROM employee
GROUP BY department

A imagem abaixo mostra o resultado:

GROUP BY vs Funções de Janela (Window Functions) em SQL

Ao comparar as funções de janela e GROUP BY, é essencial lembrar que GROUP BY colapsa os registros individuais em grupos; após usar GROUP BY, você não pode se referir a nenhum campo individual porque ele está colapsado. Mais tarde, falaremos em profundidade sobre este tópico. Por enquanto, mencionaremos apenas que as funções de janela não colapsam os registros individuais.

Portanto, se você quiser criar um relatório com o nome de um funcionário, salário e o salário máximo do departamento do funcionário, você não pode fazê-lo com GROUP BY. Os registros individuais de cada funcionário são colapsados pela cláusula GROUP BY department. Para este tipo de relatório, você precisa usar funções de janela, que é o tópico da próxima seção.

Se você quiser aprofundar as nuances do SQL GROUP BY e relatórios, recomendamos nosso curso interativo Como Criar Relatórios Básicos em SQL.

O Poder de Funções de Janela (Window Functions) em SQL

As funções de janela são uma característica poderosa do SQL. Elas nos permitem aplicar funções como AVG, COUNT, MAX, e MIN em um grupo de registros, deixando os registros individuais acessíveis. Como os registros individuais não são colapsados, podemos criar consultas mostrando os dados do registro individual junto com o resultado da função janela. Isto é o que torna as funções de janela tão poderosas.

Suponhamos que queremos obter uma lista de nomes de funcionários, salários e o salário mais alto em seus departamentos.

SELECT 	employee_name,
department,
salary,
max(salary) OVER (PARTITION BY department) as top_salary
FROM		employee

A imagem seguinte mostra o resultado:

Na consulta anterior, utilizamos uma função de janela:

max(salary) OVER (PARTITION BY department) as top_salary

A função de janela é MAX() e nós a aplicamos ao conjunto de registros definidos pela cláusula OVER (PARTITION BY department)que são os registros com o mesmo valor no campo do departamento. Por fim, renomeamos a coluna top_salary. No resultado da consulta, temos filas para funcionários individuais. Se usássemos GROUP BY ao invés de funções de janela, teríamos linhas para cada departamento.

As funções de janela têm uma sintaxe bastante verbosa; se você quiser entrar em detalhes, sugiro o curso "Funções de Janela (Window Functions) em SQL", que é um tutorial passo a passo que o leva através das funções de janela SQL usando exemplos e exercícios.

Funções de Janela (Window Functions) em SQL vs Funções Agregadas

Quando comparamos funções de janela e funções agregadas, notamos uma característica super poderosa no lado das funções de janela: funções posicionais. Elas nos permitem obter um valor de coluna de outros registros na mesma janela. Esta é uma capacidade realmente incrível, permitindo aos usuários SQL criar relatórios complexos em apenas algumas linhas. Vamos discutir brevemente duas destas funções: LEAD() e LAG().

A função LAG() retorna o valor da coluna do registro anterior na janela, enquanto LEAD() retorna o valor da coluna do próximo registro na janela. É muito importante ter a janela ordenada pela coluna da direita, se você quiser usar estas funções.

Vamos ver um exemplo de como podemos usar estas funções. Suponha que tenhamos uma tabela que armazene as ações da empresa com seus valores de mercado em um determinado momento. A tabela pode ser parecida com esta:

fig2: a tabela de ações

Suponhamos que queremos um relatório mostrando o valor de cada ação com seu valor anterior e a porcentagem de variação relacionada ao valor anterior. Podemos fazê-lo usando a função LEAD() para obter o valor anterior da ação. Note que usamos ORDER BY timestamp ao definir a partição (ou seja, a janela de registros). Voltaremos a este ponto mais tarde.

SELECT 	
  share_symbol,
  timestamp,
  value,
  LAG(value) OVER (PARTITION BY share_symbol ORDER BY timestamp ) AS previous_value, 
  TRUNC(((value - (LAG(value) OVER (PARTITION BY share_symbol ORDER BY timestamp )))*100)/value,2) AS percentage_variation
FROM share

Note que as colunas previous_value e percentage_variation são colunas calculadas. Elas utilizam valores de diferentes registros na mesma tabela.

Quando usamos funções posicionais, é muito importante colocar uma cláusula ORDER BY junto com a cláusula PARTITION (como fizemos na consulta anterior). Se não usarmos a ORDER BY correta, os resultados podem estar errados. Por quê? Porque as funções posicionais funcionam com base na ordem dos registros na janela.

Vamos examinar isto um pouco mais. A função FIRST_VALUE() retorna um valor de coluna do primeiro registro na janela. LAG() Como sabemos, retorna o valor da coluna do registro anterior na janela. Ter a ordem correta da janela é crucial; imagine o que você obteria com estas funções de outra forma! Em nosso exemplo, queremos o valor de mercado cronológico anterior para uma ação específica. Assim, utilizamos ORDER BY timestamp. Se omitirmos o ORDER BY ou encomendarmos por outra coluna, o resultado seria errado.

Em alguns casos específicos, as funções posicionais podem retornar valores errados por causa de uma janela parcialmente preenchida. E há mais funções de janela, como RANK(), NTH_VALUE() e LAST_VALUE(). Não temos espaço para cobrir tudo isso aqui, mas sugiro verificar este artigo explicando as funções de janela e estes exemplos de funções de janela para saber mais.

Fechando a Janela

Neste artigo, exploramos as diferenças entre as funções de janela e GROUP BY. Vimos exemplos com várias funções agregadas e de janela. Também falamos sobre uma importante limitação da cláusula GROUP BY, ou seja, o "colapso dos registros". Esta limitação não está presente nas funções de janela, permitindo aos desenvolvedores de SQL combinar dados de nível de registro com os resultados das funções de janela na mesma consulta.

Outra vantagem das funções de janela é sua capacidade de combinar valores de consulta de diferentes registros (da mesma janela) na mesma linha do conjunto de resultados.

Se você estiver interessado em aprender mais sobre funções de janela, sugiro o Funções de Janela (Window Functions) em SQL curso, onde você pode aprender as funções da janela SQL usando exercícios interativos e explicações detalhadas.