MySQL Aggregeringsfunktioner: SUMMA, ANTAL, AVG & MAX

Vad är aggregatfunktioner i MySQL?

An aggregerad funktion läser många rader i en enda kolumn och komprimerar dem till ett värde. Aggregeringsfunktioner handlar om:

• Utföra beräkningar på flera rader
• Av en enda kolumn i en tabell
• Och returnera ett enda värde.

ISO-standarden definierar fem (5) aggregerade funktioner, nämligen:

1. RÄKNA
2. SUMMA
3. AVG
4. MIN
5. MAX

En regel gäller för alla fem: aggregeringsfunktioner ignorerar NULL-värdenCOUNT(*) är det enda undantaget, och vi tittar på varför nedan.

Varför använda aggregerade funktioner

Olika organisationsnivåer har olika informationskrav. Chefer på högsta nivå är oftast intresserade av hela siffror, inte enskilda detaljer.

Aggregatfunktioner gör att vi enkelt kan producera sammanfattade data från vår databas.

Till exempel kan ledningen kräva följande rapporter från vår myflix-databas:

• Minst hyrda filmer.
• Mest hyrda filmer.
• Genomsnittligt antal gånger som varje film hyrs ut per månad.

Alla ovanstående rapporter kommer från aggregerade funktioner. Låt oss titta på var och en i detalj.

COUNT-funktionen

Funktionen COUNT returnerar det totala antalet värden i det angivna fältet, både numeriska och icke-numeriska datatyper. Liksom alla aggregeringsfunktioner exkluderar COUNT(kolumn) NULL-värden.

COUNT(*) är en specialform som returnerar antalet rader i en tabell. Den räknar också NULLs och dubbletter, eftersom den räknar rader snarare än värden.

Tabellen Movierentals innehåller dessa data:

Låt oss anta att vi vill få antalet gånger som filmen med id 2 har hyrts ut.

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

Att utföra detta i MySQL Arbetsbänk mot myflixdb returnerar 3, eftersom tre rader innehåller movie_id 2.

DISTINKT nyckelord

ANTAL svarar på ”hur många”. Nästa fråga är vanligtvis ”hur många olika "ettor", och det är vad DISTINCT är till för.

Nyckelordet DISTINCT utelämnar dubbletter från våra resultat efter gruppping identiska värden tillsammans, precis som illustrationen ovan antyder.

Låt oss först köra en enkel fråga.

SELECT `movie_id` FROM `movierentals`;

Nu samma fråga med nyckelordet DISTINCT:

SELECT DISTINCT `movie_id` FROM `movierentals`;

DISTINCT utelämnar dubbletter:

COUNT vs COUNT(*) vs COUNT(DISTINCT): Vilken ska du använda?

DISTINCT kan också placeras inuti en aggregerad funktion, och det är här de flesta nybörjare förlorar track av vilka rader faktiskt räknas. De fyra formulären nedan körs alla mot samma femradiga filmuthyrningstabell som visades tidigare, men de returnerar inte alla samma tal. Skillnaden beror på två frågor: räknar formuläret rader eller värden, och behåller det dubbletter?

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

💡 Tips: Använd COUNT(*) för radantal, COUNT(kolumn) när ett NULL-värde ska betyda "gäller inte", och COUNT(DISTINCT kolumn) för unika värden. Motsatsen till DISTINCT är ALL — standardvärdet och skrivs därför sällan ut.

MIN-funktionen

MIN-funktionen returnerar det minsta värdet i det angivna tabellfältet.

Anta att vi vill ha året då den äldsta filmen i vårt bibliotek släpptes. MySQLs MIN-funktion ger oss det.

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

Resultat:

MAX-funktionen

Precis som namnet antyder är MAX-funktionen motsatsen till MIN-funktionen. Det returnerar det största värdet från det angivna tabellfältet.

Anta att vi vill ha året då den senaste filmen i vår databas släpptes. Följande exempel returnerar det.

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

Resultat:

SUM-funktionen

MIN och MAX väljer ett befintligt värde från en kolumn. SUM och AVG beräkna ett nytt tal från hela kolumnen.

Anta att vi vill ha det totala beloppet av betalningar som hittills gjorts. MySQL SUMMA fungera returnerar summan av alla värden i den angivna kolumnen. SUM fungerar endast på numeriska fältoch NULL-värden exkluderas från resultatet.

Följande tabell visar informationen i betalningstabellen.

Frågan som visas nedan hämtar alla gjorda betalningar och summerar dem till ett enda resultat: 2500 + 2000 + 6000 = 10500.

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

Resultat:

AVG fungera

Ocuco-landskapet MySQL AVG fungera returnerar medelvärdet av värdena i en angiven kolumn. Precis som SUM-funktionen, det fungerar endast på numeriska datatyper.

Anta att vi vill hitta det genomsnittliga beloppet som betalats. Vi kan använda följande fråga, som dividerar summan 10500 med de tre betalningsraderna som inte är NULL.

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

Resultat:

⚠️ Varning: AVG dividerar med antalet rader som inte är NULL, inte med tabellens radantal. Ett NULL-belopp hoppas över istället för att räknas som noll, vilket i tysthet höjer medelvärdet. Använd AVG(IFNULL(`amount_paid`, 0)) när ett saknat värde betyder noll.

Praktiskt exempel: Kombinera aggregeringsfunktioner med GROUP BY

Varje funktion ovan returnerade en siffra för hela tabellen. Att lägga till en GRUPP AV klausulen returnerar en siffra per grupp istället – och det är så riktiga rapporter byggs upp.

Följande exempel grupperar medlemmar efter namn och räknar sedan det totala antalet betalningar, det genomsnittliga betalningsbeloppet och den totala summan av betalningsbeloppen för varje 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ör exemplet ovan i MySQL Workbench ger oss följande resultat.

Frågan kopplar ihop de två tabellerna i WHERE-klausulen – den äldre kommakopplingsstilen. Modern kod skriver samma logik som en explicit INNERFÖRBINDNING … PÅObservera också att varje icke-aggregerad kolumn i SELECT-listan måste visas i GROUP BY, eller MySQL 5.7 och senare avvisa frågan under ONLY_FULL_GROUP_BY. Se tjänsteman MySQL referens för aggregeringsfunktion.

Vanliga frågor