📊 Řazení a agregační funkce

Seřadit a spočítat

O čem to je

Na konci pololetí chce třídní učitelka vědět: jaký je průměr z matematiky, kolik známek kdo má a kdo má průměr horší než 2. Nechce vidět všech 500 známek, chce souhrnná čísla. A chce je seřazená.

SQL na to má řazení (ORDER BY) a agregační funkce, které z mnoha řádků spočítají jedno číslo.

Nové pojmy

Dotaz SELECTPřipomenutí: SELECT sloupce FROM tabulka WHERE podmínka; vybere řádky a sloupce z tabulky.SELECT znamka FROM znamky WHERE predmet = 'M';
ORDER BYSeřadí výsledek podle sloupce. ASC vzestupně (výchozí), DESC sestupně. Bez ORDER BY není pořadí řádků zaručené.ORDER BY prijmeni ASC
Agregační funkceFunkce, která z hodnot mnoha řádků spočítá jednu hodnotu.AVG(znamka) spočítá průměr všech známek.
FunkceCo spočítá
COUNT(*)počet řádků
COUNT(sloupec)počet řádků, kde sloupec není NULL
SUM(sloupec)součet
AVG(sloupec)aritmetický průměr (NULL vynechá)
MIN(sloupec), MAX(sloupec)nejmenší, největší hodnota

Tabulka znamky

idid_zakapredmetznamka
11M1
21ČJ2
32M3
42ČJ4
53M2
64ČJ1
72M5

Příklady

DotazVýsledek
SELECT COUNT(*) FROM znamky;7
SELECT SUM(znamka) FROM znamky;18
SELECT MIN(znamka), MAX(znamka) FROM znamky;1, 5
SELECT AVG(znamka) FROM znamky WHERE predmet = 'M';2.75
Spočítej průměr z matematiky ručně a ověř výsledek dotazu.
Matematika: 1, 3, 2, 5. Součet 11, počet 4. Průměr 11 / 4 = 2.75. WHERE nejdřív nechá jen řádky matematiky, AVG pak počítá jen z nich.
Seřaď žáky od nejmladšího, při stejném roce podle příjmení.
SELECT jmeno, prijmeni FROM zaci ORDER BY rok_narozeni DESC, prijmeni ASC; Nejdřív se řadí podle roku sestupně (nejmladší má nejvyšší rok). Když je rok stejný, rozhoduje příjmení.
✗SELECT * FROM zaci WHERE AVG(znamka) > 2;
✓Agregace patří do HAVING (viz druhé vysvětlení) nebo do SELECT, ne do WHERE.

WHERE posuzuje každý řádek zvlášť. Průměr ale vzniká až z více řádků, takže ho WHERE ještě nezná.

ORDER BY řadí výsledek (ASC vzestupně, DESC sestupně). Agregační funkce COUNT, SUM, AVG, MIN, MAX spočítají z mnoha řádků jednu hodnotu.

Skupiny

O čem to je

Průměr všech známek je málo užitečný. Učitelka chce průměr zvlášť pro každý předmět nebo pro každého žáka. Představ si, že známky rozhází do hromádek podle předmětu a v každé hromádce spočítá průměr. Přesně to dělá GROUP BY.

A když chce vidět jen žáky s průměrem horším než 2, přidá podmínku na hromádky: HAVING.

Nové pojmy

Agregační funkceCOUNT, SUM, AVG, MIN, MAX – z hodnot více řádků spočítají jednu hodnotu.AVG(znamka)
GROUP BYRozdělí řádky do skupin podle stejné hodnoty sloupce. Agregační funkce se pak spočítá pro každou skupinu zvlášť. Výsledek má jeden řádek za každou skupinu.GROUP BY predmet vytvoří skupinu M a skupinu ČJ.
HAVINGPodmínka na celé skupiny. Smí obsahovat agregační funkce.HAVING AVG(znamka) > 2
WHERE vs. HAVINGWHERE filtruje jednotlivé řádky před seskupením. HAVING filtruje hotové skupiny po seskupení.Nejdřív vyhoď neplatné známky (WHERE), pak skupiny s malým počtem (HAVING).
idid_zakapredmetznamka
11M1
21ČJ2
32M3
42ČJ4
53M2
64ČJ1
72M5

Průměr podle předmětu

  • SELECT predmet, COUNT(*) AS pocet, AVG(znamka) AS prumer
  • FROM znamky
  • GROUP BY predmet;
predmetpocetprumer
M42.75
ČJ32.33…

Pořadí vyhodnocení celého dotazu

FROM – vezmi tabulku.
WHERE – vyhoď nevyhovující řádky.
GROUP BY – rozděl řádky do skupin.
HAVING – vyhoď nevyhovující skupiny.
SELECT – spočítej sloupce a agregace.
ORDER BY – seřaď výsledek.
Kteří žáci mají průměr horší než 2? Napiš dotaz a výsledek.
SELECT id_zaka, AVG(znamka) FROM znamky GROUP BY id_zaka HAVING AVG(znamka) > 2; Skupiny: žák 1 (1, 2) = 1.5, žák 2 (3, 4, 5) = 4, žák 3 (2) = 2, žák 4 (1) = 1. Podmínku splní jen žák 2 s průměrem 4. Žák 3 má přesně 2, a to není víc než 2.
Kolik žáků narozených v roce 2008 a později je v každé třídě (tabulka zaci: 2.A Eva 2009 a Petr 2008, 3.B Jana 2008 a Tomáš 2007, 1.C Lucie 2010)?
SELECT trida, COUNT(*) FROM zaci WHERE rok_narozeni >= 2008 GROUP BY trida; WHERE vyřadí Tomáše (2007) ještě před seskupením. Výsledek: 2.A 2, 3.B 1, 1.C 1.
✗SELECT predmet, id_zaka, AVG(znamka) FROM znamky GROUP BY predmet;
✓V SELECT mohou být jen sloupce z GROUP BY a agregace: SELECT predmet, AVG(znamka) … GROUP BY predmet;

Ve skupině M je několik různých id_zaka. Který by se měl ukázat? Standard SQL takový dotaz odmítne (některé SŘBD vrátí náhodnou hodnotu).

GROUP BY rozdělí řádky do skupin a agregace se počítá pro každou zvlášť. WHERE filtruje řádky před seskupením, HAVING skupiny po něm. V SELECT smí být jen seskupované sloupce a agregace.
🔎SQL: SELECT a WHERE🤝Spojování tabulek (JOIN)