Seskupování dat

Podívejme se nejprve na možnost pokročilé agregace. Mějme tabulku pacientů se sloupci identifikátor pacienta, pohlaví a datumu narození

CREATE TABLE patients

(

     patient_id VARCHAR(10),

     sex VARCHAR(1)  ,

     date_of_birth TIMESTAMP

);

Vložíme několik řádků:

INSERT INTO patients (patient_id, sex, date_of_birth) VALUES ('pat1','F',TO_DATE('2.4.1940','dd.mm.yyyy'));

INSERT INTO patients (patient_id, sex, date_of_birth) VALUES ('pat2','M',TO_DATE('30.3.1950','dd.mm.yyyy'));

INSERT INTO patients (patient_id, sex, date_of_birth) VALUES ('pat3','F',TO_DATE('13.8.1947','dd.mm.yyyy'));

INSERT INTO patients (patient_id, sex, date_of_birth) VALUES ('pat4','M',TO_DATE('23.11.1987','dd.mm.yyyy'));

INSERT INTO patients (patient_id, sex, date_of_birth) VALUES ('pat5','F',TO_DATE('3.9.1975','dd.mm.yyyy'));

Nyní se budeme snažit získat sumární přehled o obsahu tabulky. Zajímá nás, kolik záznamů tabulka obsahuje, kolik je v ní žen kolik mužů a v jakých věkových kategoriích. Počet řádků již zjistit umíme:

SELECT COUNT(*) FROM patients;

Snadno také zjistíme, počet unikátních hodnot v jednotlivých sloupcích:

SELECT COUNT(DISTINCT patient_id),  COUNT(DISTINCT sex),  COUNT(DISTINCT date_of_birth) FROM patients ;

Z výsledku je vidět, že máme tabulku s 5 řádky, kde sloupce patient_id, date_of_birth obsahují vždy unikátní hodnotu, sloupec sex obsahuje jen 2 unikátní hodnoty, buď F nebo M.

Pokud chceme získat přehled, kolik je v tabulce žen a kolik mužů, můžeme sestavit 2 dotazy:

SELECT COUNT(*) FROM patients WHERE sex = 'F';

SELECT COUNT(*) FROM patients WHERE sex = 'M';

SQL standard však nabízí elegantnější způsob, jak tento výsledek získat v jediném dotazu. Slouží k tomu klíčové slovo GROUP BY, za kterým specifikujeme název sloupce nebo sloupců, podle kterých chceme data agregovat. GROUP BY se umísťuje v SQL dotazu za definici podmínky (WHERE), případně za název tabulky, pokud podmínka není specifikována. Počty pacientů dle pohlaví lze získat následovně:

SELECT sex, COUNT(*) FROM patients GROUP BY sex;

Výsledkem jsou 2 řádky (odpovídá počtu unikátních hodnot v agregovaném sloupci). Agregační funkce COUNT počítá řádky zvlášť pro každou kategorii agregovaného sloupce.  Použitím klauzule GROUP BY značně omezujeme možnosti výrazů za klauzulí SELECT. Můžeme zde uvést pouze názvy sloupců uvedených za GROUP BY a agregační funkce. Častou chybou je pokus vložit za SELECT název neagregovaného sloupce:

SELECT sex, date_of_birth, COUNT(*) FROM patients GROUP BY sex;

Tento dotaz nedává logický smysl, databáze neví, jaké datum narození má zobrazit (výsledek agregace jsou dva řádky, tabulka ale obsahuje 5 různých dat narození). Je ale možné požadovat pro každé pohlaví nejstaršího a nejmladšího pacienta:

SELECT sex, MIN(date_of_birth) nejstarsi,  MAX(date_of_birth) nejmladsi, COUNT(*) FROM patients GROUP BY sex;

Agregovat lze podle více sloupců i podle modifikovaných sloupců. Následující dotaz vrátí přehled počtu pacientů agregovaných přes pohlaví a přes dekádu data narození.

SELECT sex, TRUNC(EXTRACT (YEAR FROM date_of_birth)/10) AS dekada, COUNT(*) FROM patients

GROUP BY sex, TRUNC(EXTRACT (YEAR FROM date_of_birth)/10)

Povšimněte si, že výraz, který tvoří agregovaný sloupec za SELECT, musí odpovídat výrazu za GROUP BY. Výraz TRUNC(EXTRACT (YEAR FROM date_of_birth)/10) nejprve extrahuje rok z data narození, tuto hodnotu podělí 10 a funkcí TRUNC provede zaokrouhlení dolů, čímž dostáváme dekádu narození pacienta.

Co v případě, že bychom chtěli ve výsledku vidět jen záznamy s hodnotou COUNT(*) větší než 1? Tuto podmínku nemůžeme specifikovat za klíčové slovo WHERE, protože podmínky za WHERE se aplikují PŘED vlastní agregací na primární data, která do agregace teprve vstupují. Filtrovat agregovaný záznam je možné pomocí HAVING, která se umísťuje za GROUP BY výraz:

SELECT sex, TRUNC(EXTRACT (YEAR FROM date_of_birth)/10) AS dekada, COUNT(*) FROM patients

GROUP BY sex, TRUNC(EXTRACT (YEAR FROM date_of_birth)/10)

HAVING COUNT(*) > 1

Podle výsledku agregačních funkcí je možné i třídit pomocí ORDER BY:

SELECT sex, TRUNC(EXTRACT (YEAR FROM date_of_birth)/10) AS dekada, COUNT(*) FROM patients

GROUP BY sex, TRUNC(EXTRACT (YEAR FROM date_of_birth)/10)

HAVING COUNT(*) > 1

ORDER BY COUNT(*)

Pořadí klíčových slov SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY je dané a nelze je měnit.