Zoskupenie dát (group by)

Na poslednej prednáške tohto semestra o jazyku SQL si ukážeme veľmi silnú a často používanú funkciu, ktorej použitie je troška špecifické, ale zato veľmi efektívne. Ešte predtým si ale musíme troška upraviť naše dáta. Táto funkcia totižto slúži na zoskupenie záznamov podľa nejakého parametra a väčšinou býva týmto parametrom hodnota v nejakom stĺpci, ktorý akokeby kategorizuje (rozdeľuje do skupín) záznamy v danej tabuľke. Upravíme si našu tabuľku tak, že do nej pridáme stĺpec a naplníme ho hodnotami. Tieto hodnoty budú akokeby predstavovať skupiny našich používateľov:

ALTER TABLE users ADD COLUMN role VARCHAR(20) DEFAULT 'user';
UPDATE users SET role='admin' WHERE id=2 OR id=4 OR id=8 OR id=10;
UPDATE users SET role='owner' WHERE id=3 OR id=7;
UPDATE users SET role='customer service' WHERE id=9 OR id=12;

Ako vidíte, aby sme to urýchlili, resp. aby sme zmenšili kód, použili sme na nastavenie tých istých hodnôt podmienku OR v klauzule WHERE, takže nastavíme jedným riadkom viacej hodnôt. Týmto sme nastavili našim používateľom systému isté role (ich postavenie/práva v systéme) na admina, vlastníka (owner) a zákazníckej podpory (customer service).

Predstavme si, že naša tabuľka obsahuje tisíce riadkov a každý záznam má nejakú hodnotu v tomto stĺpci. Aby sme pochopili, čo táto funkcia robí je to, že zgrupuje (zoskupuje) riadky podľa hodnoty v zadanom stĺpci (parameter tejto funkcie) dokopy. To znamená, že povedzme 50 riadkov jednej kategórie zlepí do jedného. Možno sa pýtate, aký to má význam, keď nám rôzne hodnoty zlúči do jednej, takže požadované dáta stratíme, resp. k nim nemáme vo výsledku prístup. Táto funkcia sa však veľmi často používa v spojení s funkciou COUNT, ktorá ako vieme nám vracia počet záznamov v danom selecte.

Ak ju však použijeme v kombinácii s príkazom GROUP BY, vracia nám osobitne počet záznamov v každej združenej skupine. Asi si to neviete predstaviť, poďme si to názorne ukázať. Povedzme, že v našej tabuľke chceme zistiť, koľko máme používateľov jednotlivej kategórie (role). Bez tohto príkazu by sme to ručne vedeli spraviť tak, že by sme zavolali count funkciou a v podmienke WHERE by sme postupne mali konkrétnu hodnotu kategórie. Mohlo by to vyzerať asi takto:

SELECT COUNT('*') FROM users WHERE role='admin';
SELECT COUNT('*') FROM users WHERE role='owner';
SELECT COUNT('*') FROM users WHERE role='customer service';
SELECT COUNT('*') FROM users WHERE role='user';

Po vykonaní tohto príkazu dostaneme postupne štyri výsledky, kde vidíme počet záznamov každej kategórie používateľa. Tento spôsov je na jednej strane zdĺhavý, resp. neefektívny, zbytočne sú použité štyri rôzne príkazy a za ďalšie je nutné pri jeho vykonaní vedieť dopredu všetky kategórie, ktorých počet výskytov potrebujeme zistiť. Ak by sme mali kategórii dvesto alebo ešte v horšom prípade, bolo by ich veľa a ani by sme nevedeli, aké môžu byť, takto by sme to nemohli spraviť.

Práve preto existuje táto funkcia, ktorá zgrupuje rôzne hodnoty, resp. záznamy s danými hodnotami v danom stĺpci do jedného. Namiesto príkazov vyššie by sme mohli použiť jeden príkaz pomocou GROUP BY a dostali by sme žiadaný výsledok v jednej odpovedi. Mohlo by to vyzerať takto:

SELECT role,COUNT('*') FROM users GROUP BY role;

Pozrite sa na výsledok tejto query. Uvidíte, že obsahuje dva stĺpce, kde v prvom máme vypísanú hodnotu stĺpca role a v druhom to čo máme za príkazom select, čiže počet všetkých záznamov v danej kategórii. My sme zadali, že sa záznamy majú zgrupovať podľa hodnoty stĺpca role. To zapríčiní, že počet záznamov vo výsledku bude rovný počtu rôznych hodnôt v danom stĺpci, podľa ktorého chceme grupovať. Príkaz count(*) sa tým pádom vzťahuje na počet záznamov, ktoré boli zgrupnuté v danej kategórii (v danej hodnote stĺpca role).

