¿Por qué no se permiten las funciones de ventana de SQL en GROUP BY?

Es cierto: el orden de las operaciones en SQL determinan que las funciones de ventana no estén permitidas en GROUP BY. Pero puedes utilizarlas de todos modos con este truco especial.

Las funciones de ventana de SQL son una de las características más potentes del lenguaje. Sin embargo, la sintaxis de las funciones de ventana no es fácil de dominar, y tiene muchos detalles que pueden hacer tropezar a los principiantes. Una de las trampas más comunes es intentar utilizar las funciones de ventana en GROUP BY.

Error: las funciones de ventana no están permitidas en GROUP BY

Imagina que tienes una tabla examen que almacena los resultados de un examen parcial realizado por estudiantes. Las columnas de la tabla son el nombre del estudiante y el número de puntos que el estudiante obtuvo en el examen. Quieres dividir a los estudiantes en cuatro grupos de igual tamaño en función de su resultado en el examen: el 25% superior estará en un grupo, el siguiente 25% en el segundo grupo, el tercer 25% en el tercer grupo y el 25% inferior en el último grupo. A continuación, quieres ver el rango de puntos y el número de estudiantes en cada grupo.

Aquí hay una consulta que podrías escribir:

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

La función NTILE() divide a los estudiantes en grupos y asigna a cada estudiante el número (1-4) de su grupo. A continuación, queremos agrupar a los estudiantes en función del número de su grupo y calcular el mínimo, el máximo y el recuento de cada grupo. Sin embargo, al ejecutar esta consulta, obtendremos un error:

ERROR:  window functions are not allowed in GROUP BY
LINE 7: GROUP BY ntile(4) OVER (ORDER BY puntos);

¿Por qué no se permiten funciones de ventana en GROUP BY?

La razón por la que no se permiten las funciones de ventana en GROUP BY es el orden de las operaciones en SQL. Las cláusulas de una consulta SQL se procesan en un orden diferente al que están escritas en la consulta. El orden completo de operación en SQL es:

• FROM, JOIN
• WHERE
• GROUP BY
• Funciones agregadas
• HAVING
• Funciones de ventana
• SELECT
• DISTINCT
• UNION/INTERSECT/EXCEPT
• ORDER BY
• OFFSET
• LIMIT/FETCH/TOP

Una consulta SQL calcula primero las tablas desde las que se va a consultar, luego aplica los filtros de WHERE y después realiza la operación de GROUP BY. A continuación, procede a calcular las funciones de agregación, los filtros de HAVING y, por último, las funciones de ventana. Por lo tanto, en el momento en que se evalúa GROUP BY, ¡las funciones de ventana aún no se han calculado!

En la práctica, sólo se puede hacer referencia directamente a las funciones de ventana de SQL en las cláusulas SELECT y ORDER BY.

El orden de las operaciones en SQL es una de las cosas más importantes que hay que recordar cuando se escribe una consulta con funciones de ventana. Si no usas las funciones de ventana a menudo, es fácil olvidarlo, por lo que hemos puesto un recordatorio del orden de las operaciones en nuestra hoja de trucos de SQL sobre funciones de ventana. Asegúrate de marcarla si utilizas las funciones de ventana de vez en cuando.

Entonces, SQL no permite poner funciones de ventana en un GROUP BY. Pero... resulta que hay una manera de evitar esto.

¿Cómo usar funciones de ventana en GROUP BY?

¿Podemos arreglar la consulta para que devuelva el resultado que queremos? Sí. La solución es utilizar una subconsulta para calcular la función de ventana que queremos utilizar en la consulta principal. Este es nuestro ejemplo modificado:

SELECT 
  quartile,
  min(puntos),
  max(puntos),
  count(*)
FROM 
  (SELECT
     ntile(4) OVER (ORDER BY puntos) AS quartile,
     puntos
     FROM examen) grupos
GROUP BY quartile;

En la subconsulta, utilizamos la función NTILE() para asignar a los estudiantes en grupos. En la consulta principal, calculamos las estadísticas: el mínimo, el máximo y el número de estudiantes.

Otra posibilidad es utilizar una expresión de tabla común (CTE), como la siguiente:

WITH grupos AS (
  SELECT
     ntile(4) OVER (ORDER BY puntos) AS quartile,
     puntos
  FROM examen
) 
SELECT 
  quartile,
  min(puntos),
  max(puntos),
  count(*)
FROM grupos
GROUP BY quartile;

La consulta es similar a la versión anterior con la subconsulta. Sin embargo, una CTE nos permite definir la consulta auxiliar antes de la consulta principal; esto hace que el código sea más legible.

Domina las funciones de ventana con LearnSQL.com

Si quieres aprender las funciones de ventana, te recomendamos nuestro curso interactivo de funciones de ventana. Aprenderás a utilizar las funciones de ventana y a evitar los errores comunes de los novatos. Si no estás seguro de si las funciones de ventana son para ti, lee por qué deberías aprenderlas o este artículo sobre nuestro curso de funciones de ventana.

Si ya conocess las funciones de ventana, asegúrate de descargar nuestra hoja de trucos de SQL sobre funciones de ventana.