Por Que Não Podemos Usar Funções de Janela com GROUP BY?

É verdade - por causa da ordem de operações do SQL, as funções de janela não são permitidas na cláusula GROUP BY. No entanto, você pode usá-las mesmo assim com este truque especial.

As funções de janela são um dos atributos mais poderosos da linguagem SQL. Entretanto, não é fácil dominar a sintaxe dessas funções, já que ela possui muitos detalhes que podem confundir desenvolvedores iniciantes. Uma das armadilhas mais comuns é tentar usar as funções de janela dentro de GROUP BY.

Erro: Funções de Janela (Window Functions) em SQL não são permitidas em GROUP BY

Imagine que você tem uma tabela, parcial, que armazena os resultados de uma avaliação parcial feita por estudantes. As colunas da tabela incluem o nome do estudante e o número de pontos que o estudante obteve na avaliação. Você gostaria de dividir os alunos em quatro grupos de tamanhos iguais com base no resultado do teste: os 25% com maior pontuação estão em um grupo, os 25% seguintes no segundo grupo, os outros 25% no terceiro grupo, e os 25% com menor pontuação no último grupo. Em seguida, você gostaria de ver a variedade de pontos e o número de alunos em cada grupo.

Para isso, você poderia escrever a seguinte consulta:

SELECT
 ntile(4) OVER (ORDER BY pontos),
 min(pontos),
 max(pontos),
 count(*)
FROM parcial
GROUP BY ntile(4) OVER (ORDER BY pontos);

A função NTILE() divide os alunos em grupos e atribui a cada aluno o número (1-4) de seu grupo. Queremos então agrupar os estudantes com base no número do seu respectivo grupo e calcular o mínimo, o máximo e a contagem para cada grupo. Entretanto, ao executar esta consulta, você verá um erro parecido com este:

ERRO:  funções de janela não são permitidas em GROUP BY
LINHA 7: GROUP BY ntile(4) OVER (ORDER BY pontos);

Por Que Não Podemos Usar Funções de Janela (Window Functions) em GROUP BY?

A razão pela qual as funções de janela não são permitidas em GROUP BY é a ordem das operações em SQL. As cláusulas de uma consulta SQL são processadas em uma ordem diferente daquela escrita na consulta. A ordem completa de operações em SQL é:

• FROM, JOIN
• WHERE
• GROUP BY
• Funções agregadas
• HAVING
• Funções de janela
• SELECT
• DISTINCT
• UNION/INTERSECT/EXCEPT
• ORDER BY
• OFFSET
• LIMIT/FETCH/TOP

Uma consulta SQL primeiro determina as tabelas a serem consultadas, depois aplica os filtros WHERE, e depois realiza a operação GROUP BY. Em seguida, passa-se para o cálculo das funções agregadas, os filtros HAVING e, finalmente, para as funções de janela. Assim, quando o GROUP BY é avaliado, as funções de janela ainda não foram computadas!

Na prática, só é possível consultar diretamente as funções de janela em SQL nas cláusulas SELECT e ORDER BY.

A ordem das operações no SQL é uma das coisas mais importantes a serem consideradas ao escrever uma consulta utilizando funções de janela. Se você não usa funções de janela com frequência, é fácil esquecer desse detalhe, e é por isso que colocamos um lembrete da ordem de operações em nosso resumo sobre Funções de Janela (Window Functions) em SQL . Salve-o nos seus favoritos se você costuma usar Funções de Janela!

Sendo assim, o SQL não permite que você insira funções de janela em uma cláusula GROUP BY. Mas existe uma forma de contornar isto...

Como Usar Funções de Janela (Window Functions) em SQL na Cláusula GROUP BY

É possível corrigir a consulta para que ela retorne o resultado desejado? Sim! A solução é usar uma subconsulta para calcular a função de janela que você deseja usar na consulta principal. Aqui está nosso exemplo modificado:

SELECT 
  quartil,
  min(pontos),
  max(pontos),
  count(*)
FROM 
  (SELECT
     ntile(4) OVER (ORDER BY pontos) AS quartil,
     pontos
     FROM parcial) groups
GROUP BY quartil;

Na subconsulta, usamos a função NTILE() para alocar estudantes em grupos. Na consulta principal, calculamos as estatísticas: o mínimo, o máximo e o número de estudantes.

Outra possibilidade é usar uma expressão de tabela comum (CTE), da seguinte forma:

WITH groups AS (
  SELECT
     ntile(4) OVER (ORDER BY pontos) AS quartil,
     pontos
  FROM parcial
) 
SELECT 
  quartil,
  min(pontos),
  max(pontos),
  count(*)
FROM groups
GROUP BY quartil;

Esta consulta é semelhante à versão anterior utilizando a subconsulta. Entretanto, com a CTE podemos definir a consulta auxiliar antes da consulta principal, tornando nosso código mais legível.

Domine Funções de Janela (Window Functions) em SQL com a LearnSQL.com.br

Se você quiser aprender mais sobre funções de janela, recomendamos nosso curso Funções de Janela (Window Functions) em SQL. Com ele, você aprenderá a usar funções de janela e evitar os erros mais comuns cometidos por iniciantes. Se você não tem certeza se as funções de janela são para você, leia por que você deve aprendê-las ou este artigo sobre nosso curso Funções de Janela (Window Functions) em SQL .

Se você já conhece as funções da janela, baixe o nosso Resumo sobre Funções de Janela (Window Functions) em SQL.