Geaggregeerde functies in MySQL

Het draait allemaal om geaggregeerde functies

  • Berekeningen uitvoeren op meerdere rijen
  • Van een enkele kolom van een tabel
  • En het retourneren van een enkele waarde.

De ISO-norm definieert vijf (5) aggregatiefuncties, namelijk;

1) AANTAL
2) SOM
3) AVG
4) MIN
5) MAX

Waarom aggregatiefuncties gebruiken

Vanuit zakelijk perspectief hebben verschillende organisatieniveaus verschillende informatiebehoeften. Managers op het hoogste niveau zijn meestal geïnteresseerd in kenniswing hele cijfers en niet noodzakelijk de individuele details.

Met aggregatiefuncties kunnen we eenvoudig samengevatte gegevens uit onze database produceren.

Vanuit onze myflix-database kan het beheer bijvoorbeeld follo vereisenwing meldt

  • Minst gehuurde films.
  • Meest gehuurde films.
  • Gemiddeld aantal keren dat elke film in een maand wordt verhuurd.

We produceren eenvoudig bovenstaande rapporten met behulp van aggregatiefuncties.

Laten we de geaggregeerde functies in detail bekijken.

AANTAL functie

De functie AANTAL retourneert het totale aantal waarden in het opgegeven veld. Het werkt op zowel numerieke als niet-numerieke gegevenstypen. Alle aggregatiefuncties sluiten standaard nulwaarden uit voordat aan de gegevens wordt gewerkt.

COUNT (*) is een speciale implementatie van de COUNT-functie die het aantal rijen in een opgegeven tabel retourneert. COUNT (*) houdt ook rekening met Nullen en duplicaten.

De onderstaande tabel toont de gegevens in de filmverhuurtabel

referentienummer transactie datum retourdatum lidmaatschapsnummer film_id film_ terug
11 20-06-2012 NULL 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 NULL 3 3 0

Stel dat we het aantal keren willen weten dat de film met id 2 is verhuurd

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

Voer de bovenstaande query uit in MySQL-werkbank tegen myflixdb geeft ons de volgende informatiewing resultaten.

COUNT('movie_id')
3

VERSCHILLEND trefwoord

VERSCHILLEND trefwoord

Het DISTINCT-trefwoord waarmee we duplicaten uit onze resultaten kunnen weglaten. Dit wordt bereikt door vergelijkbare waarden te groeperen.

Laten we een eenvoudige query uitvoeren om het concept van Distinct te begrijpen

SELECT `movie_id` FROM `movierentals`;

movie_id
1
2
2
2
3

Laten we nu dezelfde zoekopdracht uitvoeren met het afzonderlijke trefwoord:

SELECT DISTINCT `movie_id` FROM `movierentals`;

Zoals hieronder weergegeven, worden dubbele records weggelaten uit de resultaten.

movie_id
1
2
3

MIN-functie

De MIN-functie retourneert de kleinste waarde in het opgegeven tabelveld.

Laten we bijvoorbeeld aannemen dat we het jaar willen weten waarin de oudste film in onze bibliotheek is uitgebracht. We kunnen de MIN-functie van MySQL gebruiken om de gewenste informatie te verkrijgen.

De following query helpt ons dat te bereiken

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

Het uitvoeren van de bovenstaande query in MySQL workbench tegen myflixdb geeft ons het volgendewing resultaten.

MIN('year_released')
2005

MAX-functie

Zoals de naam al doet vermoeden, is de MAX-functie het tegenovergestelde van de MIN-functie. Het retourneert de grootste waarde uit het opgegeven tabelveld.

Laten we aannemen dat we het jaar willen hebben waarin de nieuwste film in onze database is uitgebracht. We kunnen eenvoudig de MAX-functie gebruiken om dat te bereiken.

De following voorbeeld retourneert het laatste uitgebrachte filmjaar.

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

Het uitvoeren van de bovenstaande query in MySQL workbench met behulp van myflixdb geeft ons het volgendewing resultaten.

MAX('year_released')
2012

SOM-functie

Stel dat we een rapport willen met het totale bedrag van de tot nu toe gedane betalingen. We kunnen de MySQL gebruiken SOM functie die retourneert de som van alle waarden in de opgegeven kolom. SOM werkt alleen op numerieke velden. Null-waarden worden uitgesloten van het geretourneerde resultaat.

De following tabel toont de gegevens in de betalingstabel-

betalings_id lidmaatschapsnummer betaaldatum beschrijving betaald bedrag extern_referentie_nummer
1 1 23-07-2012 Betaling van filmhuur 2500 11
2 1 25-07-2012 Betaling van filmhuur 2000 12
3 3 30-07-2012 Betaling van filmhuur 6000 NULL

De onderstaande zoekopdracht verzamelt alle gedane betalingen en somt deze op tot één resultaat.

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

Het uitvoeren van de bovenstaande query in MySQL workbench tegen de myflixdb geeft het volgendewing resultaten.

SUM('amount_paid')
10500

AVG functie

MySQL AVG functie retourneert het gemiddelde van de waarden in een opgegeven kolom. Net als de SUM-functie is het werkt alleen op numerieke gegevenstypen.

Stel dat we het gemiddelde betaalde bedrag willen vinden. We kunnen het volgende gebruikenwing vraag -

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

Het uitvoeren van de bovenstaande query in MySQL workbench geeft ons het volgendewing resultaten.

AVG('amount_paid')
3500

Denkspelletje

Je denkt dat aggregatiefuncties eenvoudig zijn. Probeer dit!

De following voorbeeld groepeert leden op naam, telt het totaal aantal betalingen, het gemiddelde betalingsbedrag en het eindtotaal van de betalingsbedragen.

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

Het uitvoeren van het bovenstaande voorbeeld in MySQL workbench geeft ons het volgendewing resultaten.

AVG functie

Samengevat

  • MySQL ondersteunt alle vijf (5) ISO-standaard aggregatiefuncties COUNT, SUM, AVG, MIN en MAX.
  • SOM en AVG functies werken alleen op numerieke gegevens.
  • Als u dubbele waarden wilt uitsluiten van de geaggregeerde functieresultaten, gebruikt u het trefwoord DISTINCT. Het trefwoord ALL bevat zelfs duplicaten. Als er niets is opgegeven, wordt ALL als standaard aangenomen.
  • Aggregaatfuncties kunnen worden gebruikt in combinatie met andere SQL-clausules, zoals GROEP DOOR