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 een zakelijk perspectief hebben verschillende organisatieniveaus verschillende informatievereisten. Topmanagers zijn doorgaans geïnteresseerd in het kennen van hele getallen en niet noodzakelijkerwijs in de individuele details.

Met geaggregeerde functies kunnen we eenvoudig samengevatte gegevens uit onze database produceren.

Het management kan bijvoorbeeld de volgende rapporten nodig hebben uit onze MyFlix-database:

• 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 resultaten.

COUNT('movie_id')



3

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 willen weten in welk jaar de oudste film in onze bibliotheek is uitgebracht. We kunnen dit gebruiken MySQL's MIN-functie om de gewenste informatie te verkrijgen.

De volgende query helpt ons dat te bereiken

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

Voer de bovenstaande query uit in MySQL workbench tegen myflixdb geeft ons de volgende 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.

Het volgende voorbeeld retourneert het laatste filmjaar waarin de film is uitgebracht.

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

Voer de bovenstaande query uit 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 bedrag van de tot nu toe gedane betalingen. Wij kunnen gebruik maken van de MySQL 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 volgende 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`;

Voer de bovenstaande query uit in MySQL workbench tegen 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 SUM-functie is het werkt alleen op numerieke gegevenstypen.

Stel dat we het gemiddelde betaalde bedrag willen vinden. We kunnen de volgende query gebruiken:

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

Voer de bovenstaande query uit in MySQL workbench, geeft ons de volgende resultaten.

AVG('amount_paid')



3500

Denkspelletje

Je denkt dat aggregatiefuncties eenvoudig zijn. Probeer dit!

In het volgende voorbeeld worden leden gegroepeerd op naam, wordt het totale aantal betalingen, het gemiddelde betalingsbedrag en het totaalbedrag van de betalingen geteld.

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

Voer het bovenstaande voorbeeld uit in MySQL workbench geeft ons de volgende resultaten.

Samenvatting

• MySQL ondersteunt alle vijf (5) ISO-standaard aggregatiefuncties COUNT, SOM, 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