Agregujeme zvieratá v MySQL (GROUP BY, DISTINCT a spol.)

Dnes to bude na SQL nôtu. Predstavme si, že máme útulok pre zvieratká nájdené na ulici a pre každé si evidujeme druh a oblasť, kde bol zver nájdený. Čo keď chceme zistiť koľko zvierat sa našlo v ktorej lokalite?

Riešenie

Tabuľka bude jednoduchá (použijeme MySQL):

CREATE TABLE nalez (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    zviera VARCHAR(30), 
    miesto_nalezu VARCHAR(30)
);

V duchu tradície bude stĺpec id reprezentovať primárny kľúč, a vďaka fintivej vlastnosti MySQL s automatickou inkrementáciou sa nemusíme o nič starať.

Hodnoty vložíme napr. takto:

INSERT INTO nalez (zviera, miesto_nalezu) VALUES 
    ("nosorožec", 'Dúbravka'), 
    ("hroch", 'Devínska Nová Ves'), 
    ("pytón", 'Dúbravka'), 
    ("nosorožec", 'Dúbravka'), 
    ("nosorožec", 'Dúbravka'), 
    ("nosorožec", 'Rača'), 
    ("hroch", 'Ružinov'), 
    ("hroch", 'Devínska Nová Ves');

V tomto prípade som explicitne vymenoval vkladané stĺpce. Stĺpec id je vynechaný, a každý riadok v ňom bude mať hodnotu NULL, ale opäť: zázrak autoinkrementácie doplní automaticky vygenerovanú hodnotu.

VySELECTujme všetky zvieratá zoradené podľa druhu a následne podľa miesta nálezu:

SELECT zviera, miesto_nalezu
FROM nalez
ORDER BY zviera, miesto_nalezu

Výsledkom bude:

'hroch', 'Devínska Nová Ves'
'hroch', 'Devínska Nová Ves'
'hroch', 'Ružinov'
'nosorožec', 'Dúbravka'
'nosorožec', 'Dúbravka'
'nosorožec', 'Dúbravka'
'nosorožec', 'Rača'
'pytón', 'Dúbravka'

Niektoré záznamy sa opakujú (možno špekulovať o zlom návrhu databázy, resp. aplikácie, cez ktorú sa evidujú dáta ;-)).

Keby sme chceli jedinečné riadky

Keby sme chceli jedinečné riadky, užijeme klauzulu DISTINCT:

SELECT DISTINCT zviera, miesto_nalezu
FROM nalez
ORDER BY zviera, miesto_nalezu

Vo výsledku sa budú ignorovať duplicitné riadky a teda získame:

'hroch', 'Devínska Nová Ves'
'hroch', 'Ružinov'
'nosorožec', 'Dúbravka'
'nosorožec', 'Rača'
'pytón', 'Dúbravka'

Ostáva len vyriešiť finálny problém: zistiť koľko zvierat bude v ktorej lokalite.

Agregačné funkcie!

Chceme zoskupiť/zgrupiť záznamy s rovnakým zverom a rovnakou lokáciu a v každej takejto skupine spočítať počet prvkov. Inak povedané, chceme vedieť, že v DNV sa našli dva hrochy, v Ružinove 1, v Dúbravke tri nosorožce (Dúbravka je safari) atď.

'hroch', 'Devínska Nová Ves', 2
'hroch', 'Ružinov', 1
'nosorožec', 'Dúbravka', 3
'nosorožec', 'Rača', 1
'pytón', 'Dúbravka', 1

Na spočítanie použijeme agregačnú funkciu COUNT, ktorý nám zráta počet prvkov v každej skupine. Táto funkcia potrebuje zistiť názov stĺpca, cez ktorý sa bude rátať. V tomto prípade na ňom nezáleží, čiže vieme bez problémov uviesť * (* = “whatever” ;-)

Prvý nástrel síce zafunguje, ale vráti blbosť:

SELECT zviera, miesto_nalezu, COUNT(*)
FROM nalez

Výsledkom je:

'nosorožec', 'Dúbravka', '8'

Možno i vás prekvapili tieto hodnoty. MySQL zobralo prvý riadok a do tretieho stĺpca dopočítalo počet všetkých záznamov v tabuľke.

