Functiile de sumarizare se utilizeaza pentru a extrage informatii globale cu privire la datele din tabelele unei baze de date.
Utilizarea unei functii de sumarizare intr-o instructiune SELECT care nu detine clauza GROUP BY este echivalenta cu rularea instructiunii pentru un grup care contine toate inregistrarile.
mysql> SELECT COUNT(*) INTO @actors -> FROM sakila.actor; Query OK, 1 row affected
mysql> SELECT @actors; +---------+ | @actors | +---------+ | 200 | +---------+ 1 row in set
Informatiile de sumarizare sunt extrase pe grupuri de inregistrari; au sens in contextul gruparii datelor. De exemplu, din tabelul actor al bazei de date sakila putem extrage numarul de actori care detin acelasi prenume prin intermediul functiei de sumarizare COUNT(*).
mysql> SELECT first_name, COUNT(*) AS no -> FROM sakila.actor -> GROUP BY first_name -> HAVING no >= 4 -> ORDER BY no DESC, first_name ASC; +------------+----+ | first_name | no | +------------+----+ | JULIA | 4 | | KENNETH | 4 | | PENELOPE | 4 | +------------+----+ 3 rows in set
La nivelul exemplului precedent sunt pastrate doar grupurile de actori cu acelasi prenume, care detin cel putin patru membri/persoane.
Intr-un alt exemplu, consideram adresele de la nivelul tabelului address din baza de date sakila pentru care districtul este QLD sau Alberta. Pentru aceste adrese, valoarea corespunzatoare codului postal prezinta valoarea NULL.
mysql> SELECT address, postal_code, district -> FROM sakila.address -> WHERE district IN ('QLD', 'Alberta'); +----------------------+-------------+----------+ | address | postal_code | district | +----------------------+-------------+----------+ | 47 MySakila Drive | NULL | Alberta | | 28 MySQL Boulevard | NULL | QLD | | 23 Workhaven Lane | NULL | Alberta | | 1411 Lillydale Drive | NULL | QLD | +----------------------+-------------+----------+ 4 rows in set
Functia de sumarizare COUNT(expresie) returneaza, pentru expresia specificata, numarul de inregistrari care prezinta valori diferite de NULL. Aceasta forma a functiei COUNT() returneaza valoarea 0, daca nu exista inregistrari in rezultat.
mysql> SELECT COUNT(postal_code) AS no, district -> FROM sakila.address -> WHERE district IN ('QLD', 'Alberta') -> GROUP BY district; +----+----------+ | no | district | +----+----------+ | 0 | Alberta | | 0 | QLD | +----+----------+ 2 rows in set
Spre deosebire de COUNT(expresie), COUNT(*) returneaza numarul de inregistrari selectate, indiferent daca acestea contin sau nu valori NULL.
mysql> SELECT COUNT(*) AS no, district -> FROM sakila.address -> WHERE district IN ('QLD', 'Alberta') -> GROUP BY district; +----+----------+ | no | district | +----+----------+ | 2 | Alberta | | 2 | QLD | +----+----------+ 2 rows in set
Urmatorul tabel include principalele functii de sumarizare disponibile in cadrul sistemelor de tip MySQL.
Functie | Semnificatie |
---|---|
COUNT(*) | returneaza numarul de inregistrari pentru fiecare grup |
COUNT(expresie) | returneaza numarul de inregistrari care prezinta valoarea expresiei pentru fiecare grup |
AVG(expresie) | returneaza media aritmetica a valorilor expresiei pentru fiecare grup |
MIN(expresie) | returneaza valoarea minima a expresiei pentru fiecare grup |
MAX(expresie) | returneaza valoarea maxima a expresiei pentru fiecare grup |
SUM(expresie) | returneaza suma valorilor expresiei pentru fiecare grup |
Daca ne intoarcem la inregistrarile din tabelul actor al bazei de date sakila, putem implementa grupuri de inregistrari pentru fiecare initiala prezenta la nivelul coloanei first_name, unde initiala se gaseste in prima parte a alfabetului.
mysql> SELECT SUBSTRING(first_name, 1, 1) initial, COUNT(*) AS no, MIN(first_name) AS first -> FROM sakila.actor -> GROUP BY initial -> HAVING initial < 'M' -> ORDER BY initial ASC; +---------+----+----------+ | initial | no | first | +---------+----+----------+ | A | 13 | ADAM | | B | 8 | BELA | | C | 18 | CAMERON | | D | 7 | DAN | | E | 7 | ED | | F | 6 | FAY | | G | 16 | GARY | | H | 6 | HARRISON | | I | 1 | IAN | | J | 23 | JADA | | K | 11 | KARL | | L | 6 | LAURA | +---------+----+----------+ 12 rows in set
La nivelul fiecarui grup de inregistrari pot fi utilizate functiile COUNT(), respectiv MIN() si/sau MAX() pentru a determina numarul de actori al caror prenume incepe cu o anumita initiala, dar si primul/ultimul prenume de la nivelul fiecarui grup.
mysql> SELECT SUBSTRING(first_name, 1, 1) initial, COUNT(*) AS no, MAX(first_name) AS first -> FROM sakila.actor -> GROUP BY initial -> HAVING initial < 'M' -> ORDER BY initial ASC; +---------+----+----------+ | initial | no | first | +---------+----+----------+ | A | 13 | AUDREY | | B | 8 | BURT | | C | 18 | CUBA | | D | 7 | DUSTIN | | E | 7 | EWAN | | F | 6 | FRED | | G | 16 | GROUCHO | | H | 6 | HUMPHREY | | I | 1 | IAN | | J | 23 | JULIANNE | | K | 11 | KIRSTEN | | L | 6 | LUCILLE | +---------+----+----------+ 12 rows in set
Prenumele actorilor de la nivelul fiecarui grup de inregistrari (grup definit de o initiala) pot fi concatenate folosind functia GROUP_CONCAT().
mysql> SELECT SUBSTRING(first_name, 1, 1) initial, COUNT(*) AS no, -> GROUP_CONCAT(DISTINCT first_name) AS list_firts -> FROM sakila.actor -> GROUP BY initial -> HAVING initial < 'M' -> ORDER BY initial ASC; +---------+----+----------------------------------------------------------------------------------------------------+ | initial | no | list_firts | +---------+----+----------------------------------------------------------------------------------------------------+ | A | 13 | ADAM,AL,ALAN,ALBERT,ALEC,ANGELA,ANGELINA,ANNE,AUDREY | | B | 8 | BELA,BEN,BETTE,BOB,BURT | | C | 18 | CAMERON,CARMEN,CARY,CATE,CHARLIZE,CHRIS,CHRISTIAN,CHRISTOPHER,CUBA | | D | 7 | DAN,DARYL,DEBBIE,DUSTIN | | E | 7 | ED,ELLEN,ELVIS,EMILY,EWAN | | F | 6 | FAY,FRANCES,FRED | | G | 16 | GARY,GENE,GEOFFREY,GINA,GOLDIE,GRACE,GREG,GREGORY,GRETA,GROUCHO | | H | 6 | HARRISON,HARVEY,HELEN,HENRY,HUMPHREY | | I | 1 | IAN | | J | 23 | JADA,JAMES,JANE,JAYNE,JEFF,JENNIFER,JESSICA,JIM,JODIE,JOE,JOHN,JOHNNY,JON,JUDE,JUDY,JULIA,JULIANNE | | K | 11 | KARL,KENNETH,KEVIN,KIM,KIRK,KIRSTEN | | L | 6 | LAURA,LAURENCE,LISA,LIZA,LUCILLE | +---------+----+----------------------------------------------------------------------------------------------------+ 12 rows in set
Sintaxa completa a functiei de sumarizare GROUP_CONCAT() este precizata mai jos.
GROUP_CONCAT([DISTINCT] expresie [ORDER BY {pozitie_coloana | nume_coloana | expresie} [ASC | DESC]] [SEPARATOR sir_caractere])
mysql> SELECT SUBSTRING(first_name, 1, 1) initial, -> GROUP_CONCAT(DISTINCT first_name ORDER BY first_name DESC SEPARATOR ', ') AS list_firts -> FROM sakila.actor -> GROUP BY initial -> HAVING initial IN ('D', 'E', 'F') -> ORDER BY initial ASC; +---------+-------------------------------+ | initial | list_firts | +---------+-------------------------------+ | D | DUSTIN, DEBBIE, DARYL, DAN | | E | EWAN, EMILY, ELVIS, ELLEN, ED | | F | FRED, FRANCES, FAY | +---------+-------------------------------+ 3 rows in set