Skip to main content

4.2 Zoskupovanie údajov a súhrny

Zoskupovanie údajov

Údaje v tabuľke môžeme zoskupiť (agregovať) podľa nejakého spoločného atribútu a na každú skupinu následne uplatniť agregačné funkcie. Zoskupenie dosiahneme pomocou klauzuly GROUP BY, ktorá nasleduje až po klauzulách FROM a WHERE:

  • GROUP BY {stĺpec alebo stĺpce}

Štandard SQL nedovoľuje v GROUP BY používať aliasy stĺpcov, hoci MariaDB i PostgreSQL to umožňujú.

Napríklad údaje o zamestnancoch môžeme zoskupiť podľa ich oddelenia a potom pre každú skupinu (pre každé oddelenie) zistiť priemerný / najnižší / najvyšší plat:

SELECT
	ROUND(AVG(salary), 2) AS "priemerný plat",
	MIN(salary) AS "najnižší plat",
	MAX(salary) AS "najvyšší plat"
FROM employees
GROUP BY department_id;

Čo je zlé s vyššie uvedeným príkladom? Koľko riadkov výsledkov dostaneme?

Odpovede na otázky

Vo výsledkoch dostaneme toľko riadkov, koľko je oddelení. A možno o jeden viac - ak máme aj zamestnancov bez oddelenia. Pre každé oddelenie získame priemerný, najnižší i najvyšší plat tak, ako sme si želali.

Problém je, že nijako nemôžeme zistiť, ktorý riadok výsledkov patrí ktorému oddeleniu, a teda výsledky sú nám prakticky k ničomu.

Kombinácia zoskupených a jednotlivých údajov

V predošlej téme sme sa naučili, že v SELECT klauzule nemôžeme kombinovať agregačné výrazy a jednotlivé stĺpce. Ak však údaje zoskupujeme podľa niektorého stĺpca, ten môže byť (a z praktického hľadiska obvykle aj je) uvedený v SELECT klauzule. Teda do predošlého príkladu určite budeme chcieť pridať aspoň oddelenie:

SELECT
	department_id AS oddelenie,
	COUNT(*) AS "počet zamestnancov",
	ROUND(AVG(salary), 2) AS "priemerný plat",
	MIN(salary) AS "najnižší plat",
	MAX(salary) AS "najvyšší plat"
FROM employees
GROUP BY department_id
ORDER BY department_id;

Pokiaľ údaje zoskupujeme podľa oddelenia, vo výsledkoch bude uvedené každé oddelenie a jeho priemerný, najnižší a najvyšší plat. Tentokrát však už uvidíme názov oddelenia a pridali sme aj počet zamestnancov.

Zoskupovanie môže mať zmysel, aj keď sa celý dopyt týka len jediného stĺpca - napríklad by sme chceli rebríček mien podľa počtu ich výskytov:

SELECT
	first_name AS meno,
	COUNT(*) AS počet
FROM employees
GROUP BY first_name
ORDER BY počet DESC, meno;

V Oracle SQL je možné agregačné funkcie aj vnárať, teda získať napríklad maximum z priemerov skupín, napríklad maximálny priemerný plat výrazom SELECT MAX(AVG(salary)). Toto je v PostgreSQL a MySQL/MariaDB nutné riešiť inak - vnorenými dopytmi, ktoré sa naučíme neskôr.

Viacnásobné zoskupenie

Už teda vieme získať prehľad jednotlivých oddelení a ich priemerných platov (údaje zoskupíme podľa oddelenia) a tiež aj prehľad jednotlivých zamestnaní a ich platov (údaje zoskupíme podľa zamestnania). No bolo by to možné spojiť a získať priemerné platy zamestnaní v jednotlivých oddeleniach? Áno, údaje môžeme zoskupovať na menšie skupiny aj podľa viacerých stĺpcov - oddeľujeme ich čiarkou:

SELECT
	department_id AS "oddelenie",
	job_id AS "zamestnanie",
	ROUND(AVG(salary)) AS "priemerný plat",
	COUNT(*) AS "počet zamestnancov"
