MySQL Koondfunktsioonid: SUM, COUNT, AVG & MAX

Mis on koondfunktsioonid MySQL?

An koondfunktsioon loeb ühe veeru mitu rida ja koondab need üheks väärtuseks. Koondfunktsioonide eesmärk on:

• Arvutuste tegemine mitmel real
• Tabeli ühest veerust
• Ja ühe väärtuse tagastamine.

ISO standard defineerib viis (5) koondfunktsiooni, nimelt:

1. COUNT
2. SUM
3. AVG
4. MIN
5. MAX

Kõigi viie puhul kehtib üks reegel: koondfunktsioonid ignoreerivad NULL-väärtusi. COUNT(*) on ainus erand ja me vaatame allpool, miks.

Miks kasutada koondfunktsioone

Erinevatel organisatsioonitasanditel on erinevad infovajadused. Tippjuhid on tavaliselt huvitatud tervikarvudest, mitte üksikutest detailidest.

Koondfunktsioonid võimaldavad meil hõlpsasti koostada meie andmebaasist kokkuvõtlikke andmeid.

Näiteks võib juhtkond meie myflixi andmebaasist vajada järgmisi aruandeid:

• Kõige vähem laenutatud filme.
• Enamik laenutatud filme.
• Keskmine arv kordi, kui iga filmi kuus laenutatakse.

Kõik ülaltoodud aruanded pärinevad koondfunktsioonidest. Vaatleme igaüht neist lähemalt.

COUNT funktsioon

Funktsioon COUNT tagastab määratud väljal olevate väärtuste koguarvu nii numbriliste kui ka mittenumbriliste andmetüüpide korral. Nagu iga koondfunktsioon, välistab ka COUNT(veerg) NULL-väärtused.

COUNT(*) on erivorm, mis tagastab tabeli kõigi ridade arvu. See loendab ka NULL-id ja duplikaate, sest see loendab ridu, mitte väärtusi.

Movierentals tabel sisaldab järgmisi andmeid:

Oletame, et tahame teada, mitu korda on filmi ID-ga 2 välja laenutatud.

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

Selle elluviimine MySQL Workbench myflixdb vastu tagastab väärtuse 3, sest kolmel real on movie_id 2.

DISTINCT Märksõna

COUNT vastab küsimusele „kui palju“. Järgmine küsimus on tavaliselt „kui palju erinev „ones” ja selleks ongi DISTINCT loodud.

Märksõna DISTINCT jätab meie tulemustest välja duplikaadid grupi järgi.ping identsed väärtused koos, täpselt nagu ülaltoodud illustratsioon näitab.

Esmalt teostame lihtsa päringu.

SELECT `movie_id` FROM `movierentals`;

Nüüd sama päring märksõnaga DISTINCT:

SELECT DISTINCT `movie_id` FROM `movierentals`;

DISTINCT jätab duplikaatkirjed välja:

COUNT vs COUNT(*) vs COUNT(DISTINCT): Kumba neist peaksite kasutama?

DISTINCT saab paigutada ka sees koondfunktsioon ja just siin kaotab enamik algajaid tracmillest k ridu tegelikult loendatakse. Kõik neli allolevat vormi töötavad sama viie reaga movierentals tabeli alusel, mida varem näidati, kuid need kõik ei tagasta sama arvu. Erinevus taandub kahele küsimusele: kas vorm loendab ridu või väärtusi ja kas see säilitab duplikaate?

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

💡 Näpunäide: Reaarvu jaoks kasutage funktsiooni COUNT(*), NULL-i puhul COUNT(veerg) ja unikaalsete väärtuste jaoks funktsiooni COUNT(DISTINCT veerg). Funktsiooni DISTINCT vastand on ALL – vaikeväärtus, mida seetõttu harva välja kirjutatakse.

MIN funktsioon

Funktsioon MIN tagastab määratud tabelivälja väikseima väärtuse.

Oletame, et tahame teada aastat, mil ilmus meie raamatukogu vanim film. MySQLSelle annab meile funktsioon MIN.

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

Tulemus:

MAX funktsioon

Nagu nimigi ütleb, on MAX funktsioon MIN-funktsiooni vastand. See tagastab määratud tabelivälja suurima väärtuse.

Oletame, et tahame teada aastat, millal ilmus meie andmebaasis olev uusim film. Järgmine näide tagastab selle.

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

Tulemus:

SUM-funktsioon

MIN ja MAX valivad veerust olemasoleva väärtuse. SUM ja AVG Arvuta kogu veeru põhjal uus arv.

Oletame, et tahame teada seni tehtud maksete kogusummat. MySQL SUM funktsioon tagastab kõigi määratud veeru väärtuste summa. SUM töötab ainult numbriväljadelja NULL-väärtused jäetakse tulemusest välja.

Järgmises tabelis on näidatud maksete tabeli andmed.

Allolev päring saab kõik tehtud maksed ja summeerib need üheks tulemuseks: 2500 + 2000 + 6000 = 10500.

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

Tulemus:

AVG funktsioon

. MySQL AVG funktsioon tagastab määratud veeru väärtuste keskmise. Nii nagu funktsioon SUM, see töötab ainult numbriliste andmetüüpidega.

Oletame, et tahame leida keskmise makstud summa. Selleks võime kasutada järgmist päringut, mis jagab summa 10500 kolme mitte-NULL maksereaga.

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

Tulemus:

⚠️ Hoiatus: AVG jagab mitte-NULL ridade arvuga, mitte tabeli ridade arvuga. NULL-väärtus jäetakse vahele, mitte ei loeta seda nulliks, mis tõstab keskmist vaikselt üles. Kasutage AVG(IFNULL(`amou_paid`, 0)), kui puuduv väärtus tähendab nulli.

Praktiline näide: koondfunktsioonide kombineerimine funktsiooniga GROUP BY

Iga ülaltoodud funktsioon tagastas ühe arvu kogu tabeli kohta. A lisamine GROUP BY lause tagastab ühe numbri rühma kohta selle asemel – ja just nii luuaksegi päris aruandeid.

Järgmises näites rühmitatakse liikmed nime järgi ning seejärel loendatakse iga liikme maksete koguarv, keskmine maksesumma ja maksete kogusumma.

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

Ülaltoodud näite täitmine sisse MySQL Workbench annab meile järgmised tulemused.

Päring ühendab kaks tabelit WHERE-klauslis – vanemas komaliitmisstiilis. Tänapäeva kood kirjutab sama loogikaga kui selgesõnaline SISEMINE LIITUMINE … SEESPane tähele ka seda, et iga SELECT-loendi mittekoondatud veerg peab ilmuma ka GROUP BY-s või MySQL 5.7 ja hilisemad versioonid lükkavad päringu tagasi ONLY_FULL_GROUP_BY alusel. Vaadake ametlik MySQL koondfunktsiooni viide.

KKK