12.10. Funciones y modificadores para cláusulas GROUP BY

MySQL 5.0

12.10. Funciones y modificadores para cláusulas `GROUP BY`

12.10.1. Funciones (de agregación) de `GROUP BY`

Si usa una función de grupo en un comando sin la cláusula GROUP BY , es equivalente a agrupar todos los registros.

AVG([DISTINCT]expr)

Retorna el valor medio de expr. La opción DISTINCT puede usarse desde MySQL 5.0.3 para retornar la media de los valores distintos de expr.

mysql> SELECT student_name, AVG(test_score) -> FROM student -> GROUP BY student_name;

BIT_AND(expr)

Retorna el AND bit a bit de todos los bits en expr. Los cálculos se realizan con precisión de 64 bits (BIGINT) .

En MySQL 5.0, esta función retoran 18446744073709551615 si no hubieran registros coindicentes. (Este es el valor de un BIGINT sin signo con todos los bits a 1.)

BIT_OR(expr)

Retorna la OR bit a bit de todos los bits en expr. El cálculo se realiza con precisión de 64 bits (BIGINT) .

Esta función retorna 0 si no hay registros coincidentes.

BIT_XOR(expr)

Retorna el XOR bit a bit de todos los bits en expr. Los cálculos se realizan con precisión de 64 bits (BIGINT) .

Esta función retorna 0 si no hay registros coincidentes.

COUNT(expr)

Retorna el contador del número de valores no NULL en los registros recibidos por un comando SELECT.

mysql> SELECT student.student_name,COUNT(*) -> FROM student,course -> WHERE student.student_id=course.student_id -> GROUP BY student_name;

COUNT(*) es algo diferente en que retorna un contador del número de registros retornados, si contienen o no valores NULL .

COUNT(*) está optimizado para retornar muy rápidamente si SELECTretorna de una tabla, no se retornan otras columnas, y no hay cláusula WHERE . Por ejemplo:

mysql> SELECT COUNT(*) FROM student;

Esta optimización se aplica sólo a tablas MyISAM, ya que un conteo exacto de registros se almacena para estos tipos de tablas y puede ser accedido muy rápidamente. Para motores de almacenamiento transaccionales (InnoDB, BDB), almacenar un contador de registros es más problemático ya que pueden ocurrir múltiples transacciones, cada una de las cuales puede afectar al contador.

COUNT(DISTINCTexpr,[expr...])

Retorna un contador del número de valores no NULL distintos.

mysql> SELECT COUNT(DISTINCT results) FROM student;

En MySQL, puede obtener el número de combinaciones de distintas expresiones que no contiene NULL dada una lista de expresiones. En SQL estándar, tendría que hacer una concatenación de todas las expresiones dentro de COUNT(DISTINCT ...).

GROUP_CONCAT(expr)

Esta función retorna una cadena resultado con los valores no NULL concatenados de un grupo. Retorna NULL si no hay valores no NULL . La sintaxis completa es la siguiente:

GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] [SEPARATOR str_val])

mysql> SELECT student_name, -> GROUP_CONCAT(test_score) -> FROM student -> GROUP BY student_name;

Or:

mysql> SELECT student_name, -> GROUP_CONCAT(DISTINCT test_score -> ORDER BY test_score DESC SEPARATOR ' ') -> FROM student -> GROUP BY student_name;

En MySQL, puede obtener los valores concatenados de combinaciones de expresiones. Puede eliminar valores duplicados usando DISTINCT. Si quiere ordenar valores en el resultado, debe usar la cláusula ORDER BY. Para ordenar en orden inverso, añada la palabra clave DESC (descendente) al nombre de la columna que está ordenando con la cláusula ORDER BY . El valor por defecto es orden ascendente; puede especificarse explícitamente usando la palabra clave ASC. SEPARATOR tiene a continuación la cadena que debe insertarse entre los valores del resultado. Por defecto es una coma (','). Puede eliminar el separador especificando SEPARATOR ''.

Puede especificar la longitud máxima con la variable de sistema group_concat_max_len . La sintaxis para ello en tiempo de ejecución es la siguiente, donde val es un entero sin signo:

SET [SESSION | GLOBAL] group_concat_max_len = val;

Si se especifica una longitud máxima, el resultado se trunca a su longitudo máxima.

MIN([DISTINCT]expr), MAX([DISTINCT]expr)

