Agrupació Joan Puigcerver Ibáñez j.puigcerveribanez@edu.gva.es Llicència: CC BY-NC-SA 4.0 (Reconeixement - NoComercial - CompartirIgual) 🅭 Introducció En el món de les bases de dades, organitzar i resumir les dades és un aspecte fonamental per extreure conclusions significatives. En aquest moment entra en joc la clàusula GROUP BY, que permet agrupar i agregar dades basant-se en criteris específics. Funcions d'agrupació Alhora d'agrupar les dades es poden utilitzar les funcions d'agregació per realitzar operacions i agregar les dades (Veure Material: Consultes bàsiques - Funcions d'agragació). Les funcions més comuns són: COUNT(A): Compta el nombre de registres de la columna A. MIN(A): Retorna el valor mínim dels registres de la columna A. MAX(A): Retorna el valor màxim dels registres de la columna A. SUM(A): Retorna la suma dels valors dels registres de la columna A. AVG(A): Retorna la mitjana aritmètica dels valors dels registres de la columna A. DISTINCT(A): Retorna els valors no repetits dels registres de la columna A. GROUP BY La clàusula GROUP BY pot ser utilitzada en sentències SELECT per agrupar les dades basant-se en un criteri concret. La sintàxi és: SELECT [ALL | DISTINCT | DISTINCTROW] column_expr [[AS] alias] [, column2_expr [[AS] alias2] ...] FROM table [WHERE condition] [ORDER BY {col_name} [ASC | DESC], ...] [GROUP BY {col_name} [ASC | DESC], ... [WITH ROLLUP]] [HAVING aggregate_condition] GROUP BY: Indica que es realitzarà una agrupació basant-se en les columnes definides. col_name, ...: Indica quines columnes s'utilitzaran per realitar l'agrupació. Tots els registres que tinguen els mateixos valors en les columnes especificades s'agruparan en un únic registre. WITH ROLLUP: Quan s'indica més d'uan columna, també s'obtindran els subtotals. HAVING aggregate_condition: S'utilitza per definir condicions utilitzant les columnes agrupades, semblant al funcionament de WHERE. Documentació Documentació oficial sobre agrupació: MariaDB: https://mariadb.com/kb/en/group-by/ MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions-and-modifiers.html Agrupació amb una única columna Vegem com es poden agrupar registres d'una taula a partir d'una única columna. Tots els registres que tinguen el mateix valor en la columna indicada seran agrupats. Dades d'exemple En aquest apartat treballarem amb una versió simplidicada de la taula cursa de la base de dades Institut. Figura 3.1.1: Taula cursa amb els seus registres Taula Cursa ID Mòdul NIA Nota 0484 00000000 5.6 0484 00000001 8.0 0484 00000002 4.3 0485 00000000 7.1 0485 00000001 9.2 0485 00000002 6.1 CREATE TABLE cursa ( id_modul varchar(6), nia varchar(8), nota decimal(4,2) check (nota >= 0 and nota <= 10), primary key (nia, id_modul) ); INSERT INTO cursa (id_modul, nia, nota) VALUES ("0484", "00000000", 5.6), ("0484", "00000001", 8.0), ("0484", "00000002", 4.3), ("0485", "00000000", 7.1), ("0485", "00000001", 9.2), ("0485", "00000002", 6.1); Agrupació per estudiant Mitjançant la clàusula GROUP BY agrupem els resultats de cada estudiant. Per fer-ho, cal indicar en aquesta clàusula la columna que serveix com a criteri per fer l'agrupació. En aquest cas, haurem d'utilitzar el nia. Figura 3.1.2: Registres agrupats mitjançant GROUP BY amb el camp nia Taula Cursa ID Mòdul NIA Nota 0484 00000000 5.6 0484 00000001 8.0 0484 00000002 4.3 0485 00000000 7.1 0485 00000001 9.2 0485 00000002 6.1 Desitjem obtindre la nota mitjana obtinguda per cada estudiant en tots els mòduls que ha cursat. SELECT nia, avg(nota) nota_mitjana FROM cursa GROUP BY nia; +----------+--------------+ | nia | nota_mitjana | +----------+--------------+ | 00000000 | 6.350000 | | 00000001 | 8.600000 | | 00000002 | 5.200000 | +----------+--------------+ Agrupació per mòdul En aquest cas, agrupem les mateixes dades per obtindre la mitjana de les notes obtingudes pels estudiants per cada mòdul. L'agrupació s'haurà de realitzar amb la columna id_modul Figura 3.1.3: Registres agrupats mitjançant GROUP BY amb el camp id_modul Taula Cursa ID Mòdul NIA Nota 0484 00000000 5.6 0484 00000001 8.0 0484 00000002 4.3 0485 00000000 7.1 0485 00000001 9.2 0485 00000002 6.1 SELECT id_modul, avg(nota) nota_mitjana FROM cursa GROUP BY id_modul; +----------+--------------+ | id_modul | nota_mitjana | +----------+--------------+ | 0484 | 5.966667 | | 0485 | 7.466667 | +----------+--------------+ Agrupació amb múltiples columnes Vegem com es poden agrupar registres d'una taula a partir de múltiples columnes. Tots els registres que tinguen el mateixos valors en la columnes indicades seran agrupats. Dades d'exemple Afegim la columna curs_academic a la taula cursa. Figura 3.2.1: Taula cursa amb el curs acadèmic i els seus registres Taula Cursa ID Mòdul NIA Nota Curs acadèmic 0484 00000000 5.6 23/24 0484 00000001 8.0 23/24 0484 00000002 4.3 23/24 0485 00000000 7.1 23/24 0485 00000001 9.2 23/24 0485 00000002 6.1 23/24 0484 00000003 6.3 22/23 0484 00000004 9.5 22/23 0485 00000003 7.8 22/23 0485 00000004 8.3 22/23 0489 00000003 7.2 23/24 0489 00000004 6.8 23/24 0490 00000003 8.9 23/24 0490 00000004 8.1 23/24 CREATE TABLE cursa ( id_modul varchar(6), nia varchar(8), nota decimal(4,2) check (nota >= 0 and nota <= 10), curs_academic char(5), primary key (nia, id_modul, curs_academic) ); INSERT INTO cursa (id_modul, nia, nota, curs_academic) VALUES ("0484", "00000000", 5.6, "23/24"), ("0484", "00000001", 8.0, "23/24"), ("0484", "00000002", 4.3, "23/24"), ("0485", "00000000", 7.1, "23/24"), ("0485", "00000001", 9.2, "23/24"), ("0485", "00000002", 6.1, "23/24"), ("0484", "00000003", 6.3, "22/23"), ("0484", "00000004", 9.5, "22/23"), ("0485", "00000003", 7.8, "22/23"), ("0485", "00000004", 8.3, "22/23"), ("0489", "00000003", 7.2, "23/24"), ("0489", "00000004", 6.8, "23/24"), ("0490", "00000003", 8.9, "23/24"), ("0490", "00000004", 8.1, "23/24"); Agrupació per mòdul i curs acadèmic Amb les dades anteriors, si sols agrupem els registres per mòdul professional, el càlcul de la nota mitjana es realitzaria amb les notes de tots els estudiants de tots els cursos acadèmics. Si volem veure la nota mitjana de cada mòdul en cada curs acadèmic, hem d'indicar els dos camps en la clàusula GROUP BY. Figura 3.2.2: Registres agrupats pels camps id_modul i curs_academic Taula Cursa ID Mòdul NIA Nota Curs acadèmic 0484 00000000 5.6 23/24 0484 00000001 8.0 23/24 0484 00000002 4.3 23/24 0485 00000000 7.1 23/24 0485 00000001 9.2 23/24 0485 00000002 6.1 23/24 0484 00000003 6.3 22/23 0484 00000004 9.5 22/23 0485 00000003 7.8 22/23 0485 00000004 8.3 22/23 0489 00000003 7.2 23/24 0489 00000004 6.8 23/24 0490 00000003 8.9 23/24 0490 00000004 8.1 23/24 SELECT id_modul, curs_academic, avg(nota) nota_mitjana FROM cursa GROUP BY id_modul, curs_academic ORDER BY id_modul, curs_academic; +----------+---------------+---------------+ | id_modul | curs_academic | nota_mitajana | +----------+---------------+---------------+ | 0484 | 22/23 | 7.900000 | | 0484 | 23/24 | 5.966667 | | 0485 | 22/23 | 8.050000 | | 0485 | 23/24 | 7.466667 | | 0489 | 23/24 | 7.000000 | | 0490 | 23/24 | 8.500000 | +----------+---------------+---------------+ Agrupació per estudiant i curs acadèmic De la mateixa manera, podem agrupar les notes dels estudiants per cada curs acadèmic. Figura 3.2.3: Registres agrupats pels camps nia i curs_academic Taula Cursa ID Mòdul NIA Nota Curs acadèmic 0484 00000000 5.6 23/24 0484 00000001 8.0 23/24 0484 00000002 4.3 23/24 0485 00000000 7.1 23/24 0485 00000001 9.2 23/24 0485 00000002 6.1 23/24 0484 00000003 6.3 22/23 0484 00000004 9.5 22/23 0485 00000003 7.8 22/23 0485 00000004 8.3 22/23 0489 00000003 7.2 23/24 0489 00000004 6.8 23/24 0490 00000003 8.9 23/24 0490 00000004 8.1 23/24 SELECT nia, curs_academic, avg(nota) nota_mitjana FROM cursa GROUP BY nia, curs_academic ORDER BY nia, curs_academic; +----------+---------------+--------------+ | nia | curs_academic | nota_mitjana | +----------+---------------+--------------+ | 00000000 | 23/24 | 6.350000 | | 00000001 | 23/24 | 8.600000 | | 00000002 | 23/24 | 5.200000 | | 00000003 | 22/23 | 7.050000 | | 00000003 | 23/24 | 8.050000 | | 00000004 | 22/23 | 8.900000 | | 00000004 | 23/24 | 7.450000 | +----------+---------------+--------------+ Agrupació amb subtotals (ROLLUP) Les paraules claus WITH ROLLUP en la clàusula GROUP BY serveixen per, a més de mostrar els resultats de l'agrupament, també mostrar els subtotals que existeixen. En el resultats, els subtotals calculats tindran el valor NULL en alguna de les columnes utilitzades en l'agrupació, per indicar que aquest camp no s'ha tingut en compte al realitzar el càlcul del subtotal. Agrupació per mòdul i curs acadèmic amb subtotals Agrupem els registres de cada mòdul per cada curs acadèmic. En aquest cas, els subtotals calculats són: La mitjana de totes les notes. La mitjana de les notes de cada mòdul en tots els cursos acadèmics. SELECT id_modul, curs_academic, avg(nota) nota_mitjana FROM cursa GROUP BY id_modul, curs_academic WITH ROLLUP ORDER BY id_modul, curs_academic; +----------+---------------+--------------+ | id_modul | curs_academic | nota_mitjana | +----------+---------------+--------------+ | NULL | NULL | 7.371429 | | 0484 | NULL | 6.740000 | | 0484 | 22/23 | 7.900000 | | 0484 | 23/24 | 5.966667 | | 0485 | NULL | 7.700000 | | 0485 | 22/23 | 8.050000 | | 0485 | 23/24 | 7.466667 | | 0489 | NULL | 7.000000 | | 0489 | 23/24 | 7.000000 | | 0490 | NULL | 8.500000 | | 0490 | 23/24 | 8.500000 | +----------+---------------+--------------+ Agrupació per mòdul i curs acadèmic amb subtotals Agrupem els registres de cada estudiant per cada curs acadèmic. En aquest cas, els subtotals calculats són: La mitjana de totes les notes. La mitjana de les notes de cada estudiant en tots els cursos acadèmics. SELECT nia, curs_academic, avg(nota) nota_mitjana FROM cursa GROUP BY nia, curs_academic WITH ROLLUP ORDER BY nia, curs_academic; +----------+---------------+--------------+ | nia | curs_academic | nota_mitjana | +----------+---------------+--------------+ | NULL | NULL | 7.371429 | | 00000000 | NULL | 6.350000 | | 00000000 | 23/24 | 6.350000 | | 00000001 | NULL | 8.600000 | | 00000001 | 23/24 | 8.600000 | | 00000002 | NULL | 5.200000 | | 00000002 | 23/24 | 5.200000 | | 00000003 | NULL | 7.550000 | | 00000003 | 22/23 | 7.050000 | | 00000003 | 23/24 | 8.050000 | | 00000004 | NULL | 8.175000 | | 00000004 | 22/23 | 8.900000 | | 00000004 | 23/24 | 7.450000 | +----------+---------------+--------------+ HAVING La clàusula HAVING serveix per filtrar es resultats d'una consulta que utilitza GROUP BY a partir d'una condició. La diferència amb la clàusula WHERE és que HAVING s'aplica després de fer l'agrupació i WHERE s'aplica abans. Agrupació per estudiant i curs acadèmic amb una condició De la mateixa manera, podem agrupar les notes dels estudiants per cada curs acadèmic. SELECT nia, curs_academic, avg(nota) nota_mitjana FROM cursa GROUP BY nia, curs_academic HAVING nota_mitjana >= 8 ORDER BY nia, curs_academic; +----------+---------------+--------------+ | nia | curs_academic | nota_mitjana | +----------+---------------+--------------+ | 00000001 | 23/24 | 8.600000 | | 00000003 | 23/24 | 8.050000 | | 00000004 | 22/23 | 8.900000 | +----------+---------------+--------------+ 📌 Aquest document pot quedar desactualitzat després d’imprimir-lo. Pots consultar la versió més recent a la pàgina web. 🌿 Abans d’imprimir aquest document, considera si és realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.

CC BY-NC-SA 4.0 (Reconeixement - NoComercial - CompartirIgual) 🅭 Joan Puigcerver Ibáñez