Aggregate funksiyalar, GROUP BY, HAVING, FILTER

~20 daq · PostgreSQL · DDL va DML

Sizda 50,000 buyurtma bor. Savol: jami nechta buyurtma? Eng katta summa qancha? O'rtacha narx? Kategoriya bo'yicha nechta mahsulot bor?

Bular uchun har bir qatorni o'qib hisoblash shart emas — aggregate funksiyalar bir so'rovda butun jadval bo'yicha hisob-kitob qiladi.

Aggregate funksiyalar

SELECT COUNT(*)           FROM buyurtmalar;  -- qatorlar soni
SELECT SUM(miqdor)        FROM buyurtmalar;  -- jami summa
SELECT AVG(miqdor)        FROM buyurtmalar;  -- o'rtacha summa
SELECT MIN(miqdor)        FROM buyurtmalar;  -- eng kichik
SELECT MAX(miqdor)        FROM buyurtmalar;  -- eng katta

┌───────────┬────────────────────────────────────────┐
│  Funksiya │             Nima qiladi                │
├───────────┼────────────────────────────────────────┤
│ COUNT(*)  │ Jami qatorlar soni                     │
│ COUNT(u)  │ NULL bo'lmagan qatorlar soni            │
│ SUM(u)    │ Yig'indi                               │
│ AVG(u)    │ O'rtacha qiymat                        │
│ MIN(u)    │ Eng kichik qiymat                      │
│ MAX(u)    │ Eng katta qiymat                       │
└───────────┴────────────────────────────────────────┘

COUNT(*) va COUNT(ustun) farqi:

SELECT COUNT(*)       FROM foydalanuvchilar;  -- hammasi: 5
SELECT COUNT(telefon) FROM foydalanuvchilar;  -- telefon NULL bo'lmaganlar: 3

GROUP BY — guruhlab hisoblash

Aggregate funksiyalar yolg'iz ishlaganda — butun jadval bo'yicha bitta natija qaytaradi. GROUP BY bilan esa — har guruh uchun alohida natija:

SELECT kategoriya, COUNT(*), AVG(narx)
FROM mahsulotlar
GROUP BY kategoriya;

--   kategoriya   | count |    avg
-- ---------------+-------+-----------
--  elektronika   |    15 | 4500000.00
--  kiyim         |    32 |  250000.00
--  oziq-ovqat    |    48 |   35000.00

Hayotiy o'xshatma

Sinfda o'quvchilar bor. Har o'quvchining bahosi bor. GROUP BY — "har sinf bo'yicha o'rtacha baho". Sinf — guruh, o'rtacha baho — aggregate.

Bir nechta ustun bo'yicha guruhlash:

SELECT yil, oy, COUNT(*) AS buyurtmalar_soni, SUM(miqdor) AS jami
FROM buyurtmalar
GROUP BY yil, oy
ORDER BY yil, oy;

--  yil  | oy | buyurtmalar_soni |    jami
-- ------+----+------------------+------------
--  2024 |  1 |              142 | 58300000.00
--  2024 |  2 |              189 | 74200000.00
--  2024 |  3 |              201 | 81500000.00

HAVING — guruhlarni filtrlash

WHERE — qatorlarni filtrlaydi (GROUP BY dan oldin). HAVING — guruhlarni filtrlaydi (GROUP BY dan keyin).

-- Kamida 10 ta mahsuloti bor kategoriyalar
SELECT kategoriya, COUNT(*) AS soni
FROM mahsulotlar
GROUP BY kategoriya
HAVING COUNT(*) >= 10;

WHERE va HAVING birga:

-- Faol mahsulotlar orasida, jami narxi 1,000,000 dan katta kategoriyalar
SELECT kategoriya, SUM(narx) AS jami_narx
FROM mahsulotlar
WHERE faol = TRUE          -- ← avval faol bo'lmaganlar chiqariladi
GROUP BY kategoriya
HAVING SUM(narx) > 1000000 -- ← keyin guruhlar filtrlanadi
ORDER BY jami_narx DESC;

