SQL v praxi

Agregace

Zatím jsme dostávali řádky tak, jak v tabulce leží. Často ale chceme jedno číslo: kolik toho je, kolik to dělá dohromady, kolik je to v průměru. Výpočtu, který z mnoha řádků udělá jednu hodnotu, se říká agregace.

Agregační funkce

SELECT COUNT(*), MIN(price), MAX(price), AVG(price)
FROM products;

COUNT(*) spočítá řádky, SUM sečte hodnoty sloupce, AVG z nich udělá průměr a MIN a MAX najdou nejmenší a největší. Z osmnácti produktů vyjde jediný řádek.

Výsledné sloupce se jmenují podle výrazu, třeba AVG(price). Hezčí jméno jim dáme přes AS a funkce ROUND průměr zaokrouhlí na celé koruny:

SELECT COUNT(*) AS products_count, ROUND(AVG(price)) AS average_price
FROM products
WHERE category = 'tenisky';

COUNT jde použít i se sloupcem. Liší se něčím od COUNT(*)?

> SELECT COUNT(*), COUNT(city) FROM customers;
???

Výsledek schválně vynecháváme, ať si dotaz zkusíte sami. Nápověda: vzpomeňte si na zákazníka bez města.

Seskupování

SELECT category, COUNT(*) AS products_count, ROUND(AVG(price)) AS average_price
FROM products
GROUP BY category;

GROUP BY rozdělí řádky do skupin podle hodnoty sloupce a agregační funkce pak počítá pro každou skupinu zvlášť. Místo jednoho řádku dostaneme jeden řádek za každou kategorii. Funguje to jako třídění prádla: nejdřív ho rozházíme na hromádky podle barvy a teprve potom spočítáme, kolik kusů je na které.

Do SELECT patří jen sloupce, podle kterých seskupujeme, a agregační funkce. U ostatních sloupců by nebylo jasné, kterou z hodnot ve skupině vypsat. SQLite je v tom benevolentní a nějakou vybere, PostgreSQL takový dotaz rovnou odmítne.

Agregace jde spojit s JOINem. Kolik objednávek má který zákazník?

SELECT c.id, c.name, COUNT(o.id) AS orders_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY orders_count DESC;

Seskupujeme podle id, ne jen podle jména. V obchodě nakupují dva různí zákazníci jménem Jan Novák a seskupení podle jména by je slilo do jednoho. A počítáme COUNT(o.id), ne COUNT(*): zákazník bez objednávek má po LEFT JOIN jeden řádek s o.id prázdným, a ten se počítat nemá.

Hodnota objednávky je součet cen jejích položek:

SELECT order_id, SUM(quantity * price) AS total
FROM order_items
GROUP BY order_id;

Filtrování skupin

WHERE vybírá řádky ještě před seskupením, takže o součtech a průměrech nic neví. Na filtrování hotových skupin je HAVING:

SELECT category, ROUND(AVG(price)) AS average_price
FROM products
GROUP BY category
HAVING AVG(price) > 1000;

U řazení jsme viděli, že části dotazu mají pevné pořadí. Teď ho můžeme doplnit o všechny, které známe. Dotaz je nemusí obsahovat všechny, ale ty, které v něm jsou, jdou za sebou takhle:

SELECT    sloupce a agregační funkce
FROM      tabulka
JOIN      další tabulka ON podmínka spojení
WHERE     podmínka pro řádky
GROUP BY  sloupce skupin
HAVING    podmínka pro skupiny
ORDER BY  řazení
LIMIT     počet řádků