Cuando se hace una consulta de agrupación, SQL obliga a que todo aquello que se incluya en el select sea alguna de estas tres cosas:
- campos del criterio de agrupación
- expresiones que usen campos del criterio de agrupación
- expresiones con funciones de agregación
En esta lección discutimos sobre esta regla de SQL.
Lo anterior se puede decir de otra manera: en una consulta de agrupación, si se quiere recuperar algún dato (o expresión que use algún dato) que no sea el resultado de una función de agregación, ese dato debe estar en el criterio de agrupación de la cláusula group by.
Vamos a suponer que tenemos esta tabla, llamada TrabajaEnProyecto, que almacena datos sobre los empleados de una empresa, los códigos de los proyectos en los que trabajan y las horas que dedican a cada proyecto:
+-----------+-----------+------------------+----------+-------+ | NSS | nombre | apellidos | proyecto | horas | +-----------+-----------+------------------+----------+-------+ | 123456789 | Juan | Suárez García | 1 | 32.50 | | 123456789 | Juan | Suárez García | 2 | 7.50 | | 333445555 | Francisco | Gómez Fiz | 2 | 10.00 | | 333445555 | Francisco | Gómez Fiz | 3 | 10.00 | | 333445555 | Francisco | Gómez Fiz | 10 | 10.00 | | 333445555 | Francisco | Gómez Fiz | 20 | 10.00 | | 453453453 | Josefa | Cea Silva | 1 | 20.00 | | 453453453 | Josefa | Cea Silva | 2 | 20.00 | | 453453453 | Josefa | Cea Silva | 400 | NULL | | 999887777 | Alicia | Vives Carrascosa | 10 | 10.00 | | 999887777 | Alicia | Vives Carrascosa | 30 | 30.00 | +-----------+-----------+------------------+----------+-------+
P.ej., si queremos obtener el NSS y el nombre completo de los empleados, junto con algunos cálculos que omitiremos de momento, los campos NSS, nombre y apellidos deberán estar en el group by.
select NSS, nombre, apellidos, ...
from TrabajaEnProyecto
group by NSS, nombre, apellidos;¿Por qué? Al agrupar mediante un criterio de agrupación, el sistema gestor de BD (SGBD) reúne varias tuplas de la tabla en un grupo (y otras en otro grupo, y así sucesivamente). Siguiendo con el ejemplo, si agrupamos por NSS, nombre y apellidos obtendremos unos grupos que podríamos representar así:
+-----------+-----------+---------------------+----------+-------+ | NSS | nombre | apellidos | proyecto | horas | +-----------+-----------+---------------------+----------+-------+ | 123456789 | Juan | Suárez García | 1 | 32.50 | | 123456789 | Juan | Suárez García | 2 | 7.50 | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | 333445555 | Francisco | Gómez Fiz | 2 | 10.00 | | 333445555 | Francisco | Gómez Fiz | 3 | 10.00 | | 333445555 | Francisco | Gómez Fiz | 10 | 10.00 | | 333445555 | Francisco | Gómez Fiz | 20 | 10.00 | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | 453453453 | Josefa | Cea Silva | 1 | 20.00 | | 453453453 | Josefa | Cea Silva | 2 | 20.00 | | 453453453 | Josefa | Cea Silva | 400 | NULL | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | 999887777 | Alicia | Vives Carrascosa | 10 | 10.00 | | 999887777 | Alicia | Vives Carrascosa | 30 | 30.00 | +-----------+-----------+---------------------+----------+-------+
En cada grupo, los únicos campos que mantienen un valor uniforme (el mismo en todas las tuplas reunidas en el grupo) son los que se indican en el criterio de agrupación. Los demás campos pueden tener valores diversos, ej. horas o proyecto. Fíjate que no tiene sentido preguntar por ellos en el select:
select NSS, nombre, apellidos, horas, ...
from TrabajaEnProyecto
group by NSS, nombre, apellidos;
Como toda la información del grupo se va a resumir en una única fila, el SGBD no sabría cuál de los diversos valores de horas poner en esa fila-resumen-del-grupo. E.j., ¿qué valor de horas pondría en la fila resumen del grupo del empleado 333445555?
+-----------+-----------+---------------------+----------+-------+ | NSS | nombre | apellidos | proyecto | horas | +-----------+-----------+---------------------+----------+-------+ | 123456789 | Juan | Suárez García | ? | ? | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | 333445555 | Francisco | Gómez Fiz | ? | ? | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | 453453453 | Josefa | Cea Silva | ? | ? | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | 999887777 | Alicia | Vives Carrascosa | ? | ? | +-----------+-----------+---------------------+----------+-------+
Incluso, daos cuenta de que si intentásemos dar un enunciado para esa consulta errónea llegaríamos a algo absurdo: “Listar el nombre de cada empleado, el número de proyectos en los que trabaja y las horas que dedica a … ¿qué proyecto?”.
Por el contrario, el SGBD sí que sabe poner valores de los campos del criterio de agrupación, porque esos valores sí son siempre los mismos en el grupo. El SGBD no tiene dudas con ellos: e.j., en el grupo del empleado 333445555, nombre y apellidos son siempre 'Francisco Gómez Fiz'.
Por esto, y repetimos una vez más, lo único que podemos incluir en el select son campos que están en el group by y cosas como contar el número de proyectos, sumar las horas totales de todos los proyectos, el máximo valor de horas… es decir, expresiones con funciones de agregación:
select NSS, nombre, apellidos, count(*) N, max(horas) H
from TrabajaEnProyecto
group by NSS, nombre, apellidos;
+-----------+-----------+---------------------+---+-------+ | NSS | nombre | apellidos | N | H | +-----------+-----------+---------------------+---+-------+ | 123456789 | Juan | Suárez García | 2 | 32.50 | | 333445555 | Francisco | Gómez Fiz | 4 | 10.00 | | 453453453 | Josefa | Cea Silva | 3 | 20.00 | | 999887777 | Alicia | Vives Carrascosa | 2 | 30.00 | +-----------+-----------+---------------------+---+-------+
También se pueden incluir en el select expresiones, que no sean de agrupación, siempre que usen campos del criterio de agrupación, como en este ejemplo:
select NSS, concat(nombre,' ',apellidos) NomCompleto,
count(*) N, max(horas) H
from TrabajaEnProyecto
group by NSS, nombre, apellidos;
El SGBD no tiene dudas al evaluar la expresión concat(nombre,' ',apellidos) en cada grupo, porque los campos que intervienen en la expresión están en el group by y, en consecuencia, sus valores son uniformes en el grupo.
+-----------+-------------------------+---+-------+ | NSS | NomCompleto | N | H | +-----------+-------------------------+---+-------+ | 123456789 | Juan Suárez García | 2 | 32.50 | | 333445555 | Francisco Gómez Fiz | 4 | 10.00 | | 453453453 | Josefa Cea Silva | 3 | 20.00 | | 999887777 | Alicia Vives Carrascosa | 2 | 30.00 | +-----------+-------------------------+---+-------+
Pero este otro ejemplo es incorrecto, porque incluye en el select, en una expresión que no es de agrupación, un campo (horas) no incluido en el group by:
select NSS, concat(nombre,horas) MAL, count(*) N, max(horas) H
from TrabajaEnProyecto
group by NSS, nombre, apellidos;
En el grupo del empleado 333445555 ¿con qué valor de horas se concatenaría el nombre Francisco? El SGBD no podrá determinarlo. Pero observa que horas sí puede aparecer en una función de agregación. Sí es posible calcular el mayor valor de horas en cada grupo obtenido en la consulta. Siempre podremos usar cualquier campo en funciones de agrupación.
Cumplimiento de la regla en diferentes gestores de BD
Algunos SGBD, como Oracle, son estrictos y se ajustan al estándar SQL: el intento de ejecutar una consulta que no cumpla regla del select-group-by desencadena un error.
Otros SGBD, como MySQL, son más tolerantes, y ejecutan esas consultas mal construidas, llevando, en muchos casos, a resultados incoherentes. Ej.: en MySQL, la siguiente consulta:
select NSS, nombre, apellidos, count(*) N, proyecto, ...
from TrabajaEnProyecto
group by NSS, nombre, apellidos;
Devolvería este resultado (u otro parecido):
+-----------+-----------+---------------------+---+----------+ | NSS | nombre | apellidos | N | proyecto | +-----------+-----------+---------------------+---+----------+ | 123456789 | Juan | Suárez García | 2 | 2 | | 333445555 | Francisco | Gómez Fiz | 4 | 3 | | 453453453 | Josefa | Cea Silva | 3 | 2 | | 999887777 | Alicia | Vives Carrascosa | 2 | 30 | +-----------+-----------+---------------------+---+----------+
MySQL ejecuta la consulta anterior sin dar ningún error, poniendo un valor de proyecto cualquiera de entre los contenidos en cada grupo. En mi opinión, esta es una mala característica de MySQL, que puede llevar a errores.
NOTA: se puede evitar este comportamiento de MySQL si se configura con la propiedad sql_mode=only_full_group_by. Con esta configuración, MySQL sería estricto al cumplir la regla.
Más detalles sobre MySQL
Pero… ¿y si siempre se cumple que, aunque un dato no esté incluido en el criterio de agrupación, su valor es siempre uniforme en todas las tuplas reunidas en cada grupo? Por ejemplo, si en la consulta anterior (en la correcta), hubiésemos agrupado sólo por NSS, el resultado podría ser considerado “correcto” (no conceptualmente correcto, pero sí desde el punto de vista del resultado mostrado)
select NSS, concat(nombre,' ',apellidos), count(*) N
from TrabajaEnProyecto
group by NSS;
En MySQL, esto funciona. El resultado es “correcto” porque, debido a las características de la tabla, solo hay un valor de nombre y apellidos para cada valor de NSS. Insisto en que el resultado no es correcto conceptualmente, aunque el resultado esté bien. Como en el select hay una expresión (concat) que no es de agregación que usa los campos nombre y apellidos, éstos deberían estar en el criterio de agrupación de group by.
Lo mismo se aplica a la cláusula having
En otra lección (que puedes leer aquí) se explica que esta regla también se aplica a la cláusula having
No hay comentarios:
Publicar un comentario