FROM employees
GROUP BY
	department_id, job_id
ORDER BY
	department_id, job_id;

Obvykle za GROUP BY klauzulou nasleduje ORDER BY s rovnakými stĺpcami. Niektoré dialekty SQL (napríklad MariaDB) automaticky zoradia údaje rovnako, ako špecifikuje GROUP BY.

Čo vlastne robí tento dopyt?

Predstavme si situáciu, že máme viacerých zamestnancov s pracovným zaradením „technik IT“. Väčšina pracuje v oddelení „IT“, no niektoré oddelenia majú aj vlastného technika IT, ktorý je špecialistom na ich softvér a zariadenia.

Ak zoskupíme údaje podľa zamestnania, „zmiešame“ všetkých technikov IT zo všetkých oddelení. Ak zoskupíme podľa oddelenia, nerozlíšime technikov IT.

Pokiaľ zoskupíme aj podľa oddelenia, aj podľa zamestnania, tak budeme mať technikov IT zvlášť pre každé oddelenie a samozrejme aj ostatné zamestnania podobne.

Selekcia údajov (filtrovanie)

Nie vždy chceme vyhodnocovať všetky riadky z tabuľky a nie vždy chceme vo výsledkoch vidieť všetky riadky tabuľky. Znie to veľmi podobne, takmer rovnako. No pozor, ide o dve odlišné veci!

Filtrovanie údajov pred zoskupením

Zoskupovanie GROUP BY môžeme kombinovať aj so selekciou WHERE. Najskôr sa vykoná selekcia riadkov a až potom sa údaje zoskupia. Ide teda o akýsi predfilter. Ak budeme chcieť priemerné platy jednotlivých oddelení a vo WHERE podmienke uvedieme, že sídlo oddelenia má byť v Seattli, najskôr sa vyberú oddelenia zo sídlom v Seattli (predfilter) a až potom sa zoskupia zamestnanci podľa oddelenia:

SELECT
	d.department_name AS oddelenie,
	ROUND(AVG(e.salary)) AS "priemerný plat"
FROM departments d
	JOIN locations l ON d.location_id = l.location_id
	JOIN employees e ON e.department_id = d.department_id
WHERE l.city = 'Seattle'
GROUP BY d.department_name
ORDER BY d.department_name;

Filtrovanie údajov po zoskupení

Niekedy potrebujeme vykonať selekciu výsledkovvýsledných skupín - až po tom, čo údaje zoskupíme. Nebude sa jednať o sídlo v Seattli, ani žiadnu inú podmienku, ktorá by bola overiteľná vopred. Chceme napríklad len tie oddelenia, ktorých priemerný plat je aspoň 10000. Priemerný plat oddelenia je však možné zistiť až po zoskupení, teda nejde o predfilter a WHERE nám nepomôže.

K dispozícii však máme aj podobnú klauzulu:

  • HAVING {podmienka s agregačnou funkciou}

Píšeme ju až za klauzulou GROUP BY. V tomto prípade by sme mohli využiť dopyt:

SELECT
	d.department_name AS oddelenie,
	ROUND(AVG(e.salary)) AS "priemerný plat"
FROM departments d
	JOIN employees e ON e.department_id = d.department_id
GROUP BY d.department_name
HAVING ROUND(AVG(e.salary)) >= 10000
ORDER BY d.department_name;

Súhrny

Už sme sa zoznámili s viacnásobným zoskupením - vieme získať napríklad priemerný plat zamestnaní zvlášť pre každé oddelenie. Ale čo keby sme chceli k tomuto prehľadu ešte aj priemerný plat samotných oddelení? 

V Oracle SQL a PostgreSQL môžeme pre tento účel využiť rozšírenia GROUP BY klauzuly, ktoré poskytnú viaceré súhrny súčasne:

  • GROUP BY ROLLUP({zoznam stĺpcov})
  • GROUP BY CUBE({zoznam stĺpcov})
  • GROUP BY GROUPING SETS (({zoznam stĺpcov}), ({zoznam stĺpcov}), …)

