Bij geaggregeerde functies draait het allemaal om
- Berekeningen uitvoeren op meerdere rijen
- Van een enkele kolom van een tabel
- En een enkele waarde retourneren.
De ISO-norm definieert vijf (5) geaggregeerde functies, namelijk;
1) AANTAL
3) GEM
4) MIN
5) MAX
Waarom geaggregeerde functies gebruiken.
Vanuit een zakelijk perspectief hebben verschillende organisatieniveaus verschillende informatie-eisen. Topmanagers zijn meestal geïnteresseerd in het kennen van hele cijfers en niet per se de individuele details.
> Met geaggregeerde functies kunnen we eenvoudig samengevatte gegevens uit onze database produceren.
Vanuit onze myflix-database kan het management bijvoorbeeld de volgende rapporten vereisen
- Minst gehuurde films.
- De meeste gehuurde films.
- Gemiddeld aantal dat elke film in een maand verhuurt.
We produceren eenvoudig bovenstaande rapporten met behulp van geaggregeerde functies.
Laten we de geaggregeerde functies in detail bekijken.
COUNT functie
De COUNT-functie retourneert het totale aantal waarden in het opgegeven veld. Het werkt op zowel numerieke als niet-numerieke gegevenstypen. Alle aggregatiefuncties sluiten standaard null-waarden uit voordat ze aan de gegevens werken.
COUNT (*) is een speciale implementatie van de COUNT-functie die het aantal rijen in een opgegeven tabel retourneert. COUNT (*) houdt ook rekening met null en duplicaten.
De onderstaande tabel toont de gegevens in de tabel movierentals
| referentienummer | transactie datum | retourdatum | lidmaatschapsnummer | movie_id | movie_ is teruggekeerd |
|---|---|---|---|---|---|
| 11 | 20-06-2012 | NUL | 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 | NUL | 3 | 3 | 0 |
Stel dat we willen weten hoe vaak de film met id 2 is verhuurd
SELECT COUNT(`movie_id`) FROM `movierentals` WHERE `movie_id` = 2;
Het uitvoeren van de bovenstaande query in MySQL-workbench tegen myflixdb geeft ons de volgende resultaten.
| COUNT('movie_id') |
|---|
| 3 |
DISTINCT trefwoord
Het trefwoord DISTINCT waarmee we duplicaten uit onze resultaten kunnen weglaten. Dit wordt bereikt door vergelijkbare waarden samen te groeperen.
Laten we een eenvoudige query uitvoeren om het concept van Distinct te waarderen
SELECT `movie_id` FROM `movierentals`;
| movie_id |
|---|
| 1 |
| 2 |
| 2 |
| 2 |
| 3 |
Laten we nu dezelfde zoekopdracht uitvoeren met het distinctieve sleutelwoord -
SELECT DISTINCT `movie_id` FROM `movierentals`;
Zoals hieronder wordt getoond, laat distinct dubbele records weg uit de resultaten.
| movie_id |
|---|
| 1 |
| 2 |
| 3 |
MIN- functie
De MIN-functie retourneert de kleinste waarde in het opgegeven tabelveld .
Laten we als voorbeeld aannemen dat we willen weten in welk jaar de oudste film in onze bibliotheek is uitgebracht, dan kunnen we de MIN-functie van MySQL gebruiken om de gewenste informatie te krijgen.
De volgende vraag helpt ons daarbij
SELECT MIN(`year_released`) FROM `movies`;
Het uitvoeren van de bovenstaande query in MySQL-workbench tegen myflixdb geeft ons de volgende resultaten.
| MIN('year_released') |
|---|
| 2005 |
MAX-functie
Zoals de naam suggereert, 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 krijgen waarin de nieuwste film in onze database is uitgebracht. Daarvoor kunnen we eenvoudig de MAX-functie gebruiken.
In het volgende voorbeeld wordt het laatst uitgebrachte filmjaar geretourneerd.
SELECT MAX(`year_released`) FROM `movies`;
Het uitvoeren van de bovenstaande query in MySQL-workbench met behulp van myflixdb geeft ons de volgende resultaten.
| MAX('year_released') |
|---|
| 2012 |
SOM- functie
Stel dat we een rapport willen met het totale aantal betalingen dat tot dusver is gedaan. We kunnen de MySQL SUM- functie gebruiken die de som van alle waarden in de opgegeven kolom retourneert . SUM werkt alleen op numerieke velden . Null-waarden worden uitgesloten van het geretourneerde resultaat.
De volgende tabel toont de gegevens in de betalingstabel-
| betalings-id | lidmaatschapsnummer | betaaldatum | Omschrijving | 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 | NUL |
De onderstaande vraag haalt alle uitgevoerde betalingen op en somt ze op om een enkel resultaat te retourneren.
SELECT SUM(`amount_paid`) FROM `payments`;
Het uitvoeren van de bovenstaande query in MySQL-workbench tegen de myflixdb geeft de volgende resultaten.
| SUM('amount_paid') |
|---|
| 10500 |
AVG- functie
MySQL AVG-functie retourneert het gemiddelde van de waarden in een opgegeven kolom . Net als de functie SOM, werkt deze alleen op numerieke gegevenstypen .
Stel dat we het gemiddeld betaalde bedrag willen vinden. We kunnen de volgende vraag gebruiken -
SELECT AVG(`amount_paid`) FROM `payments`;
Het uitvoeren van de bovenstaande query in MySQL-workbench geeft ons de volgende resultaten.
| AVG('amount_paid') |
|---|
| 3500 |
Overzicht
- MySQL ondersteunt alle vijf (5) ISO-standaard geaggregeerde functies COUNT, SUM, AVG, MIN en MAX.
- SUM- en AVG-functies werken alleen op numerieke gegevens.
- Als u dubbele waarden wilt uitsluiten van de resultaten van de aggregatiefunctie, gebruikt u het trefwoord DISTINCT. Het ALL-sleutelwoord bevat zelfs duplicaten. Als er niets is opgegeven, wordt de ALL als standaard aangenomen.
- Aggregatiefuncties kunnen worden gebruikt in combinatie met andere SQL-clausules, zoals GROUP BY
Denkspelletje
U denkt dat geaggregeerde functies eenvoudig zijn. Probeer dit!
Het volgende voorbeeld groepeert leden op naam, telt het totale aantal betalingen, het gemiddelde betalingsbedrag en het totaal 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 de volgende resultaten.