AS — ustun nomini o'zgartirish

SELECT
    kategoriya,
    COUNT(*)        AS mahsulotlar_soni,
    ROUND(AVG(narx), 2) AS ortacha_narx,
    MAX(narx)       AS eng_qimmat
FROM mahsulotlar
GROUP BY kategoriya;

FILTER — shartli aggregate

Ba'zan bir so'rovda turli shartlar bo'yicha hisoblash kerak:

SELECT
    COUNT(*)                              AS jami,
    COUNT(*) FILTER (WHERE faol = TRUE)   AS faol,
    COUNT(*) FILTER (WHERE faol = FALSE)  AS nofaol,
    AVG(narx) FILTER (WHERE narx > 0)     AS ortacha_narx
FROM mahsulotlar;

--  jami | faol | nofaol | ortacha_narx
-- ------+------+--------+--------------
--    95 |   78 |     17 |   1250000.00

FILTER olmasa — alohida uchta so'rov yozishga to'g'ri kelar edi.

Amaliy misol — savdo hisoboti

SELECT
    f.ism                               AS foydalanuvchi,
    COUNT(b.id)                         AS buyurtmalar,
    SUM(b.miqdor)                       AS jami_summa,
    ROUND(AVG(b.miqdor), 2)             AS ortacha,
    MAX(b.miqdor)                       AS eng_katta,
    MAX(b.sana)                         AS oxirgi_buyurtma
FROM foydalanuvchilar f
JOIN buyurtmalar b ON b.foydalanuvchi_id = f.id
GROUP BY f.id, f.ism
HAVING COUNT(b.id) >= 3          -- kamida 3 ta buyurtma
ORDER BY jami_summa DESC
LIMIT 10;                        -- top 10

Tez-tez uchraydigan xatolar

SELECT da GROUP BY ga kirmagan ustun

-- ❌ — 'ism' GROUP BY da yo'q, aggregate ham emas
SELECT kategoriya, ism, COUNT(*)
FROM mahsulotlar
GROUP BY kategoriya;
-- ERROR: column "mahsulotlar.ism" must appear in GROUP BY or aggregate function

-- ✅
SELECT kategoriya, COUNT(*)
FROM mahsulotlar
GROUP BY kategoriya;

WHERE da aggregate ishlatish

-- ❌ — WHERE aggregate ni bilmaydi
SELECT kategoriya, COUNT(*)
FROM mahsulotlar
WHERE COUNT(*) > 5
GROUP BY kategoriya;
-- ERROR: aggregate functions are not allowed in WHERE

-- ✅ — buning uchun HAVING
SELECT kategoriya, COUNT(*)
FROM mahsulotlar
GROUP BY kategoriya
HAVING COUNT(*) > 5;

AVG — NULL qiymatlar

AVG NULL qiymatlarni hisobga olmaydi. Bu ba'zan kutilmagan natija beradi:

-- 5 qator: 100, 200, NULL, NULL, NULL
SELECT AVG(baho) FROM natijalar;
-- Natija: 150 — faqat 2 ta qiymat bo'yicha hisoblandi
-- NULL larni 0 deb hisoblash kerak bo'lsa:
SELECT AVG(COALESCE(baho, 0)) FROM natijalar;
-- Natija: 60

Xulosa

Aggregate funksiyalar — COUNT, SUM, AVG, MIN, MAX — butun jadval yoki guruh bo'yicha hisob-kitob.

GROUP BY — ma'lumotlarni guruhlaydi, har guruh uchun alohida aggregate hisoblaydi.

HAVING — guruhlarni filtrlaydi. WHERE qatorlarni, HAVING guruhlarni filtrlaydi.

FILTER — bitta so'rovda turli shartlar bo'yicha aggregate hisoblash.

Keyingi dars

12-dars: INNER, LEFT, RIGHT, FULL, CROSS, SELF JOIN