Generar resúmenes en MySQL

Uno de los aspectos más útiles de MySQL es que puede analizar multitud de resultados y resumirlos. Se convierte en un potente aliado para generar resúmenes ya que es una actividad muy tediosa si se realiza manualmente.

Una sencilla forma de resumir consiste en determinar qué valores exclusivos se incluyen en un conjunto de valores. Puede usar la palabra clave DISTINCT para eliminar filas duplicadas de un resultado. Por ejemplo, puede encontrar los distintos estados en los que han nacido presidentes de esta forma:

mysql> SELECT DISTINCT state FROM president ORDER BY state;

state
AR
CA
CT
GA
IA
IL
KY
MA
MO
NC
NE
NH
NJ
NY
OH
PA
SC
TX
VA
VT

También puede utilizar la función COUNT(). Si utiliza COUNT(*) le indica el número de filas seleccionadas por la consulta. Si una consulta carece de cláusula WHERE, selecciona toda las filas, de modo que COUNT(*) le indica el número de filas de la tabla. La siguiente consulta muestra la cantidad de filas de suscripción que tiene la tabla member:

mysql> SELECT COUNT(*) FROM member;

COUNT(*)
102

Si una consulta tiene una cláusula WHERE, COUNT(*) le indica la cantidad de filas que coincide. Esta consulta muestra cuántos exámenes ha realizado en su clase hasta el momento:

mysql> COUNT(*) FROM grade_event WHERE category = 'Q';

COUNT(*)
4

COUNT(*) cuenta todas la filas seleccionadas. Por el contrario, COUNT (nombre_col) solamente cuenta valores no NULL. La siguiente consulta ilustra estas diferencias:

mysql> SELECT COUNT(*), COUNT(email), COUNT(expiration) FROM member;

COUNT(*) COUNT(email) COUNT(expiration)
102 80 96

Muestra que aunque la tabla member tiene 102 filas, sólo 80 incluyen un valor en la columna email. También indica que seis miembros tienen una suscripción vitalicia. (Un valor NULL en la columna expiration indica una suscripción vitalicia, y como 96 de las 102 filas no son NULL, el resultado es seis.)
COUNT() combinado con DISTINCT cuenta el número de valores no NULL exclusivos en un resultado. Por ejemplo, para contar el número de estados diferentes en los que han nacido presidentes, utilice lo siguiente:

mysql> SELECT COUNT(DISTINCT state) FROM president;

COUNT(DISTINCT state)
20

Puede contar los valores de una columna o dividir la cantidad de categorías. Por ejemplo, puede saber el número total de alumnos de su clase tras ejecutar esta consulta:

mysql> SELECT COUNT(*) FROM student;

COUNT(*)
31

Para saber cuántos son chicos y cuántas chicas, debe realizar un recuento del género de forma independiente:

mysql> SELECT COUNT(*) FROM student WHERE sex='f';

COUNT(*)
15

mysql> SELECT COUNT(*) FROM student WHERE sex='m';
COUNT(*)
16

Sin embargo, aunque este enfoque funcione, resulta tedioso y no es muy adecuado para columnas con diferentes valores. Imagine cómo calcular de esta forma el número de presidentes nacidos en cada estado. Tendría que buscar los estados representados para no excluir ninguno (SELECT DISTINC state FROM president) y, tras ello, ejecutar SELECT COUNT(*) en cada estado. Evidentemente, no merece la pena hacerlo.
Afortunadamente, se puede usar una sola consulta para contar el número de instancias de cada valor exclusivo en una columna. Para la lista de alumnos, utilice una cláusula GROUP BY:

mysql> SELECT sex, COUNT(*) FROM student GROUP BY sex;

sex COUNT(*)
F 15
M 16

El mismo tipo de consulta nos podrá indicar el número de presidentes que han nacido en cada uno de los estados:

mysql> SELECT state, COUNT(*) FROM presidente GROUP BY state;

sex COUNT(*)
AR 1
CA 1
CT 1
GA 1
IA 1
IL 1
KY 1
MA 4
MO 1
NC 2
NE 1
NH 1
NJ 1
NY 4
OH 7
PA 1
SC 1
TX 2
VA 8
VT 2

