MySQL Összesítő függvények: SZUM, SZÁM, AVG & MAX

Mik azok az aggregált függvények? MySQL?

An aggregált függvény egyetlen oszlop sok sorát olvassa be, és egyetlen értékké csukja össze őket. Az aggregátumfüggvények lényege:

• Számítások végrehajtása több sorban
• Egy táblázat egyetlen oszlopából
• És egyetlen értéket ad vissza.

Az ISO szabvány öt (5) aggregátumfüggvényt határoz meg, nevezetesen:

1. COUNT
2. ÖSSZEG
3. AVG
4. MIN
5. MAX

Egyetlen szabály vonatkozik mind az ötre: az összesítő függvények figyelmen kívül hagyják a NULL értékeketA COUNT(*) az egyetlen kivétel, és az alábbiakban megvizsgáljuk, hogy miért.

Miért használjunk aggregált függvényeket?

A különböző szervezeti szinteken eltérő információigények vannak. A felső szintű vezetőket általában a teljes számok érdeklik, nem az egyes részletek.

Az aggregált függvények lehetővé teszik, hogy könnyen összefoglalt adatokat állítsunk elő adatbázisunkból.

Például a myflix adatbázisunkból a vezetőségnek a következő jelentésekre lehet szüksége:

• Legkevésbé kölcsönzött filmek.
• A legtöbb kölcsönzött film.
• Az egyes filmek átlagos kölcsönzési száma egy hónapban.

A fenti jelentések mind összesített függvényekből származnak. Nézzük meg mindegyiket részletesen.

COUNT függvény

A DARAB függvény visszaadja a megadott mezőben található értékek teljes számát, mind numerikus, mind nem numerikus adattípusok esetén. Mint minden összesítő függvény, a COUNT(oszlop) is kizárja a NULL értékeket.

A COUNT(*) egy speciális alak, amely egy táblázat összes sorának számát adja vissza. Számolja is NULL-ok és duplikátumokat is tartalmaz, mivel sorokat számol, nem pedig értékeket.

A movierentals tábla a következő adatokat tartalmazza:

Tegyük fel, hogy meg akarjuk tudni, hogy a 2-es azonosítójú filmet hányszor kölcsönözték ki.

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

Ennek végrehajtása MySQL Workbench A myflixdb függvény 3-as értéket ad vissza, mivel három sor tartalmazza a 2-es movie_id-t.

DISTINCT Kulcsszó

A COUNT válasza a „hány” kérdésre. A következő kérdés általában az, hogy „hány” különböző „egyek”, és erre való a DISTINCT.

A DISTINCT kulcsszó kihagyja a duplikátumokat az eredményeinkből csoportonként.ping azonos értékeket együtt, pontosan úgy, ahogy a fenti ábra sugallja.

Először is, futtassunk le egy egyszerű lekérdezést.

SELECT `movie_id` FROM `movierentals`;

Most ugyanaz a lekérdezés a DISTINCT kulcsszóval:

SELECT DISTINCT `movie_id` FROM `movierentals`;

A DISTINCT függvény kihagyja a duplikált rekordokat:

COUNT vs COUNT(*) vs COUNT(DISTINCT): Melyiket érdemes használni?

DISTINCT is elhelyezhető belső egy aggregátumfüggvény, és itt veszít a legtöbb kezdő track amelyből a sorokat ténylegesen megszámoljuk. Az alábbi négy űrlap mind ugyanazon az öt soros movierentals táblázaton fut, amelyet korábban láthattunk, mégis nem mindegyik adja vissza ugyanazt a számot. A különbség két kérdésre vezethető vissza: a űrlap sorokat vagy értékeket számol, és megőrzi-e a duplikáltakat?

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

💡 Tipp: Használd a COUNT(*) függvényt sorok számának meghatározásához, a COUNT(oszlop) függvényt, ha a NULL értéknek „nem alkalmazható” jelentést kell adnia, és a COUNT(DISTINCT oszlop) függvényt egyedi értékekhez. A DISTINCT ellentéte az ALL – az alapértelmezett, ezért ritkán kerül kiírásra.

MIN funkció

A MIN funkció a megadott táblamező legkisebb értékét adja vissza.

Tegyük fel, hogy azt az évet keressük, amelyben a könyvtárunkban található legrégebbi film megjelent. MySQLA MIN függvénye adja meg ezt.

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

Eredmény:

MAX funkció

Ahogy a neve is sugallja, a MAX függvény a MIN függvény ellentéte. Azt a legnagyobb értéket adja vissza a megadott táblamezőből.

Tegyük fel, hogy az adatbázisunkban szereplő legújabb film megjelenésének évét keressük. A következő példa ezt adja vissza.

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

Eredmény:

SUM funkció

A MIN és MAX függvények egy meglévő értéket választanak ki egy oszlopból. A SZUM és AVG Számítson ki egy új számot az egész oszlopból.

Tegyük fel, hogy az eddig kifizetett összegek teljes összegét szeretnénk. MySQL ÖSSZEG funkció visszaadja a megadott oszlopban lévő összes érték összegét. A SUM csak numerikus mezőkön működikés A NULL értékeket kizárjuk az eredményből..

A következő táblázat a fizetési táblázatban található adatokat mutatja.

Az alább látható lekérdezés kiszámolja az összes befizetést, és egyetlen eredményné összegzi azokat: 2500 + 2000 + 6000 = 10500.

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

Eredmény:

AVG funkció

Az MySQL AVG funkció egy megadott oszlopban lévő értékek átlagát adja vissza. Csakúgy, mint a SUM függvény, az csak numerikus adattípusokon működik.

Tegyük fel, hogy meg akarjuk találni az átlagosan kifizetett összeget. Használhatjuk a következő lekérdezést, amely a 10500-as összeget elosztja a három nem NULL értékű kifizetési sorral.

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

Eredmény:

⚠️ Figyelmeztetés: AVG a nem NULL sorok számával osztja, nem a tábla sorainak számával. A NULL összeget a rendszer kihagyja, ahelyett, hogy nullának számolná, ami csendben feljebb nyomja az átlagot. Használja a AVG(IFNULL(`fizetett_összeg`, 0)), ha a hiányzó érték nullát jelent.

Gyakorlati példa: Összesítő függvények kombinálása a GROUP BY függvénnyel

A fenti függvények mindegyike egy számot adott vissza a teljes táblázatra vonatkozóan. Egy hozzáadásával CSOPORTOSÍT záradék egy számjegyet ad vissza csoportonként ehelyett – és így készülnek az igazi jelentések.

A következő példa név szerint csoportosítja a tagokat, majd megszámolja az egyes tagok befizetéseinek teljes számát, az átlagos befizetési összeget és a befizetések végösszegét.

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

A fenti példa végrehajtása in MySQL A Workbench a következő eredményeket adja.

A lekérdezés a WHERE záradékban egyesíti a két táblát – a régebbi vesszős illesztési stílusban. A modern kód ugyanazt a logikát írja, mint egy explicit BELSŐ CSATLAKOZÁS … BEAzt is vegye figyelembe, hogy a SELECT lista minden nem összesített oszlopának szerepelnie kell a GROUP BY listában, vagy MySQL 5.7 és újabb verziók elutasítják a lekérdezést ONLY_FULL_GROUP_BY alatt. Lásd a hivatalos MySQL összesítő függvény referencia.

GYIK