Aggregáció és GROUP BY

1Mik az aggregáló függvények?

Az aggregáló (összesítő) függvények sok sorból egyetlen értéket számolnak ki. Ahelyett, hogy az egyes termékeket látnánk, a függvény az egész halmazról ad egy összesített számot (darab, összeg, átlag…).

-- Egyetlen összesített sor a teljes táblából:
SELECT COUNT(*) AS termekek_szama,
       AVG(ar)  AS atlagar,
       MAX(ar)  AS legdragabb
FROM termek;

2COUNT — a leggyakoribb

A COUNT sorokat számol, de fontos a háromféle változata:

-- Hány termékhez van egyáltalán megadva kategória? (a NULL-okat kihagyja)
SELECT COUNT(kategoria_id) FROM termek;

-- Hány KÜLÖNBÖZŐ kategóriában van termék?
SELECT COUNT(DISTINCT kategoria_id) FROM termek;

3SUM, AVG, MIN, MAX

Ezek számértékeken dolgoznak (a MIN/MAX szövegen és dátumon is). A NULL értékeket figyelmen kívül hagyják.

-- A 3-as kategória árstatisztikái:
SELECT SUM(keszlet) AS ossz_keszlet,
       AVG(ar)     AS atlagar,
       MIN(ar)     AS legolcsobb,
       MAX(ar)     AS legdragabb
FROM termek
WHERE kategoria_id = 3;

ROUND — kerekítés

Az AVG gyakran sok tizedesjegyet ad (pl. 73052.5). A ROUND(érték, tizedesjegyek) függvény a kívánt pontosságra kerekít. A második paraméter a megtartandó tizedesjegyek száma — ha elhagyod, egész számra kerekít.

-- Átlagár egész forintra kerekítve:
SELECT ROUND(AVG(ar)) AS atlagar FROM termek;

-- Ugyanaz, két tizedesjegyre kerekítve:
SELECT ROUND(AVG(ar), 2) AS atlagar FROM termek;

-- A ROUND önállóan is működik, nem csak aggregátumon (pl. bruttó ár):
SELECT nev, ROUND(ar * 1.27) AS brutto FROM termek;

4GROUP BY — csoportosítás

Eddig a teljes táblát összesítettük egyetlen sorba. A GROUP BY csoportokra bontja a sorokat (pl. kategóriánként), és az aggregáló függvény minden csoportra külön lefut. Így kapunk csoportonkénti darabszámot, átlagot stb.

-- Hány termék van az egyes kategóriákban?
SELECT kategoria_id, COUNT(*) AS db
FROM termek
GROUP BY kategoria_id;

-- Átlag-, min- és maxár kategóriánként:
SELECT kategoria_id,
       COUNT(*)      AS db,
       ROUND(AVG(ar)) AS atlagar,
       MIN(ar)        AS legolcsobb,
       MAX(ar)        AS legdragabb
FROM termek
GROUP BY kategoria_id;

5A SELECT-lista aranyszabálya

Ha van GROUP BY, akkor a SELECT-ben szereplő minden oszlop vagy szerepel a GROUP BY-ban, vagy aggregáló függvényben van. Különben a szerver nem tudja, melyik értéket mutassa a csoportból.

-- HIBÁS: a nev nincs sem a GROUP BY-ban, sem aggregálva
SELECT kategoria_id, nev, COUNT(*)
FROM termek
GROUP BY kategoria_id;   -- melyik nevet mutassa a sok termékből??

-- HELYES: csak csoportosító oszlop + aggregátum
SELECT kategoria_id, COUNT(*) AS db
FROM termek
GROUP BY kategoria_id;

6HAVING — csoportok szűrése

A WHERE az egyes sorokat szűri — de aggregátumra (pl. COUNT(*) > 2) nem hivatkozhatunk benne. Erre való a HAVING: a csoportokat szűri, az aggregálás után.

-- Mely kategóriákban van 1-nél több termék?
SELECT kategoria_id, COUNT(*) AS db
FROM termek
GROUP BY kategoria_id
HAVING COUNT(*) > 1;

-- Mely kategóriák átlagára haladja meg a 20 000 Ft-ot?
SELECT kategoria_id, ROUND(AVG(ar)) AS atlagar
FROM termek
GROUP BY kategoria_id
HAVING AVG(ar) > 20000;

7WHERE vs HAVING

A kettő együtt is használható: előbb a WHERE kiszűri a nem kívánt sorokat, majd a GROUP BY csoportosít, végül a HAVING szűri a csoportokat.

-- Csak a készleten lévő termékeket nézzük (WHERE), majd a 8000 Ft feletti
-- átlagárú kategóriákat tartjuk meg (HAVING):
SELECT kategoria_id, COUNT(*) AS db, ROUND(AVG(ar)) AS atlagar
FROM termek
WHERE keszlet > 0
GROUP BY kategoria_id
HAVING AVG(ar) > 8000;

8Élő SQL-gyakorló

Összesíts a webshopon! A chipek aggregáló és csoportosító mintákat töltenek be — futtasd, majd alakítsd át (pl. más oszlop szerint csoportosíts, vagy módosítsd a HAVING feltételt).

9Feladatok

SELECT kategoria_id, COUNT(*) AS db
FROM termek
GROUP BY kategoria_id;

SELECT kategoria_id, nev, COUNT(*)
FROM termek
GROUP BY kategoria_id;

SELECT kategoria_id, COUNT(*) FROM termek GROUP BY kategoria_id HAVING COUNT(*) > 1;
SELECT kategoria_id, COUNT(*) FROM termek WHERE keszlet > 0 GROUP BY kategoria_id;

10Önellenőrző kvíz

11Fogalomtár

12Összegzés