Skip navigation links
12.10.3 GROUP BY con campos escondidos »
Section Navigation      [Toggle]

12.10.2. Modificadores de GROUP BY

La cláusula GROUP BY permite añadir un modificador WITH ROLLUP que provoca añadir registros extra al resumen de la salida. Estos registros representan operaciones de resumen de alto nivel ( o super agregadas ) . ROLLUP por lo tanto le permite responder preguntas en múltiples niveles de análisis con una sola consulta. Puede usarse, por ejemplo, para proporcionar soporte para operaciones OLAP (Online Analytical Processing).

Suponga que una tabla llamada sales tiene las columnas year, country, product, y profit para guardar las ventas productivas: 

CREATE TABLE sales
(
    year    INT NOT NULL,
    country VARCHAR(20) NOT NULL,
    product VARCHAR(32) NOT NULL,
    profit  INT
);

Los contenidos de la tabla pueden resumirse por año con un simple GROUP BY como este: 

mysql> SELECT year, SUM(profit) FROM sales GROUP BY year;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 |        4525 |
| 2001 |        3010 |
+------+-------------+

Esta salida muestra el beneficio total para cada año, pero si quiere determinar el beneficio total registrado durante todos los años, debe añadir los valores individuales usted mismo o ejecutar una consulta adicional.

O puede usar ROLLUP, que proporciona ambos niveles de análisis con una única consulta. Añadir un modificador WITH ROLLUP a la cláusula GROUP BY provoca que la consulta produzca otro registro que muestra el beneficio total sobre todos los valores de año: 

mysql> SELECT year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 |        4525 |
| 2001 |        3010 |
| NULL |        7535 |
+------+-------------+

La línea super agregada con la suma total se identifica con el valor NULL en la columna year .

ROLLUP tiene un efecto más complejo cuando hay múltiples columnas GROUP BY . En este caso, cada vez que hay un “break” (cambio en el valor) en cualquiera excepto la última columna de agrupación, la consulta produce registros super agregados extra.

Por ejemplo, sin ROLLUP, un resumen de la tabla sales basado en year, country, y product puede tener este aspecto: 

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product;
+------+---------+------------+-------------+
| year | country | product    | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer   |        1500 |
| 2000 | Finland | Phone      |         100 |
| 2000 | India   | Calculator |         150 |
| 2000 | India   | Computer   |        1200 |
| 2000 | USA     | Calculator |          75 |
| 2000 | USA     | Computer   |        1500 |
| 2001 | Finland | Phone      |          10 |
| 2001 | USA     | Calculator |          50 |
| 2001 | USA     | Computer   |        2700 |
| 2001 | USA     | TV         |         250 |
+------+---------+------------+-------------+

La salida indica valores resumen sólo en el nivel de análisis year/country/product . Cuando se añade ROLLUP , la consulta produce registros extra: 

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+-------------+
| year | country | product    | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer   |        1500 |
| 2000 | Finland | Phone      |         100 |
| 2000 | Finland | NULL       |        1600 |
| 2000 | India   | Calculator |         150 |
| 2000 | India   | Computer   |        1200 |
| 2000 | India   | NULL       |        1350 |
| 2000 | USA     | Calculator |          75 |
| 2000 | USA     | Computer   |        1500 |
| 2000 | USA     | NULL       |        1575 |
| 2000 | NULL    | NULL       |        4525 |
| 2001 | Finland | Phone      |          10 |
| 2001 | Finland | NULL       |          10 |
| 2001 | USA     | Calculator |          50 |
| 2001 | USA     | Computer   |        2700 |
| 2001 | USA     | TV         |         250 |
| 2001 | USA     | NULL       |        3000 |
| 2001 | NULL    | NULL       |        3010 |
| NULL | NULL    | NULL       |        7535 |
+------+---------+------------+-------------+

Para esta consulta, añadir ROLLUP provoca que la salida incluya información resumen en cuatro niveles de análisis, no sólo uno. Aquí se muestra cómo interpretar la salida de ROLLUP :

  • A continuación de cada conjunto de registros producto de un año dado y un país, un registro resume extra se produce mostrando el total para todos los productos. Estos registros tienen la columna product a NULL.

  • A continuación de cada conjunto de registros para un año dado, se produce un registro resumen extra mostrando el total para todos los países y productos. Estos registros tienen las columnas country y products a NULL.

  • Finalmente, a continuación de todos los otros registros, un registro extra resumen se produce mostrando el total para todos los años, paises y productos. Este registro tiene las columnas year, country, y products a NULL.

Otras consideraciones usando ROLLUP

Los siguientes puntos listan algunos comportamientos específicos a la implementación de MySQL de ROLLUP:

Cuando usa ROLLUP, no puede usar una cláusula ORDER BY para ordenar los resultados. En otras palabras, ROLLUP y ORDER BY son mútuamente exclusivas. Sin embargo, puede tener algún control sobre la ordenación. GROUP BY en MySQL ordena los resultados, y puede usar explícitamente ASC y DESC con columnas mostradas en la lista GROUP BY para especificar orden de ordenación para columnas individuales. (Los registros resumen de alto nivel apadidos por ROLLUP todavía aparecen tras los registros para los que son calculados, a pesar del orden de ordenación.)

LIMIT puede usarse para restringir el número de registros retornados al cliente. LIMIT se aplica tras ROLLUP, así que el límite se aplica contra los registros extra añadidos por ROLLUP. Por ejemplo: 

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product WITH ROLLUP
    -> LIMIT 5;
+------+---------+------------+-------------+
| year | country | product    | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer   |        1500 |
| 2000 | Finland | Phone      |         100 |
| 2000 | Finland | NULL       |        1600 |
| 2000 | India   | Calculator |         150 |
| 2000 | India   | Computer   |        1200 |
+------+---------+------------+-------------+

Usar LIMIT con ROLLUP puede producir resultados que son más difíciles de interpretar, ya que tiene menos contexto para entender los registros super agregados.

Los indicadores NULL en cada registro super agregado se preducen cuando los registros se envían al cliente. El servidor busca las columnas llamadas en la cláusula GROUP BY siguiendo la que esté más a la izquierda que ha cambiado un valor. Para cualquier columna en el conjunto de resultados con un nombre que sea una coincidencia léxica para cualquiera de estos nombres, su valor se cambia a NULL. (Si especifica columnas para agrupar con número de columna, el servidor identifica tales columnas para cambiar a NULL por el número.)

Debido a que los valores NULL en los registros super agregados se guardan en el conjunto de resultados en una de las últimas etapas del proceso de la consulta, no puede testearlas como valores NULL dentro de la propia consulta. Por ejemplo, no puede añadir HAVING product IS NULL a la consulta para eliminar de la salida todos los valores menos los registros super agregados.

Por otro lado, los valores NULL aparecen como NULL en la parte del cliente y pueden testearse como tales usando cualquier interfaz de programación de cliente MySQL.

Ésta es una traducción del manual de referencia de MySQL, que puede encontrarse en dev.mysql.com. El manual de referencia original de MySQL está escrito en inglés, y esta traducción no necesariamente está tan actualizada como la versión original. Para cualquier sugerencia sobre la traducción y para señalar errores de cualquier tipo, no dude en dirigirse a mysql-es@vespito.com.