MySQL Aggregerte funksjoner: SUM, COUNT, AVG & MAX

Hva er aggregatfunksjoner i MySQL?

An aggregert funksjon leser mange rader i en enkelt kolonne og samler dem til én verdi. Aggregerte funksjoner handler om:

• Utføre beregninger på flere rader
• Av en enkelt kolonne i en tabell
• Og returnerer én enkelt verdi.

ISO-standarden definerer fem (5) aggregerte funksjoner, nemlig:

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

Én regel gjelder for alle fem: aggregatfunksjoner ignorerer NULL-verdierCOUNT(*) er det eneste unntaket, og vi ser på hvorfor nedenfor.

Hvorfor bruke aggregerte funksjoner

Ulike organisasjonsnivåer har ulike informasjonskrav. Toppledere er vanligvis interessert i hele tall, ikke individuelle detaljer.

Aggregerte funksjoner lar oss enkelt produsere oppsummerte data fra databasen vår.

For eksempel kan ledelsen kreve følgende rapporter fra myflix-databasen vår:

• Minst leide filmer.
• De fleste leide filmer.
• Gjennomsnittlig antall ganger hver film leies ut i løpet av en måned.

Alle rapportene ovenfor kommer fra aggregerte funksjoner. La oss se nærmere på hver av dem.

COUNT funksjon

COUNT-funksjonen returnerer det totale antallet verdier i det angitte feltet, både på numeriske og ikke-numeriske datatyper. Som alle aggregerte funksjoner ekskluderer COUNT(kolonne) NULL-verdier.

COUNT(*) er en spesialform som returnerer antallet rader i en tabell. Den teller også NULL og duplikater, fordi den teller rader i stedet for verdier.

Tabellen for filmutleie inneholder disse dataene:

La oss anta at vi ønsker å få antall ganger filmen med ID 2 har blitt leid ut.

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

Utfører dette i MySQL Workbench mot myflixdb returnerer 3, fordi tre rader har movie_id 2.

DISTINKT nøkkelord

ANTALL svarer på «hvor mange». Det neste spørsmålet er vanligvis «hvor mange». forskjellig «og det er det DISTINCT er til for.

Nøkkelordet DISTINCT utelater duplikater fra resultatene våre etter gruppeping identiske verdier sammen, akkurat som illustrasjonen ovenfor antyder.

La oss først utføre en enkel spørring.

SELECT `movie_id` FROM `movierentals`;

Nå samme spørring med DISTINCT-nøkkelordet:

SELECT DISTINCT `movie_id` FROM `movierentals`;

DISTINCT utelater duplikatpostene:

COUNT vs. COUNT(*) vs. COUNT(DISTINCT): Hvilken bør du bruke?

DISTINCT kan også plasseres innsiden en aggregert funksjon, og det er her de fleste nybegynnere taper track av hvilke rader faktisk telles. De fire skjemaene nedenfor kjører alle mot den samme femrads-tabellen for filmutleie som ble vist tidligere, men de returnerer ikke alle det samme tallet. Forskjellen kommer ned til to spørsmål: teller skjemaet rader eller verdier, og beholder det duplikater?

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

💡 Tips: Bruk COUNT(*) for et radantall, COUNT(kolonne) når en NULL skal bety «gjelder ikke», og COUNT(DISTINCT kolonne) for unike verdier. Det motsatte av DISTINCT er ALL – standardverdien, og derfor sjelden skrevet ut.

MIN funksjon

MIN-funksjonen returnerer den minste verdien i det angitte tabellfeltet.

Anta at vi vil ha året den eldste filmen i biblioteket vårt ble utgitt. MySQLMIN-funksjonen gir oss det.

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

Resultat:

MAX-funksjon

Akkurat som navnet antyder, er MAX-funksjonen det motsatte av MIN-funksjonen. Den returnerer den største verdien fra det angitte tabellfeltet.

Anta at vi ønsker året den nyeste filmen i databasen vår ble utgitt. Følgende eksempel returnerer det.

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

Resultat:

SUM funksjon

MIN og MAX velger en eksisterende verdi fra en kolonne. SUM og AVG Beregn et nytt tall fra hele kolonnen.

Anta at vi ønsker det totale beløpet av betalinger som er gjort så langt. MySQL SUM funksjon returnerer summen av alle verdiene i den angitte kolonnen. SUM fungerer kun på numeriske feltog NULL-verdier er ekskludert fra resultatet.

Tabellen nedenfor viser dataene i betalingstabellen.

Spørringen som vises nedenfor henter alle betalingene som er gjort og summerer dem opp til ett enkelt resultat: 2500 + 2000 + 6000 = 10500.

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

Resultat:

AVG funksjon

Ocuco MySQL AVG funksjon returnerer gjennomsnittet av verdiene i en spesifisert kolonne. Akkurat som SUM-funksjonen, det fungerer bare på numeriske datatyper.

Anta at vi ønsker å finne det gjennomsnittlige betalte beløpet. Vi kan bruke følgende spørring, som deler summen på 10500 med de tre betalingsradene som ikke er NULL.

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

Resultat:

⚠️ Advarsel: AVG deler på antall rader som ikke er NULL, ikke på radantallet i tabellen. Et NULL-beløp hoppes over i stedet for å telles som null, noe som stille presser gjennomsnittet opp. Bruk AVG(IFNULL(`betalt_beløp`, 0)) når en manglende verdi betyr null.

Praktisk eksempel: Kombinering av aggregeringsfunksjoner med GROUP BY

Hver funksjon ovenfor returnerte ett tall for hele tabellen. Legge til en GRUPPE AV leddsetningen returnerer ett siffer per gruppe i stedet – og det er slik ekte rapporter bygges.

Følgende eksempel grupperer medlemmer etter navn, og teller deretter det totale antallet betalinger, det gjennomsnittlige betalingsbeløpet og den totale summen av betalingsbeløpene 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`;

Utføre eksemplet ovenfor i MySQL Workbench gir oss følgende resultater.

Spørringen kobler de to tabellene sammen i WHERE-klausulen – den eldre kommakoblingsstilen. Moderne kode skriver den samme logikken som en eksplisitt INDRE SAMMENSLUTNING … PÅMerk også at alle ikke-aggregerte kolonner i SELECT-listen må vises i GROUP BY, eller MySQL 5.7 og senere avvis spørringen under ONLY_FULL_GROUP_BY. Se offisiell MySQL referanse for samlet funksjon.

Spørsmål og svar