MySQL Koostefunktiot: SUMMA, LASKU, AVG & MAKSIMI

Mitä ovat aggregaattifunktiot MySQL?

An aggregaattitoiminto lukee yhden sarakkeen useita rivejä ja kutistaa ne yhdeksi arvoksi. Kokonaisfunktiot tarkoittavat:

• Laskelmien suorittaminen useilla riveillä
• Taulukon yhdestä sarakkeesta
• Ja palauttaa yhden arvon.

ISO-standardi määrittelee viisi (5) aggregaattifunktiota, nimittäin:

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

Yksi sääntö pätee kaikkiin viiteen: koostefunktiot jättävät huomiotta NULL-arvot. COUNT(*) on ainoa poikkeus, ja tarkastelemme miksi alla.

Miksi käyttää aggregaattifunktioita

Eri organisaatiotasoilla on erilaiset tiedontarpeet. Ylin johto on yleensä kiinnostunut kokonaisluvuista, ei yksittäisistä yksityiskohdista.

Aggregaattitoimintojen avulla voimme helposti tuottaa yhteenvetotietoja tietokannastamme.

Esimerkiksi myflix-tietokannastamme johto voi tarvita seuraavia raportteja:

• Vähiten vuokratut elokuvat.
• Useimmat vuokratut elokuvat.
• Elokuvan vuokrauskertojen keskimääräinen määrä kuukaudessa.

Kaikki yllä olevat raportit ovat peräisin koostefunktioista. Tarkastellaan kutakin yksityiskohtaisesti.

COUNT-toiminto

LASKU-funktio palauttaa määritetyn kentän arvojen kokonaismäärän sekä numeerisissa että ei-numeerisissa tietotyypeissä. Kuten kaikki koostefunktiot, COUNT(sarake) jättää pois NULL-arvot.

COUNT(*) on erikoismuoto, joka palauttaa taulukon kaikkien rivien lukumäärän. Se laskee myös NULLeja ja kaksoiskappaleita, koska se laskee rivejä arvojen sijaan.

Movierentals-taulukko sisältää seuraavat tiedot:

Oletetaan, että haluamme selvittää, kuinka monta kertaa elokuva, jonka id on 2, on vuokrattu.

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

Tämän toteuttaminen MySQL Työpöytä myflixdb-funktio palauttaa arvon 3, koska kolmella rivillä on movie_id 2.

DISTINCT Avainsana

COUNT vastaa ”kuinka monta”. Seuraava kysymys on yleensä ”kuinka monta” eri ykkösiä”, ja sitä varten DISTINCT on olemassa.

DISTINCT-avainsana jättää pois kaksoiskappaleet tuloksistamme ryhmän mukaan.ping identtiset arvot yhdessä, täsmälleen kuten yllä oleva kuva antaa ymmärtää.

Suoritetaan ensin yksinkertainen kysely.

SELECT `movie_id` FROM `movierentals`;

Nyt sama kysely DISTINCT-avainsanalla:

SELECT DISTINCT `movie_id` FROM `movierentals`;

DISTINCT jättää pois kaksoiskappaleet:

COUNT vs. COUNT(*) vs. COUNT(DISTINCT): Kumpaa kannattaa käyttää?

DISTINCT voidaan sijoittaa myös sisällä koostefunktio, ja tässä useimmat aloittelijat häviävät tracjoista k riviä itse asiassa lasketaan. Kaikki neljä alla olevaa lomaketta suorittavat saman aiemmin esitetyn viisirivisen movierentals-taulukon, mutta ne eivät kaikki palauta samaa lukua. Ero tiivistyy kahteen kysymykseen: laskeeko lomake rivejä vai arvoja ja säilyttääkö se kaksoiskappaleet?

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

💡 Vinkki: Käytä COUNT(*)-funktiota rivien lukumäärän laskemiseen, COUNT(sarake)-funktiota, kun NULL-arvon tulisi tarkoittaa "ei sovelleta", ja COUNT(DISTINCT-sarake)-funktiota yksilöllisten arvojen laskemiseen. DISTINCT-funktion vastakohta on ALL — oletusarvo, jota siksi harvoin kirjoitetaan.

MIN-toiminto

MIN-toiminto palauttaa pienimmän arvon määritetyssä taulukkokentässä.

Oletetaan, että haluamme vuoden, jolloin kirjastomme vanhin elokuva julkaistiin. MySQLn MIN-funktio antaa meille sen.

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

Tulos:

MAX-toiminto

Kuten nimestä voi päätellä, MAX-toiminto on MIN-funktion vastakohta. Se palauttaa suurimman arvon määritetystä taulukkokentästä.

Oletetaan, että haluamme vuoden, jolloin tietokannassamme oleva uusin elokuva julkaistiin. Seuraava esimerkki palauttaa sen.

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

Tulos:

SUM-toiminto

MIN ja MAX valitsevat olemassa olevan arvon sarakkeesta. SUMMA ja AVG Laske uusi luku koko sarakkeesta.

Oletetaan, että haluamme tähän mennessä suoritettujen maksujen kokonaismäärän. MySQL SUMMA toiminto palauttaa kaikkien määritetyn sarakkeen arvojen summan. SUM toimii vain numeerisissa kentissäja NULL-arvot jätetään pois tuloksesta.

Seuraavassa taulukossa näkyvät maksutaulukon tiedot.

Alla oleva kysely hakee kaikki suoritetut maksut ja summaa ne yhdeksi tulokseksi: 2500 + 2000 + 6000 = 10500.

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

Tulos:

AVG toiminto

MySQL AVG toiminto palauttaa määritetyn sarakkeen arvojen keskiarvon. Aivan kuten SUM-funktio, se toimii vain numeerisilla tietotyypeillä.

Oletetaan, että haluamme selvittää keskimääräisen maksetun summan. Voimme käyttää seuraavaa kyselyä, joka jakaa kokonaissumman 10500 kolmella ei-NULL-maksurivillä.

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

Tulos:

⚠️ Varoitus: AVG jakaa ei-NULL-rivien lukumäärällä, ei taulukon rivien lukumäärällä. NULL-määrä ohitetaan nollan sijaan, mikä nostaa keskiarvoa hiljaa ylöspäin. Käytä AVG(IFNULL(`amount_paid`, 0)), kun puuttuva arvo tarkoittaa nollaa.

Käytännön esimerkki: Koostefunktioiden yhdistäminen GROUP BY -funktion kanssa

Jokainen yllä oleva funktio palautti yhden luvun koko taulukosta. Lisäämällä GROUP BY lauseke palauttaa yhden luvun ryhmää kohden sen sijaan – ja näin oikeat raportit rakennetaan.

Seuraavassa esimerkissä jäsenet ryhmitellään nimen mukaan ja lasketaan sitten kunkin jäsenen maksujen kokonaismäärä, keskimääräinen maksusumma ja maksujen kokonaissumma.

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

Suorita yllä oleva esimerkki sisään MySQL Workbench antaa meille seuraavat tulokset.

Kysely yhdistää kaksi taulukkoa WHERE-lausekkeessa — vanhemmassa pilkkuliitostyylissä. Nykyaikainen koodi kirjoittaa saman logiikan kuin eksplisiittinen SISÄINEN LIITTYMINEN … PÄÄLLÄHuomaa myös, että jokaisen SELECT-luettelon ei-aggregoidun sarakkeen on oltava GROUP BY -luettelossa. MySQL 5.7 ja uudemmat hylkäävät kyselyn ONLY_FULL_GROUP_BY-kohdassa. Katso virallinen MySQL koostefunktion viite.

UKK