Cláusula GROUP BY: ¿Qué tan bien la conoce?

La base de datos puede hacer diferentes tipos de cálculos: puede sumar y restar, multiplicar y dividir, puede hacer cálculos sobre fechas, puede contar filas y sumar valores de filas, etc. También puede realizar estadísticas bastante sofisticadas. La cláusula GROUP BY es la forma más básica de calcular estadísticas en SQL. Puede ser bastante difícil para los principiantes, pero es realmente poderosa. Veamos los detalles de la cláusula GROUP BY, empezando por lo más básico.

Lo básico: Cómo funciona GROUP BY

Aquí tenemos la tabla medallas con los medallistas de la Copa del Mundo de Salto de Esquí de las últimas cuatro temporadas.

 country |         person        | season  | place
---------+-----------------------+---------+-------
Norway   | Anders Bardal         | 2011-12 |   1
Austria  | Gregor Schlierenzauer | 2011-12 |   2
Austria  | Andreas Kofler        | 2011-12 |   3
Austria  | Gregor Schlierenzauer | 2012-13 |   1
Norway   | Anders Bardal         | 2012-13 |   2
Poland   | Kamil Stoch           | 2012-13 |   3
Poland   | Kamil Stoch           | 2013-14 |   1
Slovenia | Peter Prevc           | 2013-14 |   2
Germany  | Severin Freund        | 2013-14 |   3
Germany  | Severin Freund        | 2014-15 |   1
Slovenia | Peter Prevc           | 2014-15 |   2
Austria  | Stefan Kraft          | 2014-15 |   3

Quiero saber cuántas medallas ha conseguido Polonia:

SELECT count(*)
FROM medals
WHERE country = 'Poland';

Si quisiera averiguar el número de medallas de Alemania, tendría que realizar esta consulta:

SELECT count(*)
FROM medals
WHERE country = 'Germany';

Si quisiera saber el número de medallas de cada país, podría hacer seis consultas similares. O podría utilizar un GROUP BY.

SELECT country, count(*)
FROM medals
GROUP BY country;

La cláusula GROUP BY viene justo después de la cláusula WHERE en la consulta SQL. En este caso, falta la cláusula WHERE, por lo que va justo después de FROM.

El resultado:

 country | count
---------+-------
Poland   |   2
Germany  |   2
Austria  |   4
Norway   |   2
Slovenia |   2

Con la consulta GROUP BY la base de datos divide los datos en grupos. Las filas con la misma columna GROUP BY (país en el ejemplo) se ponen en un solo grupo. Por lo tanto, utilizando nuestro ejemplo, los medallistas de Polonia se ponen en un grupo, los medallistas de Alemania se ponen en otro grupo y así sucesivamente. Esta es la agrupación que obtenemos para esta consulta:

 country |        person         | season  | place
–--------+-----------------------+---------+-------
Poland   | Kamil Stoch           | 2012-13 |   3
         | Kamil Stoch           | 2013-14 |   1
–--------+-----------------------+---------+-------
Germany  | Severin Freund        | 2013-14 |   3
         | Severin Freund        | 2014-15 |   1
–--------+-----------------------+---------+-------
Austria  | Gregor Schlierenzauer | 2012-13 |   1
         | Stefan Kraft          | 2014-15 |   3
         | Gregor Schlierenzauer | 2011-12 |   2
         | Andreas Kofler        | 2011-12 |   3
–--------+-----------------------+---------+-------
Norway   | Anders Bardal         | 2012-13 |   2
         | Anders Bardal         | 2011-12 |   1
–--------+-----------------------+---------+-------
Slovenia | Peter Prevc           | 2013-14 |   2
         | Peter Prevc           | 2014-15 |   2
–--------+-----------------------+---------+-------

Con GROUP BY los agregados (count, sum, avg, min, max, y otros) se calculan para cada uno por separado. En el ejemplo la base de datos cuenta el número de filas en cada grupo.

Agrupación con múltiples columnas

Puede agrupar filas por más de una columna.

Por ejemplo, si quiere averiguar cuántas medallas obtuvo cada país en cada temporada, su consulta sería así:

SELECT country, season, count(*)
FROM medals
GROUP BY country, season;

Las filas con el mismo país y la misma temporada se ponen en un grupo. La agrupación tiene el siguiente aspecto:

 country | season  |        person         | place
–--------+---------+-----------------------+-------
Poland   | 2012-13 | Kamil Stoch           |   3
–--------+---------+-----------------------+-------
Poland   | 2013-14 | Kamil Stoch           |   1
–--------+---------+-----------------------+-------
...
–--------+---------+-----------------------+-------
Austria  | 2011-12 | Gregor Schlierenzauer |   2
         | 2011-12 | Andreas Kofler        |   3
–--------+---------+-----------------------+------

El resultado final:

 country | season  | count
---------+---------+-------
 Poland  | 2012-13 |   1
 Austria | 2011-12 |   2
 ...
 Poland  | 2013-14 |   1

Valores nulos en GROUP BY

Un recordatorio: En la condición WHERE no se consideran dos NULL iguales. Por extraño que parezca, la consulta

