MySQL Funcții de agregare: SUMĂ, COUNT, AVG & MAX

Ce sunt funcțiile agregate în MySQL?

An functie de agregat citește mai multe rânduri dintr-o singură coloană și le restrânge într-o singură valoare. Funcțiile de agregare au ca scop:

• Efectuarea calculelor 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. COUNT
2. USM
3. AVG
4. MIN
5. MAX

O regulă se aplică tuturor celor cinci: funcțiile agregate ignoră valorile NULLCOUNT(*) este singura excepție, iar mai jos vom analiza motivul.

De ce să folosiți funcții agregate

Diferitele niveluri organizaționale au cerințe informaționale diferite. Managerii de nivel superior sunt de obicei interesați de cifre întregi, nu de detalii 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, conducerea poate solicita următoarele rapoarte:

• Filmele cel mai puțin închiriate.
• Cele mai multe filme închiriate.
• Numărul mediu de închirieri ale fiecărui film într-o lună.

Toate rapoartele de mai sus provin din funcții agregate. Să analizăm fiecare în detaliu.

Funcția COUNT

Funcția COUNT returnează numărul total de valori din câmpul specificat, atât pentru tipurile de date numerice, cât și pentru cele nenumerice. Ca orice funcție de agregare, COUNT(coloană) exclude valorile NULL.

COUNT(*) este o formă specială care returnează numărul tuturor rândurilor dintr-un tabel. De asemenea, numără NULL-uri și duplicate, deoarece numără rânduri în loc de valori.

Tabelul movierentals conține aceste date:

Să presupunem că vrem să aflăm de câte ori a fost închiriat filmul cu id-ul 2.

SELECT COUNT(`movie_id`) FROM `movierentals` WHERE `movie_id` = 2;

Executarea acestui lucru în MySQL Banc de lucru `against myflixdb` returnează 3, deoarece trei rânduri conțin movie_id 2.

Cuvânt cheie DISTINCT

COUNT răspunde la „câte”. Următoarea întrebare este de obicei „câte” diferit „cele”, și pentru asta există DISTINCT.

Cuvântul cheie DISTINCT omite duplicatele din rezultatele noastre după grup.ping valori identice împreună, exact așa cum sugerează ilustrația de mai sus.

Mai întâi, să executăm o interogare simplă.

SELECT `movie_id` FROM `movierentals`;

Acum aceeași interogare cu cuvântul cheie DISTINCT:

SELECT DISTINCT `movie_id` FROM `movierentals`;

DISTINCT omite înregistrările duplicate:

COUNT vs COUNT(*) vs COUNT(DISTINCT): Pe care ar trebui să îl utilizați?

DISTINCT poate fi, de asemenea, plasat în interiorul o funcție agregată, iar aici pierd majoritatea începătorilor track dintre care rânduri sunt efectiv numărate. Cele patru formulare de mai jos rulează toate pe aceeași tabelă movierentals cu cinci rânduri prezentată anterior, însă nu toate returnează același număr. Diferența se reduce la două întrebări: formularul numără rânduri sau valori și păstrează duplicatele?

SELECT COUNT(*) AS `all_rows`,
       COUNT(`return_date`) AS `returned_rows`,
       COUNT(DISTINCT `movie_id`) AS `unique_movies`
FROM `movierentals`;

💡 Sfat: Folosiți COUNT(*) pentru numărarea rândurilor, COUNT(coloană) când o valoare NULL ar trebui să însemne „nu se aplică” și COUNT(DISTINCT coloană) pentru valori unice. Opusul lui DISTINCT este ALL — valoarea implicită și, prin urmare, rareori scrisă.

Funcția MIN

Funcția MIN returnează cea mai mică valoare din câmpul tabel specificat.

Să presupunem că vrem anul în care a fost lansat cel mai vechi film din biblioteca noastră. MySQLFuncția MIN a lui ne oferă asta.

SELECT MIN(`year_released`) FROM `movies`;

Rezultat:

MAX

Așa cum sugerează și numele, funcția MAX este opusul funcției MIN. Aceasta returnează cea mai mare valoare din câmpul de tabel specificat.

Să presupunem că dorim anul în care a fost lansat cel mai recent film din baza noastră de date. Următorul exemplu îl returnează.

SELECT MAX(`year_released`) FROM `movies`;

Rezultat:

Funcția SUM

MIN și MAX aleg o valoare existentă dintr-o coloană. SUM și AVG calculați un număr nou din întreaga coloană.

Să presupunem că dorim suma totală a plăților efectuate până în prezent. MySQL USM funcţie returnează suma tuturor valorilor din coloana specificată. SUM funcționează numai pe câmpuri numerice și Valorile NULL sunt excluse din rezultat.

Următorul tabel prezintă datele din tabelul de plăți.

Interogarea de mai jos obține toate plățile efectuate și le însumează într-un singur rezultat: 2500 + 2000 + 6000 = 10500.

SELECT SUM(`amount_paid`) FROM `payments`;

Rezultat:

AVG funcţie

MySQL AVG funcţie returnează media valorilor dintr-o coloană specificată. La fel ca și funcția SUM, aceasta funcționează numai pe tipuri de date numerice.

Să presupunem că vrem să aflăm suma medie plătită. Putem folosi următoarea interogare, care împarte totalul de 10500 la cele trei rânduri de plată non-NULL.

SELECT AVG(`amount_paid`) FROM `payments`;

Rezultat:

⚠️ Atenție: AVG împarte la numărul de rânduri non-NULL, nu la numărul de rânduri din tabel. O valoare NULL este omisă în loc să fie considerată zero, ceea ce crește discret media. Folosește AVG(IFNULL(`sumă_plătită`, 0)) când o valoare lipsă înseamnă zero.

Exemplu practic: Combinarea funcțiilor de agregare cu GROUP BY

Fiecare funcție de mai sus a returnat o cifră pentru întregul tabel. Adăugarea unui A SE GRUPA CU clauza returnează o cifră pe grup în schimb — și așa se construiesc rapoartele reale.

Următorul exemplu grupează membrii după nume, apoi numără numărul total de plăți, suma medie a plății și totalul general al sumelor plăților pentru fiecare membru.

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.

Interogarea unește cele două tabele în clauza WHERE — stilul mai vechi de unire cu virgulă. Codul modern scrie aceeași logică ca o clauză explicită ÎNCHIDERE INTERIOARĂ … ACTIVATĂRețineți, de asemenea, că fiecare coloană neagregată din lista SELECT trebuie să apară în GROUP BY sau MySQL 5.7 și versiunile ulterioare resping interogarea din ONLY_FULL_GROUP_BY. Consultați oficial MySQL referință funcție agregată.

Întrebări frecvente