Postupné súhrny (ROLLUP)

Na prvý pohľad vyzerajú výsledky pri použití ROLLUP rovnako, ako by sme ho nepoužili a uviedli len zoskupenie podľa viacerých stĺpcov. Doplnené sú však práve súhrny.

Ak chceme poznať počty zamestnancov a v ROLLUP uvedieme dva stĺpce, napríklad oddelenie a zamestnanie - GROUP BY ROLLUP(oddelenie, zamestnanie), tak vo výsledkoch nebudú len počty zamestnancov každého zamestnania zvlášť pre každé oddelenie, ale aj počty zamestnancov celých oddelení a celkový počet zamestnancov, teda spoja sa tri zoskupenia:

  1. GROUP BY oddelenie, zamestnanie
  2. GROUP BY oddelenie
  3. celková agregácia bez zoskupenia (napríklad počet všetkých zamestnancov)
SELECT
	department_id AS oddelenie,
	job_id AS zamestnanie,
	COUNT(*) AS počet
FROM employees
GROUP BY
	ROLLUP(oddelenie, zamestnanie)
ORDER BY
	oddelenie, zamestnanie;

V tomto príklade (ale aj ďalších) sme v klauzule GROUP BY použili aliasy stĺpca - v záujme prehľadnosti. Hoci MariaDB a PostgreSQL to umožňujú, je to v rozpore so štandardným SQL a napríklad v Oracle SQL by to skončilo s chybovým hlásením. Lepšie je preto používať priamo názvy stĺpcov.

Ešte zaujímavejšia situácia nastane, keď uvedieme tri stĺpce, všeobecne s1, s2, s3. Klauzula GROUP BY ROLLUP (s1, s2, s3) spojí viaceré zoskupenia:

  1. GROUP BY s1, s2, s3
  2. GROUP BY s1, s2
  3. GROUP BY s1
  4. celková agregácia bez zoskupenia

Môžeme to chápať ako hierarchiu, prenesené do časových období by to bolo napríklad ako:

  • súhrn za každý mesiac → súhrn za každý štvrťrok → súhrn za každý rok → celkový súhrn.

Kompletné súhrny (CUBE)

Kým ROLLUP poskytoval postupné súhrny tak, ako idú za sebou, CUBE poskytne súhrn úplne všetkých kombinácií, čo v praxi býva obvykle zbytočné. K predošlému príkladu pribudnú ešte súhrny pre každé zamestnanie:

SELECT
	department_id AS oddelenie,
	job_id AS zamestnanie,
	COUNT(*) AS počet
FROM employees
GROUP BY
	CUBE(oddelenie, zamestnanie)
ORDER BY
	oddelenie, zamestnanie;

Rozoberme to tiež na rovnakom všeobecnom príklade - klauzula GROUP BY CUBE (s1, s2, s3) spojí viaceré zoskupenia:

  1. GROUP BY s1, s2, s3
  2. GROUP BY s1, s2
  3. GROUP BY s1, s3
  4. GROUP BY s2, s3
  5. GROUP BY s1
  6. GROUP BY s2
  7. GROUP BY s3
  8. celková agregácia bez zoskupenia

Voliteľné súhrny (GROUPING SETS)

Pokiaľ chceme mať plnú kontrolu nad situáciou a chceme si zvoliť konkrétne zoskupenia, ktoré si želáme, GROUPING SETS nám to umožnia. Ak by sme napríklad v našom príklade chceli k počtom zamestnancov každého zamestnania pre každé oddelenie (oddelenie, zamestnanie) pridať ešte celkový súhrn, použijeme klauzulu GROUPING SETS((oddelenie, zamestnanie), ()):

SELECT
	department_id AS oddelenie,
	job_id AS zamestnanie,
	COUNT(*) AS počet
FROM employees
GROUP BY
	GROUPING SETS((oddelenie, zamestnanie), ())
ORDER BY
	oddelenie, zamestnanie;
Back to top