Al contar valores en grupos, la cláusula GROUP BY es necesario; indica a MySQL cómo agrupar los valores antes de contarlos. Si la omite, se produce un error.
El uso de COUNT(*) con GROUP BY para contar valores ofrece diversas ventajas con respecto a contar individualmente instancias de cada valor exclusivo:

  • No es necesario que conozcamos con antelación los valores incluidos en la columna que va a resumir.
  • Solamente necesita una cadena de consulta, no varias.
  • Obtiene todos los resultados con una misma consulta y puede ordenar el resultado.

Las dos primeras son muy útiles para expresar consultas más fácilmente. La tercera ofrece mayor flexibilidad para representar resultados. De forma predeterminada, MySQL utiliza las columnas indicadas en la cláusula GROUP BY para ordenar los resultados pero puede especificar otro orden diferente. Por ejemplo, si desea el número de presidentes agrupados por estado de nacimiento pero ordenados por importancia del estado, puede utilizar la siguiente cláusula ORDER BY:

mysql> SELECT state, COUNT(*) AS count FROM president
-> GROUP BY state ORDER BY count DESC;

state count
VA 8
OH 7
MA 4
NY 4
NC 2
VT 2
TX 2
SC 1
NH 1
PA 1
KY 1
NJ 1
IA 1
MO 1
CA 1
NE 1
GA 1
IL 1
AR 1
CT 1

Cuando la columna que desea utilizar para ordenar se obtiene por una función de resumen, no se puede hacer referencia a ésta directamente en la cláusula ORDER BY. En su lugar, debe asignar un alias a la columna para hacer referencia a la misma. Lo podemos ver en la consulta anterior, en la que el alias de la columna COUNT(*) es count. Otra forma de hacer referencia en la cláusula ORDER BY es mediante su posición en el resultado. La consulta anterior se podría haber escrito de esta forma:

SELECT state, COUNT(*) FROM president
GROUP BY state ORDER BY 2 DESC;

MySQL permite hacer referencia a columnas por su posición, aunque esto presente ciertos inconvenientes:

  • El uso de posiciones de columna genera consultas menos comprensibles ya que los número tienen menos sentido que los nombres.
  • Si añade, elimina o reordena columnas del resultado, debe corregir el número de columna de la cláusula ORDER BY siempre que cambie.
  • La sintaxis para hacer referencia a posiciones de columna en cláusulas ORDER BY ya no forma parte de SQL y debe considerarse obsoleta.

Los alias no presentan estos problemas.
Si desea agrupar resultados con GROUP BY en una columna, puede hacer referencia a la misma con un alias o posición de columna, como en ORDER BY. La siguiente columna determina cuántos presidentes han nacido en cada mes del año:

mysql> SELECT MONTH(birth) AS Month, MONTHNAME(birth) AS Name,
-> COUNT(*) AS count
-> FROM president GROUP BY Name ORDER BY Month;

Month Name count
1 January 4
2 Februay 4
3 March 4
4 April 4
5 May 2
6 June 1
7 July 4
8 August 4
9 September 1
10 October 6
11 November 5
12 December 3

COUNT() se puede combinar con ORDER BY y LIMIT. Por ejemplo, para buscar los cuatro estados principales en la tabla president, utilice la siguiente instrucción:

mysql> SELECT state, COUNT(*) AS count FROM president
-> GROUP BY state ORDER BY count DESC LIMIT 4;

state count
VA 8
OH 7
MA 4
NY 4

Si no desea limitar el resultado de la consulta con LIMIT, sino buscando determinados valores de COUNT(), utilice una cláusula HAVING. Es similar a WHERE, ya que especifica condiciones que las filas del resultado deben cumplir. Difiere de WHERE en que puede hacer referencia a los resultado de funciones de resumen como COUNT(). La siguiente consulta le indica qué estados están representados por dos o más presidentes:

mysql> SELECT state, COUNT(*) AS count FROM president
-> GROUP BY state HAVING count > 1 ORDER BY count DESC;

state count
VA 8
OH 7
MA 4
NY 4
NC 2
VT 2
TX 2

Por lo general, este tipo de consulta se ejecuta para buscar valores duplicados en una columna. Para buscar valores no duplicados, utilice HAVING count = 1.
Existen otras funciones de resumen además de COUNT(). Las funciones MIN(), MAX(), SUM() y AVG() son muy útiles para determinar los valores mínimos, máximos, totales y medios de una columna. Incluso puede utilizarlos simultáneamente. La siguiente consulta muestra varias características aritméticas para los exámenes. También cuántas calificaciones se utilizan para calcular cada uno de los valores.