SELECT *
FROM medals
WHERE place = place;

seleccionará todas las filas excepto las que tengan un lugar NULL. Para SQL el valor NULL significa "Desconocido" y si es desconocido, SQL no puede asumir que sabe con seguridad cuál será su resultado. (En particular, no puede saber con seguridad que el resultado es TRUE).

Con GROUP BY, es diferente. Las filas con valores NULL van todas a un grupo, y los agregados se calculan para este grupo, como para cualquier otro. También funciona para los GROUP BY de varias columnas.

Para esta tabla

    country    |   city  |    person    | earnings
---------------+---------+--------------+----------
 NULL          | Warsaw  | John Doe     |   1000
 United States | NULL    | Maria Jane   |   1000
 Germany       | Berlin  | Hans Schmitt |   2430
 United States | NULL    | Bill Noir    |   1000
 United States | Chicago | Rob Smith    |   3000
 NULL          | Warsaw  | Sophie Doe   |   2000
 Germany       | Berlin  | Jane Dahl    |   1500

la consulta

SELECT country, city, sum(earnings)
FROM employees
GROUP BY country, city;

hace estos grupos:

    country    |   city  |    person    | earnings
---------------+---------+--------------+----------
 NULL          | Warsaw  | John Doe     |   1000
               |         | Sophie Doe   |   2000
–--------------------------------------------------
 United States | NULL    | Maria Jane   |   1000
               |         | Bill Noir    |   1000
–--------------------------------------------------
 United States | Chicago | Rob Smith    |   3000
–--------------------------------------------------
 Germany       | Berlin  | Hans Schmitt |   2430
               |         | Jane Dahl    |   1500

y este resultado

    country    |   city  |  sum
---------------+---------+-------
 NULL          | Warsaw  |  3000
 United States | NULL    |  2000
 United States | Chicago |  3000
 Germany       | Berlin  |  3930

Mito: ¿Las columnas seleccionadas deben aparecer en la cláusula GROUP BY o bajo una función agregada?

La sabiduría común dice que las columnas seleccionadas en una consulta GROUP BY deben aparecer en la cláusula GROUP BY o bajo una función agregada. Por lo tanto, esta consulta es incorrecta:

SELECT user_account.id, email, count(*)
FROM user_account JOIN address
ON user_account.id = address.user_id
GROUP BY email;

La consulta agrupa los resultados por correo electrónico, pero selecciona la columna id, que no está en la cláusula GROUP BY.

Esta sabiduría era la regla en el estándar SQL92. Así es como muchas bases de datos implementan el comportamiento GROUP BY hoy en día. Obtendrás un error similar a este:

ERROR:  column "user_account.id" must appear in the GROUP BY clause or be used 
in an aggregate function

¿De dónde viene esta regla? Veamos un ejemplo de datos.

|       user_account      |   |         address         |
+----+--------------------+   +----+----------+---------+
| id |        Email       |   | id |   city   | user_id |
+----+--------------------+   +----+----------+---------+
| 1  | john@example.com   |   | 1  | London   | 1       |
| 2  | mary@example.co.uk |   | 2  | Brussels | 2       |
| 3  | john@example.com   |   | 3  | Cairo    | 3       |
|    |                    |   | 4  | Dublin   | 1       |

Agrupamos los datos por correo electrónico

 
user_account.email |user_account.id |address.id|address.city|address.user_id|
-------------------+----------------+----------+------------+---------------+
john@example.com   | 1              | 1        | A          | 1             |
                   +----------------+----------+------------+---------------+
                   | 1              | 4        | D          | 1             |
                   +----------------+----------+------------+---------------+
                   | 3              | 3        | C          | 3             |
-------------------+----------------+----------+------------+---------------+
mary@example.com   | 2              | 2        | B          | 2             |

La base de datos crea un grupo para cada correo electrónico. Pero hay varios user_account ids en cada grupo. La base de datos no sabe qué id debe devolver. El estándar SQL quiere que el resultado del SQL sea determinista por lo que prohíbe ejecutar una consulta como esta.

La norma SQL99 ha modificado la redacción de la regla. Ahora dice que cualquier columna que aparezca en SELECT tiene que aparecer en la función agregada o ser funcionalmente dependiente de las columnas de la cláusula GROUP BY. La norma ya no espera que todas las columnas no agregadas se repitan en la cláusula GROUP BY.

¿Qué significa columnas funcionalmente dependientes en la cláusula BY by? Significa: si fijo valores para las columnas en la cláusula GROUP BY, sólo tiene que haber un valor para la otra columna. Por ejemplo, la dirección de correo electrónico determina el valor del nombre de su propietario. Pero hay una trampa: la base de datos tiene que conocer esta dependencia. En el contexto de las bases de datos, la dependencia significa claves primarias y claves únicas. Si agrupo por una clave primaria, entonces sé que otras columnas de esta tabla tienen valores fijos.

