Funcțiile agregate sunt despre toate
- Efectuarea de calcule pe mai multe rânduri
- Dintr-o singură coloană a unui tabel
- Și returnând o singură valoare.
Standardul ISO definește cinci (5) funcții agregate și anume;
1) NUMĂR
3) AVG
4) MIN
5) MAX
De ce să folosiți funcții agregate.
Din perspectiva afacerii, diferite niveluri ale organizației au cerințe de informații diferite. Managerii de nivel superior sunt de obicei interesați să cunoască cifre întregi și nu sunt necesare detaliile individuale.
> Funcțiile agregate ne permit să producem cu ușurință date rezumate din baza noastră de date.
De exemplu, din baza noastră de date myflix, managementul poate necesita rapoarte următoare
- Filme cel mai puțin închiriate.
- Cele mai multe filme închiriate.
- Numărul mediu pe care fiecare film este închiriat într-o lună.
Producem cu ușurință rapoarte de mai sus folosind funcții agregate.
Să analizăm în detaliu funcțiile agregate.
Funcția COUNT
Funcția COUNT returnează numărul total de valori din câmpul specificat. Funcționează atât pe tipuri de date numerice, cât și non-numerice. Toate funcțiile agregate implicit exclud valorile nule înainte de a lucra la date.
COUNT (*) este o implementare specială a funcției COUNT care returnează numărul tuturor rândurilor dintr-un tabel specificat. COUNT (*) consideră, de asemenea, valori nule și duplicate.
Tabelul prezentat mai jos prezintă datele din tabelul movierentals
| numar de referinta | Data tranzacției | return_date | numar de membru | film_id | film_ a revenit |
|---|---|---|---|---|---|
| 11 | 20-06-2012 | NUL | 1 | 1 | 0 |
| 12 | 22-06-2012 | 25-06-2012 | 1 | 2 | 0 |
| 13 | 22-06-2012 | 25-06-2012 | 3 | 2 | 0 |
| 14 | 21-06-2012 | 24-06-2012 | 2 | 2 | 0 |
| 15 | 23-06-2012 | NUL | 3 | 3 | 0 |
Să presupunem că vrem să obținem de câte ori a fost închiriat filmul cu ID-ul 2
SELECT COUNT(`movie_id`) FROM `movierentals` WHERE `movie_id` = 2;
Executarea interogării de mai sus în MySQL workbench împotriva myflixdb ne oferă următoarele rezultate.
| COUNT('movie_id') |
|---|
| 3 |
Cuvânt cheie DISTINCT
Cuvântul cheie DISTINCT care ne permite să omitem duplicatele din rezultatele noastre. Acest lucru se realizează prin gruparea unor valori similare.
Pentru a aprecia conceptul de Distinct, permite să executăm o interogare simplă
SELECT `movie_id` FROM `movierentals`;
| movie_id |
|---|
| 1 |
| 2 |
| 2 |
| 2 |
| 3 |
Acum să executăm aceeași interogare cu cuvântul cheie distinct -
SELECT DISTINCT `movie_id` FROM `movierentals`;
Așa cum se arată mai jos, distincte omite înregistrările duplicate din rezultate.
| movie_id |
|---|
| 1 |
| 2 |
| 3 |
Funcția MIN
Funcția MIN returnează cea mai mică valoare din câmpul tabelului specificat .
De exemplu, să presupunem că vrem să știm anul în care a fost lansat cel mai vechi film din biblioteca noastră, putem folosi funcția MIN a MySQL pentru a obține informațiile dorite.
Următoarea interogare ne ajută să realizăm acest lucru
SELECT MIN(`year_released`) FROM `movies`;
Executarea interogării de mai sus în MySQL workbench împotriva myflixdb ne oferă următoarele rezultate.
| MIN('year_released') |
|---|
| 2005 |
Funcția MAX
Așa cum sugerează și numele, funcția MAX este opusă funcției MIN. Aceasta returnează cea mai mare valoare din câmpul de masă specificat .
Să presupunem că vrem să obținem anul în care a fost lansat cel mai recent film din baza noastră de date. Putem folosi cu ușurință funcția MAX pentru a realiza acest lucru.
Următorul exemplu returnează ultimul an al filmului lansat.
SELECT MAX(`year_released`) FROM `movies`;
Executarea interogării de mai sus în MySQL workbench folosind myflixdb ne oferă următoarele rezultate.
| MAX('year_released') |
|---|
| 2012 |
Funcția SUM
Să presupunem că dorim un raport care să ofere suma totală a plăților efectuate până acum. Putem utiliza funcția MySQL SUM care returnează suma tuturor valorilor din coloana specificată . SUM funcționează numai pe câmpuri numerice . Valorile nule sunt excluse din rezultatul returnat.
Următorul tabel prezintă datele din tabelul de plăți-
| plată_id | numar de membru | Data de plată | Descriere | Suma plătită | extern_număr_referință |
|---|---|---|---|---|---|
| 1 | 1 | 23-07-2012 | Plata închirierii filmului | 2500 | 11 |
| 2 | 1 | 25-07-2012 | Plata închirierii filmului | 2000 | 12 |
| 3 | 3 | 30-07-2012 | Plata închirierii filmului | 6000 | NUL |
Interogarea prezentată mai jos obține toate plățile efectuate și le rezumă pentru a obține un singur rezultat.
SELECT SUM(`amount_paid`) FROM `payments`;
Executarea interogării de mai sus în MySQL workbench împotriva myflixdb dă următoarele rezultate.
| SUM('amount_paid') |
|---|
| 10500 |
Funcția AVG
Funcția MySQL AVG returnează media valorilor dintr-o coloană specificată . La fel ca funcția SUM, funcționează numai pe tipuri de date numerice .
Să presupunem că vrem să găsim suma medie plătită. Putem utiliza următoarea interogare -
SELECT AVG(`amount_paid`) FROM `payments`;
Executarea interogării de mai sus în bancul de lucru MySQL ne oferă următoarele rezultate.
| AVG('amount_paid') |
|---|
| 3500 |
rezumat
- MySQL acceptă toate cele cinci (5) funcții agregate standard ISO COUNT, SUM, AVG, MIN și MAX.
- Funcțiile SUM și AVG funcționează numai pe date numerice.
- Dacă doriți să excludeți valorile duplicate din rezultatele funcției agregate, utilizați cuvântul cheie DISTINCT. Cuvântul cheie ALL include chiar și duplicate. Dacă nu este specificat nimic, ALL se presupune ca implicit.
- Funcțiile agregate pot fi utilizate împreună cu alte clauze SQL, cum ar fi GROUP BY
Joc de inteligență
Crezi că funcțiile agregate sunt ușoare. Incearca asta!
Următorul exemplu grupează membrii după nume, numără numărul total de plăți, suma medie a plății și suma totală a sumelor plății.
SELECT m.`full_names`,COUNT(p.`payment_id`) AS `paymentscount`,AVG(p.`amount_paid`) AS `averagepaymentamount`,SUM(p.`amount_paid`) AS `totalpayments` FROM members m, payments p WHERE m.`membership_number` = p.`membership_number` GROUP BY m.`full_names`;
Executarea exemplului de mai sus în MySQL workbench ne oferă următoarele rezultate.
