Multiple ‘GROUP BY’ en SQL 2
Os planteo una dudilla típica en el mundo de las bases de datos: tenemos una tabla con todos los empleados de una empresa, cada empleado tiene un único perfil asociado y además cada uno de ellos puede tener (o no) una serie de atributos.
Lo que quiero es obtener una tabla de frecuencias en la que diga cuantos empleados pertenecientes a un perfil y con una serie de atributos, empiezan con la letra A, con la B, con la C y así hasta la Z.
Es decir agrupar los empleados por perfil/atributo y después por letra alfabetica; algo parecido a una nube de tags o un histográma.
La instrucción GROUP BY de SQL sólo nos permite agrupar por un campo, entonces ¿Se puede sacar con una sola consulta sin utilizar programación ni tablas temporales? Si.
Veamos el esquema de datos sobre MySQL:
Tabla employees, almacena los datos relativos a los empleados:
-
-
CREATE TABLE employees (
-
id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-
name varchar(50),
-
profile_id int(10) UNSIGNED NOT NULL,
-
PRIMARY KEY (id)
-
) ENGINE=MyISAM;
Tabla profile, almacena el perfil de los empleados (uno por empleado):
-
-
CREATE TABLE profiles (
-
id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-
name varchar(50),
-
PRIMARY KEY (id)
-
) ENGINE=MyISAM;
Tabla attributes, almacena todos los atributos:
-
-
CREATE TABLE attributes (
-
id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-
name varchar(50),
-
PRIMARY KEY (id)
-
) ENGINE=MyISAM;
Tabla employee_attributes, almacena las relaciones entre empleados y atributos:
-
-
CREATE TABLE employee_attributes (
-
id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-
employee_id int(10) UNSIGNED NOT NULL,
-
attribute_id int(10) UNSIGNED NOT NULL,
-
PRIMARY KEY (id)
-
) ENGINE=MyISAM;
Insertamos algunos datos de prueba:
-
-
INSERT INTO profiles (name) VALUES(‘perfil de programador’);
-
INSERT INTO profiles (name) VALUES(‘perfil de manager’);
-
INSERT INTO profiles (name) VALUES(‘perfil de tester’);
-
-
INSERT INTO attributes (name) VALUES(‘jornada completa’);
-
INSERT INTO attributes (name) VALUES(‘empleado del mes’);
-
INSERT INTO attributes (name) VALUES(‘encargado de sección’);
-
INSERT INTO attributes (name) VALUES(‘le toca guardia’);
-
-
INSERT INTO employees (name,profile_id) VALUES(‘pepito’, 1);
-
INSERT INTO employees (name,profile_id) VALUES(‘juanito’, 1);
-
INSERT INTO employees (name,profile_id) VALUES(‘manolito’, 1);
-
INSERT INTO employees (name,profile_id) VALUES(‘menganito’, 1);
-
-
INSERT INTO employee_attributes (employee_id,attribute_id) VALUES(1,1);
-
INSERT INTO employee_attributes (employee_id,attribute_id) VALUES(1,3);
-
INSERT INTO employee_attributes (employee_id,attribute_id) VALUES(2,1);
-
INSERT INTO employee_attributes (employee_id,attribute_id) VALUES(2,2);
-
INSERT INTO employee_attributes (employee_id,attribute_id) VALUES(2,3);
-
INSERT INTO employee_attributes (employee_id,attribute_id) VALUES(3,1);
-
INSERT INTO employee_attributes (employee_id,attribute_id) VALUES(3,4);
-
INSERT INTO employee_attributes (employee_id,attribute_id) VALUES(3,3);
-
INSERT INTO employee_attributes (employee_id,attribute_id) VALUES(3,2);
La solución Quick&Dirt empleada masivamente en estos casos es hacer una consulta para filtrar los empleados que nos interesan mediante la siguiente consulta:
-
-
SELECT Employee.id, Employee.name
-
FROM employees AS Employee, employee_attributes AS EmployeeAttr
-
WHERE Employee.id = EmployeeAttr.employee_id
-
AND EmployeeAttr.attribute_id IN (1,2,3)
-
AND Employee.profile_id = 1
-
GROUP BY Employee.id
Resultado:
id name ---------------- 1 pepito 2 juanito 3 manolito 4 menganito
… para después mediante programación, agruparlos y realizar el conteo.
¿Es óptimo? NO, hay que hacer un tratamiento a posteriori de los datos.
¿Es sencillo? NO, quizá sea más complicado y engorroso la programación del tratamiento posterior que hacerlo corréctamente.
¿Es elegante? NO, se mezcla la capa de negocio con la capa de datos.
¿Entonces cómo lo hago?
La mejor forma de hacer este tipo de consulta y mantener tu karma como programador es utilizar una Subquery. Una subquery es una manera especial de anidar sentencias SQL para componer consultas mas complejas que de otra forma no se podrían realizar.
Las subqueries se agrupan fundamentalmente en dos grandes grupos: escalares y correlacionadas, aunque la que voy a utilizar yo es un poco diferente puesto que el resultado de la subquery suplantará a una tabla; jugando con este efecto conseguimos el comportamiento de un GROUP BY multiple.
- Más información en el apartado Subquery Syntax de la documentación de MySQL.
- Más infromación sobre Subqueries correlacionadas y escalares (con muchos ejemplos) en MySQL Hispano.
Veamos la consulta:
-
-
SELECT LEFT(Employee.name,1) AS alphacrum, COUNT(LEFT(Employee.name,1)) AS total
-
FROM (
-
SELECT Employee.id, Employee.name
-
FROM employees AS Employee, employee_attributes AS EmployeeAttr
-
WHERE Employee.id = EmployeeAttr.employee_id
-
AND EmployeeAttr.attribute_id IN (1,2,3)
-
AND Employee.profile_id = 1
-
GROUP BY Employee.id
-
) AS Employee
-
GROUP BY alphacrum
-
ORDER BY alphacrum ASC;
Resultado:
alphacrum total ---------------------------- j 1 m 2 p 1
Como podeis observar, la tabla employees de la consulta principal no es la original; es el resultado del filtrado previo según los parametros que nos interesen.
Una vez filtrados es muy sencillo reordenar los datos a nuestro gusto, únicamente agrupamos por letra y realizamos el conteo de los resultados con COUNT, devolviendonos los datos tal y como nos hacen falta.
De esta manera se utiliza efectivamente el cache del servidor de bases de datos y la programación de la aplicación no se satura con un montón funciones de tratamiento de datos. Simple y elegante.
menéame