Retornas los valores máximos y mínimos de expr. MIN() y MAX() pueden tener un argumento; en tales casos retornan el valor de cadena mínimo y máximo. Consulte Sección 7.4.5, “Cómo utiliza MySQL los índices”. La palabra clave DISTINCT puede usarse en MySQL 5.0 para encontrar el mínimo o máximo de los distintos valores de expr; esto es soportado, pero produce el mismo resultado que omitiendo DISTINCT.

mysql> SELECT student_name, MIN(test_score), MAX(test_score) -> FROM student -> GROUP BY student_name;

Para MIN(), MAX(), y otras funciones agregadas, MySQL compara columnas ENUM y SET por su valor de cadena de carácteres en lugar que por la posición relativa de la cadena en el conjunto. Esto difiere de cómo los compara ORDER BY. Esto se rectificará en una futura versión de MySQL .

STD(expr), STDDEV(expr)

Retorna la desviación estándard de expr. Esta es una extensión del estándar SQL. La forma STDDEV() de esta función se proporciona para compatibilidad con Oracle. Desde MySQL 5.0.3, la función estándar SQL STDDEV_POP() puede usarse en su lugar.

STDDEV_POP(expr)

Retorna la desviación estándar de expr (la raíz cuadrada de VAR_POP()). Esta función se añadió en MySQL 5.0.3. Antes de 5.0.3, puede usar STD() o STDDEV(), que son equivalentes pero no SQL estándar.

STDDEV_SAMP(expr)

Retorna la muestra de la desviación estándar de expr (la raíz cuadrada de VAR_SAMP(). Esta función se añadió en MySQL 5.0.3.

SUM([DISTINCT]expr)

Retorna la suma de expr. Si el conjunto resultado no tiene registros, SUM() retorna NULL. La palabra clave DISTINCT puede usarse en MySQL 5.0 para sumar sólo los valores distintos de expr.

VAR_POP(expr)

Retorna la varianza estándar de expr. Considera los registros como la población completa, no como una muestra, así que tiene el número de registros como denominador. Esta función se añadió en MySQL 5.0.3. Antes de 5.0.3, puede usar VARIANCE(), que es equivalente pero no SQL estándar.

VAR_SAMP(expr)

Retorna la varianza de muestra de expr. Esto es, el denominador es el número de registros menos uno. Esta función se añadió en MySQL 5.0.3.

VARIANCE(expr)

Retorna la varianza estándar de expr. Esto es una extensión de SQL estándar. Desde MySQL 5.0.3, la función SQL estándar VAR_POP() puede usarse en su lugar.

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.

12.10.3. GROUP BY con campos escondidos

MySQL extiende el uso de GROUP BY para que pueda usar columnas o cálculos en la lista SELECT que no aparecen en la cláusula GROUP BY. Esto se aplica a cualquier valor posible para este grupo . Puede usarlo para obtener mejor rendimiento al evitar ordenar y agrupar elementos innecesarios. Por ejemplo, no necesita agrupar customer.name en la siguiente consulta:

mysql> SELECT order.custid, customer.name, MAX(payments) -> FROM order,customer -> WHERE order.custid = customer.custid -> GROUP BY order.custid;

En SQL estándar, puede tener que añadir customer.name a la cláusula GROUP BY . En MySQL, el nombre es redundante si no se ejecuta en modo ANSI.

No use esta característica si las columnas que omite de la parte GROUP BY no son únicos en el grupo! Obtendría resultados impredecibles.

En algunos casos, puede usar MIN() y MAX() para obtener valores específicos de columna incluso si no son únicos. La siguiente da el valor de column del registro conteniendo el valor más pequeño en la columna sort :

SUBSTR(MIN(CONCAT(RPAD(sort,6,' '),column)),7)

Consulte Sección 3.6.4, “Los registros de un grupo que tienen el máximo valor en alguna columna”.

Tenga en cuenta que si trata de seguir SQL estándar, no puede usar expresiones en clásulas GROUP BY o ORDER BY . Puede solucionar esta limitación usando un alias para la expresión:

mysql> SELECT id,FLOOR(value/100) AS val -> FROM tbl_name -> GROUP BY id, val ORDER BY val;

Sin embargo, MySQL le permite usar expresiones en cláusulas GROUP BY y ORDER BY . Por ejemplo:

mysql> SELECT id, FLOOR(value/100) FROM tbl_name ORDER BY RAND();

previous page start next page

12.10. Funciones y modificadores para cláusulas GROUP BY

MySQL 5.0