Možno je to ťažké na pochopenie. Skúste si to predstaviť takto. Naša tabuľka má 11 záznamov a skúsme si ich zoradiť podľa stĺpca role (SELECT * FROM users ORDER BY role), aby sme videli všetky dáta (riadky) zoradené podľa kategórie. Keď sa na to takto pozeráme, tak vidíme 3 záznamy admin, potom 2 customer service, potom 2 owner a nakoniec 4 záznamy user. Count na takýto SELECT by hodil výsledok 11. Zavolaním príkazu GROUP BY podľa stĺpca role sa v podstate stane to, že sa prvé 3 riadky spoja dokopy (do jedného), potom dva riadky do jedného, zase dva riadky do jedného a nakoniec 4 riadky do jedného. Ak by sme si dali vypísať hodnoty aj iných stĺpcov, ako id, user_name, user_surname alebo age, hodnoty vo výsledky by nehovorili o ničom, pretože by to bola len jedna zo zoskupeých hodnôt. My by sme nevedeli, ku ktorému ID patrí ani nič, preto to v tomto prípade nemá význam.

Hlavné využitie funkcie GROUP BY je zistenie všetkých rôznych kategórii alebo typov záznamov podľa nejakého kľúča (kategórie – hodnoty v nejakom stĺpci). Ak by sme teda zavolali príkaz bez funkcie COUNT, dostali by sme iba zlepené riadky, ktorých hodnota okrem stĺpca role, ktorý obsahuje všetky rôzne hodnoty v danom stĺpci, nehovoria nič:

SELECT * FROM users GROUP BY role;

Ako vidíte, vo výsledku sú z každej kategórie vybraté ľubovoľné riadky (niekedy prvý, niekedy posledný...), ktoré nám nič nepovedia. Je to iba akokeby názorná ukážka z každej kategórie. Samozrejme, aj toto sa dá niekedy využiť, ak chceme napríklad ukázať na vzorový príklad údajov z každej kategórie. Ako sme ale povedali, využíva sa to najmä na spojenie s agregačnými funkciami.

Kombináciou týchto všetkých funkcií, ktoré sme sa naučili, môžeme naozaj dostať zaujímavé výsledky. Ak napríklad pridáme funkcie SUM a AVG do našej predchádzajúcej query, dostaneme navyše zaujímavé info:

SELECT role,COUNT('*'),AVG(age),SUM(age) FROM users GROUP BY role;

Okrem počtu záznamov kažédo typu používateľa vidíme teraz aj napríklad súčet rokov každej kategórie (SUM) alebo primerný vek v danej kategórii. Ak si predstavíte, že by to nebola tabuľka používateľov ale napríklad tabuľka produktov v eshope, kde by sme namiesto veku mali cenu a namiesto role by bola kategória produktu, pomocou tejto query by sme veľmi pekne videli počet produktov v každej kategórii ako aj priemernú cenu produktov v danej kategórii.

Okrem toho je možné viacnásobne grupovať. To znamená, že ak by sme do tejto našej query pridali ešte jedno zoskupenie, každá kategória by sa ešte rozbila podľa rôzneho záznamu v danej kategórii. V tomto prípade to nebude mať veľký význam, resp. výsledok nebude až tak veľavravný, pretože nemáme veľa rovnakých údajov v stĺpcoch. Ak by sme ale chceli mať rozdelené záznamy najprv podľa kategórie používateľa (role) a následne podľa veku, čiže aby nám používateľov v každej skupine zoskupilo podľa veku, príkaz by vyzeral takto:

SELECT role,age,COUNT('*'),AVG(age),SUM(age) FROM users GROUP BY role, age;

Ako vidíte vo výsledku, takmer v každej kategórii (role) nemáme žiadneho používateľa s rovnakým vekom, preto sú takmer všetky zázamy osobitne, ako keby sme group ani nepoužili. Ale v prípade skupiny admin a veku 18 rokov máme count 2, čo znamená, že v tejto kategórii a s týmto vekom sa nachádzajú 2 záznamy, ktoré sa zlepili dokopy.

Týmto sme sa dostali na koniec sekcie, resp. semestra o jazyka SQL a databázach. Ak ste niečo nepochopili, ako to funguje alebo aký je toho význam, nezúfajte. Hneď v ďalšom semestri si ideme všetko toto ukázať v praxi a napojíme si to na našu stránku, ktorú sme si vytvorili v minulom stupni štúdia. Tam uvidíte, ako to naozaj v praxi funguje a čo to vlastne znamená označenie informačný systém, pretože už budeme mať systém (webstránku), ktorá pracuje s informáciami (údajmi – dátami), ktoré budeme mať osobitne uložené v databáze.