Todo lo que necesita saber sobre la instrucción GROUP BY de SQL

febrero 24, 2021 Aranzulla de Los Pobres

Gran parte del poder de las bases de datos relacionales proviene de filtrar datos y unir tablas. Es por eso que representamos esas relaciones en primer lugar. Pero los sistemas de bases de datos modernos proporcionan otra técnica valiosa: la agrupación.

La agrupación le permite extraer información resumida de una base de datos. Le permite combinar resultados para crear datos estadísticos útiles. La agrupación le evita escribir código para casos comunes, como promediar listas de cifras. Y puede hacer que los sistemas sean más eficientes.

¿Qué hace la cláusula GROUP BY?

GROUP BY, como sugiere el nombre, agrupa los resultados en un conjunto más pequeño. Los resultados consisten en una fila para cada valor distinto de la columna agrupada. Podemos mostrar su uso observando algunos datos de muestra con filas que comparten algunos valores comunes.

La siguiente es una base de datos muy simple con dos tablas que representan álbumes de registros. Puede configurar dicha base de datos escribiendo un esquema básico para su sistema de base de datos elegido. La tabla de álbumes tiene nueve filas con una columna de identificación de clave principal y columnas para nombre, artista, año de lanzamiento y ventas:

 +----+---------------------------+-----------+--------------+-------+
 | id | name | artist_id | release_year | sales |
 +----+---------------------------+-----------+--------------+-------+
 | 1 | Abbey Road | 1 | 1969 | 14 |
 | 2 | The Dark Side of the Moon | 2 | 1973 | 24 |
 | 3 | Rumours | 3 | 1977 | 28 |
 | 4 | Nevermind | 4 | 1991 | 17 |
 | 5 | Animals | 2 | 1977 | 6 |
 | 6 | Goodbye Yellow Brick Road | 5 | 1973 | 8 |
 | 7 | 21 | 6 | 2011 | 25 |
 | 8 | 25 | 6 | 2015 | 22 |
 | 9 | Bat Out of Hell | 7 | 1977 | 28 |
 +----+---------------------------+-----------+--------------+-------+

La mesa de artistas es aún más sencilla. Tiene siete filas con columnas de identificación y nombre:

 +----+---------------+
 | id | name |
 +----+---------------+
 | 1 | The Beatles |
 | 2 | Pink Floyd |
 | 3 | Fleetwood Mac |
 | 4 | Nirvana |
 | 5 | Elton John |
 | 6 | Adele |
 | 7 | Meat Loaf |
 +----+---------------+

Puede comprender varios aspectos de GROUP BY con solo un conjunto de datos simple como este. Por supuesto, un conjunto de datos de la vida real tendría muchas, muchas más filas, pero los principios siguen siendo los mismos.

Agrupación por una sola columna

Digamos que queremos saber cuántos álbumes tenemos de cada artista. Comience con una consulta SELECT típica para obtener la columna artist_id:

 SELECT artist_id FROM albums

Esto devuelve las nueve filas, como se esperaba:

 +-----------+
 | artist_id |
 +-----------+
 | 1 |
 | 2 |
 | 3 |
 | 4 |
 | 2 |
 | 5 |
 | 6 |
 | 6 |
 | 7 |
 +-----------+

Para agrupar estos resultados por artista, agregue la frase GROUP BY artist_id :

 SELECT artist_id FROM albums GROUP BY artist_id

Lo que da los siguientes resultados:

 +-----------+
 | artist_id |
 +-----------+
 | 1 |
 | 2 |
 | 3 |
 | 4 |
 | 5 |
 | 6 |
 | 7 |
 +-----------+

Hay siete filas en el conjunto de resultados, reducidas del total de nueve en la tabla de álbumes . Cada artist_id único tiene una sola fila. Finalmente, para obtener los recuentos reales, agregue COUNT (*) a las columnas seleccionadas:

 SELECT artist_id, COUNT(*)
 FROM albums
 GROUP BY artist_id
 +-----------+----------+
 | artist_id | COUNT(*) |
 +-----------+----------+
 | 1 | 1 |
 | 2 | 2 |
 | 3 | 1 |
 | 4 | 1 |
 | 5 | 1 |
 | 6 | 2 |
 | 7 | 1 |
 +-----------+----------+

Los resultados agrupan dos pares de filas para los artistas con los identificadores 2 y 6 . Cada uno tiene dos álbumes en nuestra base de datos.

Relacionado: La hoja de referencia de comandos SQL esenciales para principiantes

Cómo acceder a datos agrupados con una función agregada

Es posible que haya utilizado la función COUNT antes, particularmente en el formulario COUNT (*) como se ve arriba. Obtiene el número de resultados en un conjunto. Puede usarlo para obtener el número total de registros en una tabla:

 SELECT COUNT(*) FROM albums
 +----------+
 | COUNT(*) |
 +----------+
 | 9 |
 +----------+

COUNT es una función agregada. Este término se refiere a funciones que traducen valores de varias filas en un solo valor. A menudo se utilizan junto con la instrucción GROUP BY.

En lugar de simplemente contar el número de filas, podemos aplicar una función agregada a valores agrupados:

 SELECT artist_id, SUM(sales)
 FROM albums
 GROUP BY artist_id
 +-----------+------------+
 | artist_id | SUM(sales) |
 +-----------+------------+
 | 1 | 14 |
 | 2 | 30 |
 | 3 | 28 |
 | 4 | 17 |
 | 5 | 8 |
 | 6 | 47 |
 | 7 | 28 |
 +-----------+------------+