Zabudli sme totiž zapnúť zoskupovanie/groupovanie. V jednej skupine chceme mať hrochov z Devínskej, v ďalšej zase ružinovský gang hrochov, ďalej dúbravské nosorožce a podobne (a samozrejme osamelého pytóna z Dúbravky). Slovom, v skupine budú zvieratá s rovnakým menom a miestom nálezu.

To môžeme rovno uviesť do klauzuly GROUP BY:

SELECT zviera, miesto_nalezu, COUNT(*)
FROM nalez
GROUP BY zviera, miesto_nalezu; 

a máme, čo sme chceli:

'hroch', 'Devínska Nová Ves', '2'
'hroch', 'Ružinov', '1'
'nosorožec', 'Dúbravka', '3'
'nosorožec', 'Rača', '1'
'pytón', 'Dúbravka', '1'

Možno by sme to mohli trochu preusporiadať a zoradiť:

SELECT miesto_nalezu, zviera, COUNT(*)
FROM nalez
GROUP BY zviera, miesto_nalezu
ORDER BY miesto_nalezu, zviera;

Všimnime si, že hoci sa vymenilo poradie stĺpcov v SELECTe, poradie v GROUP BY nemusíme (ale môžeme vymeniť).

Ak sa nepáči neutešený názov tretieho stĺpca, stačí ho premenovať

SELECT zviera, miesto_nalezu, COUNT(*) as pocet
...

Všimnime si ešte dôležitú vlastnosť: vo výsledku máme len stĺpce, ktoré sú v GROUP BY (teda zviera, miesto_nalezu) alebo len agregované stĺpce (tretí stĺpec). Stĺpce, ktoré nie sú ani také, ani onaké, spôsobia divné správanie. Aha, dodajme do SELECTu aj výpis identifikátora id:

SELECT id, zviera, miesto_nalezu, COUNT(*)
FROM nalez
GROUP BY zviera, miesto_NALEZU;

Výsledok je:

'2', 'hroch', 'Devínska Nová Ves', '2'
'7', 'hroch', 'Ružinov', '1'
'1', 'nosorožec', 'Dúbravka', '3'
'6', 'nosorožec', 'Rača', '1'
'3', 'pytón', 'Dúbravka', '1'

Čísla v id sú prekvapivé: zrejme MySQL zobralo identifikátor z prvého riadka v každej skupine a použilo ho. Máme síce aký taký výsledok, ale či to má nejaký zmysel, ponechám na vás.

Inak babská rada starého databázového administrátora znie:

„Grupujeme podľa všetkých neagregovaných stĺpcov v SELECTe”.

To zodpovedá vyššie uvedenej zásade o dvoch rodinách stĺpcov.

Pozor však, nepokúšajte sa urobiť

SELECT id, zviera, miesto_nalezu, COUNT(*)
FROM nalez
GROUP BY id, zviera, miesto_nalezu;

Vôbec si nepomôžete:

'1', 'nosorožec', 'Dúbravka', '1'
'2', 'hroch', 'Devínska Nová Ves', '1'
'3', 'pytón', 'Dúbravka', '1'
'4', 'nosorožec', 'Dúbravka', '1'
'5', 'nosorožec', 'Dúbravka', '1'
'6', 'nosorožec', 'Rača', '1'
'7', 'hroch', 'Ružinov', '1'
'8', 'hroch', 'Devínska Nová Ves', '1'

Práve sme totiž povedali, že v jednej grupe budú záznamy s rovnakým identifikátorom, zvieraťom a miestom nálezu… čo povedie k jednočlenným grupám.

Počty zvierat v jednotlivých miestach

Ešte by od nás mohol niekto chcieť počty zvierat na jednotlivých miestach. Výsledná tabuľka si vystačí s dvoma stĺpcami: názvom lokácie a počtom zvierat. Počet zvierat zistíme opäť cez COUNT(*).

SELECT miesto_nalezu, COUNT(*) as pocet
FROM nalez
GROUP BY miesto_nalezu

Overenie rady starého databázistu funguje: miesto_nalezu je zgrupované a druhý stĺpec je agregovaný cez GROUP, takže všetko je v poriadku.