MySQL Agregační funkce: SUM, COUNT, AVG & MAX

Co jsou agregační funkce v MySQL?

An agregační funkce čte mnoho řádků z jednoho sloupce a sbaluje je do jedné hodnoty. Agregační funkce jsou o:

• Provádění výpočtů na více řádcích
• Z jednoho sloupce tabulky
• A vrací jedinou hodnotu.

Norma ISO definuje pět (5) agregačních funkcí, a to:

1. COUNT
2. SOUČET
3. AVG
4. MIN
5. MAX

Pro všech pět platí jedno pravidlo: Agregační funkce ignorují hodnoty NULLCOUNT(*) je jedinou výjimkou a níže se podíváme na proč.

Proč používat agregační funkce

Různé úrovně organizace mají různé požadavky na informace. Vrcholoví manažeři se obvykle zajímají o celá čísla, nikoli o jednotlivé detaily.

Agregační funkce nám umožňují snadno vytvářet souhrnná data z naší databáze.

Například z naší databáze myflix může management vyžadovat následující zprávy:

• Nejméně vypůjčené filmy.
• Nejpůjčovanější filmy.
• Průměrný počet půjček každého filmu za měsíc.

Všechny výše uvedené sestavy pocházejí z agregačních funkcí. Pojďme se na každou z nich podívat podrobněji.

Funkce COUNT

Funkce COUNT vrací celkový počet hodnot v zadaném poli, a to jak číselných, tak nečíselných datových typů. Stejně jako každá agregační funkce, i funkce COUNT(column) vylučuje hodnoty NULL.

COUNT(*) je speciální forma, která vrací počet všech řádků v tabulce. Také počítá NULL a duplikáty, protože počítá řádky, nikoli hodnoty.

Tabulka movierentals obsahuje tato data:

Předpokládejme, že chceme zjistit, kolikrát byl film s ID 2 vypůjčen.

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

Provedení tohoto v MySQL Workbench proti myflixdb vrací 3, protože tři řádky obsahují movie_id 2.

DISTINCT Klíčové slovo

COUNT odpovídá na otázku „kolik“. Další otázkou je obvykle „kolik odlišný „ones“, a k tomu slouží DISTINCT.

Klíčové slovo DISTINCT vynechává duplikáty z našich výsledků podle skupiny.ping stejné hodnoty dohromady, přesně jak naznačuje výše uvedený obrázek.

Nejprve provedeme jednoduchý dotaz.

SELECT `movie_id` FROM `movierentals`;

Nyní stejný dotaz s klíčovým slovem DISTINCT:

SELECT DISTINCT `movie_id` FROM `movierentals`;

Funkce DISTINCT vynechává duplicitní záznamy:

COUNT vs. COUNT(*) vs. COUNT(DISTINCT): Který z nich byste měli použít?

DISTINCT lze také umístit uvnitř agregační funkce, a právě zde většina začátečníků prohrává track z nichž řádků se skutečně počítají. Všechny čtyři níže uvedené formuláře běží na stejné pětiřádkové tabulce movierentals, která byla uvedena dříve, ale ne všechny vracejí stejné číslo. Rozdíl spočívá ve dvou otázkách: počítá formulář řádky nebo hodnoty a uchovává duplikáty?

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

💡 Tip: Pro počet řádků použijte COUNT(*), pro počet sloupců COUNT(sloupec), pokud hodnota NULL znamená „neplatí“, a COUNT(DISTINCT sloupec) pro jedinečné hodnoty. Opakem DISTINCT je ALL – výchozí, a proto se vypisuje jen zřídka.

Funkce MIN

Funkce MIN vrátí nejmenší hodnotu v zadaném poli tabulky.

Předpokládejme, že chceme znát rok, ve kterém byl uveden nejstarší film v naší knihovně. MySQLTo nám dává funkce MIN.

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

Výsledek:

Funkce MAX

Jak již název napovídá, funkce MAX je opakem funkce MIN. To vrátí největší hodnotu ze zadaného pole tabulky.

Předpokládejme, že chceme rok vydání nejnovějšího filmu v naší databázi. Následující příklad ho vrátí.

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

Výsledek:

Funkce SUM

Funkce MIN a MAX vybírají existující hodnotu ze sloupce. Funkce SUM a AVG Vypočítejte nové číslo z celého sloupce.

Předpokládejme, že chceme znát celkovou částku dosud provedených plateb. MySQL SOUČET funkce vrací součet všech hodnot v zadaném sloupci. SUM funguje pouze na číselných polích, a Hodnoty NULL jsou z výsledku vyloučeny..

Následující tabulka zobrazuje data v tabulce plateb.

Dotaz zobrazený níže načte všechny provedené platby a sečte je do jednoho výsledku: 2500 + 2000 + 6000 = 10500.

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

Výsledek:

AVG funkce

Jedno MySQL AVG funkce vrátí průměr hodnot v zadaném sloupci. Stejně jako funkce SUM, to funguje pouze na číselných typech dat.

Předpokládejme, že chceme zjistit průměrnou zaplacenou částku. Můžeme použít následující dotaz, který vydělí součet 10500 třemi řádky plateb, které nemají hodnotu NULL.

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

Výsledek:

Warning️ Varování: AVG dělí se počtem řádků, které nejsou NULL, nikoli počtem řádků v tabulce. Hodnota NULL se přeskočí, místo aby se považovala za nulu, což nenápadně zvyšuje průměr. Použijte AVG(IFNULL(`zaplacená_částka`, 0)), pokud chybějící hodnota znamená nulu.

Praktický příklad: Kombinace agregačních funkcí s funkcí GROUP BY

Každá výše uvedená funkce vrátila jeden údaj pro celou tabulku. Přidání SKUPINA VYTVOŘENÁ klauzule vrací jednu číslici za skupinu místo toho – a tak se vytvářejí skutečné zprávy.

Následující příklad seskupí členy podle jména a poté spočítá celkový počet plateb, průměrnou výši platby a celkovou částku plateb pro každého člena.

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

Provedení výše uvedeného příkladu v MySQL Workbench nám dává následující výsledky.

Dotaz spojuje obě tabulky v klauzuli WHERE – starším stylu spojení čárkou. Moderní kód zapisuje stejnou logiku jako explicitní VNITŘNÍ SPOJENÍ … ZAPNUTOVšimněte si také, že každý neagregovaný sloupec v seznamu SELECT se musí objevit ve skupině GROUP BY, nebo MySQL 5.7 a novější odmítají dotaz pod ONLY_FULL_GROUP_BY. Viz oficiální MySQL odkaz na agregační funkci.

Nejčastější dotazy