Nuestro ejemplo inicial sigue sin ser válido bajo la nueva regla. PERO: si aplico la restricción única en la columna de correo electrónico, la consulta pasa a ser válida según la nueva regla. Si la columna email es única en la tabla user_account, la fijación del valor del email determina todas las demás columnas de la tabla user_account. Por supuesto, si añado la restricción única, mis datos de ejemplo tampoco son válidos. No puedo tener dos filas diferentes con el mismo correo electrónico.

¿Son las bases de datos compatibles con la nueva regla GROUP BY?

Algunas lo hacen, otras no. La nueva regla está en el estándar SQL99. MySQL desde la versión 5.7.4 soporta el nuevo comportamiento. También lo hace Postgres desde la versión 9.1. Postgres trata la columna como funcionalmente dependiente de las columnas agrupadas si un subconjunto de las columnas agrupadas es una clave primaria de la tabla de la que proviene la columna.

Por lo que sé, Oracle y SQL Server siguen manteniendo la versión antigua.

¿Debería utilizar la nueva versión o la versión antigua de la regla en sus consultas? En la práctica, esta modificación no cambia realmente nada. Ambas reglas garantizan que cuando se selecciona una columna no agregada en una consulta GROUP BY, su valor es inequívoco en cada grupo. La regla antigua te obliga a añadir esta columna en la cláusula GROUP BY, pero este GROUP BY no cambia la semántica de la consulta. El resultado es el mismo, sólo tienes que escribir un poco más con la nueva regla.

En general, es mejor seguir con la regla anterior. Tus consultas funcionarán en la mayoría de las bases de datos. Pero es bueno saber que no es necesario.

Extensiones de GROUP BY

SQL-99 añadió ROLLUP, y CUBE y GROUPING SETS como opciones para las sentencias SELECT.

GROUP BY ROLLUP

La sintaxis de ROLLUP es

SELECT <columns>
FROM <tables>
WHERE <condition>
GROUP BY ROLLUP (<group-by columns>);

El uso de ROLLUP (a,b,c) generará las cláusulas GROUP BY: (a, b, c), (a, b), (a) y una fila para una agregación de todas las filas seleccionadas. Equivale a cuatro consultas SELECT con varias cláusulas GROUP BY.

Para esta tabla

department | year | sales
–----------+--------------
 IT        | 2012 |  25000
 IT        | 2013 |  26000
 Retail    | 2012 |  35000
 Retail    | 2013 |  15000
 IT        | 2014 |  18000

y esta consulta

SELECT department, year, sum(sales)
FROM sales
GROUP BY ROLLUP (department, year);

obtenemos el resultado:

 department | year |   sum   
------------+------+--------
 IT         | 2012 |   25000
 IT         | 2014 |   18000
 IT         | 2013 |   26000
 IT         | NULL |   69000 <-  group by department
 Retail     | 2013 |   15000
 Retail     | 2012 |   35000
 Retail     | NULL |   50000 <-  group by department
 NULL       | NULL |  119000 <-  group by (), i.e. all rows selected

Las filas adicionales se denominan a veces superagregados.

ROLLUP es soportado por SQL Server, Oracle, DB2.

En MySQL se puede utilizar la sintaxis WITH ROLLUP:

SELECT <columns>
FROM <tables>
WHERE <condition>
GROUP BY a,b,c WITH ROLLUP;

PostgreSQL no soporta ROLLUP.

GROUP BY CUBE

La sintaxis para CUBE es

SELECT <columns>
FROM <tables>
WHERE <condition>
GROUP BY CUBE (a, b, c);

Funciona de forma similar a ROLLUP pero genera todas las combinaciones posibles de columnas: (a,b,c), (a,b), (a,c), (b,c), (a), (b), (c) y una fila para todas las filas seleccionadas.

La consulta

SELECT department, year, sum(sales)
FROM sales
GROUP BY CUBE (department, year);

mostrará este resultado:

 department | year |   sum   
------------+------+--------
 IT         | 2012 |   25000
 IT         | 2014 |   18000
 IT         | 2013 |   26000
 IT         | NULL |   69000  <-  group by department
 Retail     | 2013 |   15000
 Retail     | 2012 |   35000
 Retail     | NULL |   50000  <-  group by department
 NULL       | NULL |  119000  <-  group by ()
 NULL       | 2014 |   18000 |
 NULL       | 2012 |   60000 | <= three new rows added by CUBE
 NULL       | 2013 |   41000 |    

CUBE está soportado por SQL Server y Oracle, y DB2. MySQL y Postgres no lo soportan.

AGRUPAR POR CONJUNTOS DE AGRUPACIÓN

GROUPING SETS funciona de forma similar, pero permite especificar qué combinaciones de columnas deben utilizarse en el resultado. Los conjuntos de agrupación deben separarse con comas. Si hay más de una columna en un conjunto de agrupación, este conjunto de agrupación debe ponerse entre paréntesis. Los paréntesis vacíos significan el registro general con agregados para todo el conjunto.

Ejemplo de consulta:

SELECT <columns>
FROM <tables>
WHERE <condition>
GROUP BY GROUPING SETS ((a, b), c, ());

Los conjuntos de agrupación son compatibles con SQL Server, Oracle y DB2. MySQL y Postgres no lo soportan.