MySQL Aggregeringsfunktioner: SUM, COUNT, AVG & MAKS

Hvad er aggregatfunktioner i MySQL?

An samlet funktion læser mange rækker i en enkelt kolonne og samler dem til én værdi. Aggregeringsfunktioner handler om:

• Udførelse af beregninger på flere rækker
• Af en enkelt kolonne i en tabel
• Og returnere en enkelt værdi.

ISO-standarden definerer fem (5) aggregerede funktioner, nemlig:

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

Én regel gælder for alle fem: aggregeringsfunktioner ignorerer NULL-værdierCOUNT(*) er den eneste undtagelse, og vi ser på hvorfor nedenfor.

Hvorfor bruge aggregerede funktioner

Forskellige organisationsniveauer har forskellige informationskrav. Topledere er normalt interesserede i hele tal, ikke individuelle detaljer.

Aggregerede funktioner giver os mulighed for nemt at producere opsummerede data fra vores database.

For eksempel kan ledelsen kræve følgende rapporter fra vores myflix-database:

• Mindst lejede film.
• De fleste lejede film.
• Gennemsnitligt antal gange, en film udlejes på en måned.

Alle ovenstående rapporter kommer fra aggregerede funktioner. Lad os se nærmere på hver enkelt.

COUNT funktion

Funktionen COUNT returnerer det samlede antal værdier i det angivne felt, både på numeriske og ikke-numeriske datatyper. Ligesom alle aggregeringsfunktioner udelukker COUNT(kolonne) NULL-værdier.

COUNT(*) er en specialformular, der returnerer antallet af alle rækker i en tabel. Den tæller også NULL og dubletter, fordi den tæller rækker i stedet for værdier.

Tabellen over filmudlejning indeholder disse data:

Lad os antage, at vi vil have det antal gange, filmen med id 2 er blevet udlejet.

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

Udfører dette i MySQL Workbench mod myflixdb returnerer 3, fordi tre rækker indeholder movie_id 2.

DISTINKT søgeord

COUNT svarer på "hvor mange". Det næste spørgsmål er normalt "hvor mange forskellige "enere", og det er dét DISTINCT er til for.

Nøgleordet DISTINCT udelader dubletter fra vores resultater efter gruppeping identiske værdier sammen, præcis som illustrationen ovenfor antyder.

Lad os først udføre en simpel forespørgsel.

SELECT `movie_id` FROM `movierentals`;

Nu den samme forespørgsel med DISTINCT-nøgleordet:

SELECT DISTINCT `movie_id` FROM `movierentals`;

DISTINCT udelader de dublerede poster:

COUNT vs. COUNT(*) vs. COUNT(DISTINCT): Hvilken skal du bruge?

DISTINCT kan også placeres indvendig en aggregeret funktion, og det er her, de fleste begyndere mister track, hvoraf rækker rent faktisk tælles. De fire formularer nedenfor kører alle mod den samme fem-rækkede movierentals-tabel, der er vist tidligere, men de returnerer ikke alle det samme tal. Forskellen kommer ned til to spørgsmål: tæller formularen rækker eller værdier, og gemmer den dubletter?

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

💡 Tip: Brug COUNT(*) for et rækkeantal, COUNT(kolonne) når et NULL-tal skal betyde "gælder ikke", og COUNT(DISTINCT kolonne) for unikke værdier. Det modsatte af DISTINCT er ALL — standardværdien og derfor sjældent skrevet ud.

MIN funktion

MIN-funktionen returnerer den mindste værdi i det angivne tabelfelt.

Antag, at vi ønsker året, hvor den ældste film i vores bibliotek blev udgivet. MySQL's MIN-funktion giver os det.

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

Resultat:

MAX funktion

Ligesom navnet antyder, er MAX-funktionen det modsatte af MIN-funktionen. Det returnerer den største værdi fra det angivne tabelfelt.

Antag, at vi ønsker året, hvor den seneste film i vores database blev udgivet. Følgende eksempel returnerer det.

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

Resultat:

SUM funktion

MIN og MAX vælger en eksisterende værdi fra en kolonne. SUM og AVG Beregn et nyt tal ud fra hele kolonnen.

Antag, at vi ønsker det samlede beløb af betalinger foretaget indtil videre. MySQL SUM funktion returnerer summen af ​​alle værdier i den angivne kolonne. SUM virker kun på numeriske felterog NULL-værdier er udeladt af resultatet.

Følgende tabel viser dataene i betalingstabellen.

Forespørgslen nedenfor henter alle de foretagne betalinger og summerer dem til ét resultat: 2500 + 2000 + 6000 = 10500.

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

Resultat:

AVG funktion

MySQL AVG funktion returnerer gennemsnittet af værdierne i en specificeret kolonne. Ligesom SUM-funktionen er det virker kun på numeriske datatyper.

Antag, at vi vil finde det gennemsnitlige betalte beløb. Vi kan bruge følgende forespørgsel, som dividerer summen af ​​10500 med de tre ikke-NULL-betalingsrækker.

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

Resultat:

⚠️ Advarsel: AVG dividerer med antallet af rækker, der ikke er NULL, ikke med tabellens rækkeantal. Et NULL-beløb springes over i stedet for at tælle som nul, hvilket stille og roligt skubber gennemsnittet op. Brug AVG(IFNULL(`betalt_beløb`, 0)) når en manglende værdi betyder nul.

Praktisk eksempel: Kombination af aggregatfunktioner med GROUP BY

Hver funktion ovenfor returnerede ét tal for hele tabellen. Tilføjelse af en GROUP BY klausul returnerer ét ciffer per gruppe i stedet – og det er sådan rigtige rapporter opbygges.

Følgende eksempel grupperer medlemmer efter navn og tæller derefter det samlede antal betalinger, det gennemsnitlige betalingsbeløb og den samlede sum af betalingsbeløb for hvert medlem.

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

Udførelse af ovenstående eksempel i MySQL Workbench giver os følgende resultater.

Forespørgslen forbinder de to tabeller i WHERE-klausulen — den ældre komma-join-stil. Moderne kode skriver den samme logik som en eksplicit INDRE SAMMENSLUTNING … TILBemærk også, at alle ikke-aggregerede kolonner i SELECT-listen skal vises i GROUP BY, eller MySQL 5.7 og senere afvis forespørgslen under ONLY_FULL_GROUP_BY. Se officiel MySQL reference til samlet funktion.

Ofte Stillede Spørgsmål