Las ventas totales que se muestran arriba para los artistas 2 y 6 son las ventas de sus múltiples álbumes combinadas:

 SELECT artist_id, sales
 FROM albums
 WHERE artist_id IN (2, 6)
 +-----------+-------+
 | artist_id | sales |
 +-----------+-------+
 | 2 | 24 |
 | 2 | 6 |
 | 6 | 25 |
 | 6 | 22 |
 +-----------+-------+

Agrupación por varias columnas

Puede agrupar por más de una columna. Solo incluya varias columnas o expresiones, separadas por comas. Los resultados se agruparán según la combinación de estas columnas.

 SELECT release_year, sales, count(*)
 FROM albums
 GROUP BY release_year, sales

Esto normalmente producirá más resultados que agrupar por una sola columna:

 +--------------+-------+----------+
 | release_year | sales | count(*) |
 +--------------+-------+----------+
 | 1969 | 14 | 1 |
 | 1973 | 24 | 1 |
 | 1977 | 28 | 2 |
 | 1991 | 17 | 1 |
 | 1977 | 6 | 1 |
 | 1973 | 8 | 1 |
 | 2011 | 25 | 1 |
 | 2015 | 22 | 1 |
 +--------------+-------+----------+

Tenga en cuenta que, en nuestro pequeño ejemplo, solo dos álbumes tienen el mismo año de lanzamiento y recuento de ventas (28 en 1977).

Funciones agregadas útiles

Aparte de COUNT, varias funciones funcionan bien con GROUP. Cada función devuelve un valor basado en los registros que pertenecen a cada grupo de resultados.

COUNT () devuelve el número total de registros coincidentes.
SUM () devuelve el total de todos los valores en la columna dada sumados.
MIN () devuelve el valor más pequeño en una columna determinada.
MAX () devuelve el valor más grande en una columna determinada.
AVG () devuelve el promedio medio. Es el equivalente de SUM () / COUNT ().

También puede utilizar estas funciones sin una cláusula GROUP:

 SELECT AVG(sales) FROM albums
 +------------+
 | AVG(sales) |
 +------------+
 | 19.1111 |
 +------------+

Usando GROUP BY con una cláusula WHERE

Al igual que con un SELECT normal, aún puede usar WHERE para filtrar el conjunto de resultados:

 SELECT artist_id, COUNT(*)
 FROM albums
 WHERE release_year > 1990
 GROUP BY artist_id

 +-----------+----------+
 | artist_id | COUNT(*) |
 +-----------+----------+
 | 4 | 1 |
 | 6 | 2 |
 +-----------+----------+

Ahora solo tiene los álbumes lanzados después de 1990, agrupados por artista. También puede usar una combinación con la cláusula WHERE, independientemente de GROUP BY:

 SELECT r.name, COUNT(*) AS albums
 FROM albums l, artists r
 WHERE artist_id=r.id
 AND release_year > 1990
 GROUP BY artist_id

 +---------+--------+
 | name | albums |
 +---------+--------+
 | Nirvana | 1 |
 | Adele | 2 |
 +---------+--------+

Sin embargo, tenga en cuenta que si intenta filtrar en función de una columna agregada:

 SELECT r.name, COUNT(*) AS albums
 FROM albums l, artists r
 WHERE artist_id=r.id
 AND albums > 2
 GROUP BY artist_id;

Recibirás un error:

 ERROR 1054 (42S22): Unknown column 'albums' in 'where clause'

Las columnas basadas en datos agregados no están disponibles para la cláusula WHERE.

Uso de la cláusula HAVING

Entonces, ¿cómo se filtra el conjunto de resultados después de que se ha realizado una agrupación? La cláusula HAVING se ocupa de esta necesidad:

 SELECT r.name, COUNT(*) AS albums
 FROM albums l, artists r
 WHERE artist_id=r.id
 GROUP BY artist_id
 HAVING albums > 1;

Tenga en cuenta que la cláusula HAVING viene después de GROUP BY. De lo contrario, es esencialmente un simple reemplazo del DÓNDE con TENER. Los resultados son:

 +------------+--------+
 | name | albums |
 +------------+--------+
 | Pink Floyd | 2 |
 | Adele | 2 |
 +------------+--------+

Aún puede usar una condición WHERE para filtrar los resultados antes de la agrupación. Funcionará junto con una cláusula HAVING para filtrar después de la agrupación:

 SELECT r.name, COUNT(*) AS albums
 FROM albums l, artists r
 WHERE artist_id=r.id
 AND release_year > 1990
 GROUP BY artist_id
 HAVING albums > 1;

Solo un artista en nuestra base de datos lanzó más de un álbum después de 1990:

 +-------+--------+
 | name | albums |
 +-------+--------+
 | Adele | 2 |
 +-------+--------+

Combinar resultados con GROUP BY

La declaración GROUP BY es una parte increíblemente útil del lenguaje SQL. Puede proporcionar información resumida de datos, para una página de contenido, por ejemplo. Es una excelente alternativa para obtener grandes cantidades de datos. La base de datos maneja bien esta carga de trabajo adicional, ya que su mismo diseño la hace óptima para el trabajo.

Una vez que comprenda la agrupación y cómo unir varias tablas, podrá utilizar la mayor parte del poder de una base de datos relacional.