MySQL Agregacijske funkcije: SUM, COUNT, AVG & MAX

Što su agregacijske funkcije u MySQL?

An agregatna funkcija čita više redaka jednog stupca i sažima ih u jednu vrijednost. Agregatne funkcije su:

• Izvršavanje izračuna na više redaka
• Od jednog stupca tablice
• I vraćanje jedne vrijednosti.

ISO standard definira pet (5) agregatnih funkcija, i to:

1. TOČKA
2. IZNOS
3. AVG
4. MIN
5. MAX

Jedno pravilo vrijedi za svih pet: Agregatne funkcije ignoriraju NULL vrijednosti. COUNT(*) je jedina iznimka, a u nastavku ćemo pogledati zašto.

Zašto koristiti agregatne funkcije

Različite razine organizacije imaju različite zahtjeve za informacijama. Menadžeri na najvišoj razini obično su zainteresirani za cijele brojke, a ne za pojedinačne detalje.

Skupne funkcije omogućuju nam jednostavnu izradu sažetih podataka iz naše baze podataka.

Na primjer, iz naše myflix baze podataka, uprava može zahtijevati sljedeća izvješća:

• Najmanje iznajmljivani filmovi.
• Većina iznajmljivanih filmova.
• Prosječan broj iznajmljivanja svakog filma u mjesecu.

Sva gore navedena izvješća dolaze iz agregatnih funkcija. Pogledajmo svaku od njih detaljnije.

COUNT funkcija

Funkcija COUNT vraća ukupan broj vrijednosti u navedenom polju, i za numeričke i za nenumeričke tipove podataka. Kao i svaka agregacijska funkcija, COUNT(stupac) isključuje NULL vrijednosti.

COUNT(*) je poseban oblik koji vraća broj svih redaka u tablici. Također broji NULL-ovi i duplikate, jer broji retke, a ne vrijednosti.

Tablica movietrenals sadrži ove podatke:

Pretpostavimo da želimo dobiti koliko je puta film s ID-jem 2 bio iznajmljen.

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

Izvršavanje ovoga u MySQL Radna tezga u odnosu na myflixdb vraća 3, jer tri reda nose movie_id 2.

DISTINCT Ključna riječ

COUNT odgovara na „koliko“. Sljedeće pitanje je obično „koliko drukčiji one”, i za to služi DISTINCT.

Ključna riječ DISTINCT izostavlja duplikate iz naših rezultata po grupamaping identične vrijednosti zajedno, točno kao što gornja ilustracija sugerira.

Prvo, izvršimo jednostavan upit.

SELECT `movie_id` FROM `movierentals`;

Sada isti upit s ključnom riječi DISTINCT:

SELECT DISTINCT `movie_id` FROM `movierentals`;

DISTINCT izostavlja duplicirane zapise:

COUNT vs. COUNT(*) vs. COUNT(DISTINCT): Koji biste trebali koristiti?

DISTINCT se također može postaviti u agregatna funkcija, i tu većina početnika gubi track od kojih se redova zapravo broji. Četiri obrasca u nastavku sva se pokreću prema istoj tablici movietrenals s pet redaka prikazanoj ranije, no ne vraćaju svi isti broj. Razlika se svodi na dva pitanja: broji li obrazac retke ili vrijednosti i čuva li duplikate?

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

💡 Savjet: Koristite COUNT(*) za brojanje redaka, COUNT(stupac) kada NULL treba značiti „ne primjenjuje se“ i COUNT(DISTINCT stupac) za jedinstvene vrijednosti. Suprotno od DISTINCT je ALL - zadano i stoga se rijetko piše.

MIN funkcija

Funkcija MIN vraća najmanju vrijednost u navedenom polju tablice.

Pretpostavimo da želimo godinu u kojoj je izašao najstariji film u našoj biblioteci. MySQLTo nam daje funkcija MIN od .

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

Rezultat:

MAX funkcija

Kao što naziv sugerira, funkcija MAX suprotna je funkciji MIN. To vraća najveću vrijednost iz navedenog polja tablice.

Pretpostavimo da želimo godinu u kojoj je izašao najnoviji film u našoj bazi podataka. Sljedeći primjer je vraća.

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

Rezultat:

SUM funkcija

MIN i MAX odabiru postojeću vrijednost iz stupca. SUM i AVG izračunajte novi broj iz cijelog stupca.

Pretpostavimo da želimo ukupan iznos do sada izvršenih plaćanja. MySQL IZNOS funkcija vraća zbroj svih vrijednosti u navedenom stupcu. SUM radi samo na numeričkim poljimai NULL vrijednosti su isključene iz rezultata.

Sljedeća tablica prikazuje podatke u tablici plaćanja.

Upit prikazan u nastavku dobiva sve izvršene uplate i zbraja ih u jedan rezultat: 2500 + 2000 + 6000 = 10500.

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

Rezultat:

AVG funkcija

The MySQL AVG funkcija vraća prosjek vrijednosti u određenom stupcu. Baš kao i funkcija SUM, to radi samo na numeričkim tipovima podataka.

Pretpostavimo da želimo pronaći prosječan plaćeni iznos. Možemo koristiti sljedeći upit koji dijeli ukupan iznos od 10500 s tri retka plaćanja koja nisu NULL.

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

Rezultat:

⚠️ Upozorenje: AVG dijeli se s brojem redaka koji nisu NULL, a ne s brojem redaka u tablici. Iznos NULL se preskače umjesto da se broji kao nula, što tiho povećava prosjek. Koristite AVG(IFNULL(`plaćeni_iznos`, 0)) kada nedostajuća vrijednost znači nulu.

Praktični primjer: Kombiniranje agregacijskih funkcija s GROUP BY

Svaka gornja funkcija vratila je jednu sliku za cijelu tablicu. Dodavanjem GROUP BY klauzula vraća jednu znamenku po grupi umjesto toga - i tako se grade prava izvješća.

Sljedeći primjer grupira članove po imenu, zatim broji ukupan broj plaćanja, prosječan iznos plaćanja i ukupan zbroj iznosa plaćanja za svakog člana.

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`;

Izvođenje gornjeg primjera u MySQL Workbench nam daje sljedeće rezultate.

Upit spaja dvije tablice u WHERE klauzuli - starijem stilu spajanja zarezom. Moderni kod piše istu logiku kao eksplicitna UNUTARNJE SPAJANJE … UKLJUČENOTakođer imajte na umu da se svaki neagregirani stupac na SELECT listi mora pojaviti u GROUP BY ili MySQL 5.7 i novije verzije odbijaju upit pod ONLY_FULL_GROUP_BY. Pogledajte službenik MySQL referenca agregatne funkcije.

Pitanja i odgovori