mysql> SLECT
-> event_id,
-> MIN(score) AS minimum,
-> MAX(score) AS maximum,
-> MAX(score) - MIN(score)+1 AS span,
->SUM(score) AS total,
-> AVG(score) AS average,
-> COUNT(score) AS count
-> FROM score
-> GROUP BY event_id;

event_id minimum maximum span total average count
1 9 20 12 439 15.1379 29
2 8 19 12 425 14.1667 30
3 60 97 38 2425 78.2258 31
4 7 20 14 379 14.0370 27
5 8 20 13 383 14.1852 27
6 62 100 39 2325 80.1724 29

Esta información resultaría más clara si los valores event_id representaran un tipo de examen u otro. Sin embargo, para generar dicha información, también hay que consultar la tabla grade_event, como veremos mas adelante.
Si desea generar líneas adicionales con un resumen de resúmenes, añada una cláusula WITH ROLLUP. Indica a MySQL que calcule valores superagregados de las filas agrupadas. El siguiente ejemplo se basa en una instrucción anterior que cuenta el número de alumnos de cada sexo. La cláusula WITH ROLLUP genera otra línea que resume el número de alumnos de casa sexo:

mysql> SELECT sex, COUNT(*) FROM student GROUP BY sex WITH ROLLUP;

sex COUNT(*)
F 15
M 16
NULL 31

NULL en la columna agrupada indica que esa cantidad es el resumen de los grupos anteriores.
WITH ROLLUP también se puede utilizar con otras funciones agregadas. La siguiente instrucción calcula resúmenes de calificaciones como mostramos antes pero también incluye una línea adicional:

msyql> SELECT
-> event_id,
-> MIN(score) AS minimum,
-> MAX(score) AS maximum,
-> MAX(score) - MIN(score)+1 AS span,
-> SUM(score) AS TOTAL,
-> AVG(score) AS average,
-> COUNT(score) AS count
-> FROM score
-> GROUP BY event_id
-> WITH ROLLUP;

event_id minimum maximum span total average count
1 9 20 12 439 15.1379 29
2 8 19 12 425 14.1667 30
3 60 97 38 2425 78.2258 31
4 7 20 14 379 14.0370 27
5 8 20 13 383 14.1852 27
6 62 100 39 2325 80.1724 29
NULL 7 100 94 6376 36.8555 173

En este resultado, la última línea muestra valores sumados calculados en función de los valores de resumen anteriores.
WITH ROLLUP resulta muy útil ya que proporciona información adicional que en circunstancias normales tendría que obtener mediante la ejecución de otra consulta. El uso de una sola consulta es más eficaz ya que no es necesario que el servidor examine los datos dos veces. Si la cláusula GROUP BY hace referencia a más de una columna, WITH ROLLUP genera líneas adicionales con valores de resumen de nivel superior.
Las funciones de resumen son divertidas de utilizar, debido a sus prestaciones pero debe prestar atención. Considere la siguiente consulta:

mysql> SELECT
-> state AS State
-> AVG(TIMESTAMPDIFF(YEAR, birth, death)) AS age
-> FROM president WHERE death IS NOT NULL
-> GROUP BY state ORDER BY Age;

State Age
HY 56.0000
VT 58.5000
NC 59.5000
OH 62.2857
NH 64.0000
NY 69.0000
NJ 71.0000
TX 71.0000
MA 72.0000
VA 72.3750
PA 77.0000
SC 78.0000
CA 81.0000
MO 88.0000
IA 90.0000
NE 93.0000
IL 93.0000

La consulta selecciona presidentes fallecidos, los agrupa por estado de nacimiento, determina la edad aproximada al morir, calcula la edad media (por estado) y ordena los resultados por la media de edad. Es decir, la consulta determina, para los presidentes fallecidos, la edad media de fallecimiento por estado de nacimiento. ¿Para qué sirve? Solamente muestra que la consulta se puede crear, aunque no demuestra que merezca la pena hacerlo. No todo lo que se puede hacer con una base de datos siempre tiene sentido. Sin embargo, los usuario suelen dar rienda suelta a la creación de consultas cuando descubren todo lo que pueden hacer con una base de datos. Esto puede explicar el aumento de extrañas y rebuscadas estadísticas en los programas deportivos en los últimos años. Utilizan sus bases de datos para determinar todo lo que siempre quiso saber sobre un equipo y también lo que no quería saber.

Comparte