SQL: GROUP BY va agregatlar

9-bob 11 kod bloki 1783 so'z

Bitta raqamdan jadvalgacha

8-bobda COUNT(*) bitta raqam berdi: bekor qilinganlar 1 000 ga yaqin. Analitik savoli esa deyarli har doim "... bo'yicha": to'lov usuli bo'yicha, oy bo'yicha, hudud bo'yicha. Bu GROUP BY. U jadvalni guruhlarga bo'ladi va har guruh uchun bitta qator qaytaradi:

SELECT status, COUNT(*) AS soni
FROM orders
GROUP BY status

3 qator, 68 ms

Excel pivot ning Rows va Values qismi aynan shu. Farqi: bu yerda 20 million qator ham bo'lishi mumkin.

Agregat funksiyalar

SELECT
    payment_method,
    COUNT(*)                        AS buyurtmalar,
    SUM(total_amount) / 1000000.0   AS summa_mln,
    ROUND(AVG(total_amount))        AS ortacha_chek,
    MAX(total_amount)               AS eng_katta
FROM orders
WHERE status = 'delivered'
GROUP BY payment_method
ORDER BY summa_mln DESC

4 qator, 34 ms

Bir so'rovda to'rt savolga javob. WHERE guruhlashdan oldin filtrlaydi (faqat yetkazilganlar), ORDER BY natija jadvalini tartiblaydi. ROUND o'rtachadagi kasrni olib tashlaydi: hisobotda 1 816 693.7 emas, 1 816 694.

Qoida: SELECT dagi hamma ustun yo GROUP BY da, yo agregatda

SELECT payment_method, order_id, COUNT(*)      -- XATO
FROM orders
GROUP BY payment_method

order_id guruhda yuzlab qiymat oladi, qaysi birini ko'rsatsin? PostgreSQL xato beradi. SQLite jimgina tasodifiy bittasini beradi: bu yomonroq, chunki xato ko'rinmaydi. Qoida: SELECT dagi har ustun yo GROUP BY da, yo COUNT/SUM/AVG ichida.

Bir nechta ustun bo'yicha

SELECT payment_method, status, COUNT(*) AS soni
FROM orders
GROUP BY payment_method, status
ORDER BY payment_method, status

12 qator, 27 ms

Har juftlik uchun bitta qator: 4 to'lov usuli × 3 holat = 12 qator. Bu 3-bobdagi long shakl. Pivot dagi Rows + Columns ko'rinishi (wide) uchun 13-bobdagi CASE yoki pandas pivot (19-bob).

Hikoya 5: qaysi to'lovda bekor qilish ko'p

Sonlar o'zi javob bermaydi: naqd to'lovda 300 ta bekor, kartada 200 ta, lekin naqd buyurtmalar kamroq. Ulush kerak:

SELECT
    payment_method,
    COUNT(*) AS jami,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS bekor,
    ROUND(100.0 * SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) / COUNT(*), 1) AS bekor_foiz
FROM orders
GROUP BY payment_method
ORDER BY bekor_foiz DESC

4 qator, 25 ms

Naqd to'lovda bekor qilish ulushi boshqalardan taxminan uch barobar yuqori. Bu 1-bobdagi 5-sir. CASE WHEN ... THEN 1 ELSE 0 END ni SUM ichida ishlatish "shartli sanash" degani, Excel dagi COUNTIF ning SQL varianti. 13-bobda CASE batafsil. 100.0 * dagi nuqta: butun songa bo'lish tuzog'i (7-bob).

Bu jadval qaror uchun yetarli emas, lekin savol uchun yetarli: "naqd buyurtmalarni tasdiqlash uchun qo'ng'iroq qilaylikmi?" Analitik ishi shu: raqamdan savolga.

HAVING: guruhlarni filtrlash

WHERE qatorlarni guruhlashdan oldin filtrlaydi. Guruhlarni natijasiga qarab filtrlash uchun HAVING:

SELECT customer_id, COUNT(*) AS buyurtmalar, SUM(total_amount) / 1000000.0 AS summa_mln
FROM orders
WHERE status = 'delivered'
GROUP BY customer_id
HAVING COUNT(*) >= 15
ORDER BY buyurtmalar DESC
LIMIT 10

10 qator, 104 ms

"15 va undan ko'p buyurtma bergan mijozlar." Bu shartni WHERE ga yozib bo'lmaydi: WHERE COUNT(*) >= 15 xato, chunki WHERE paytida guruh hali yo'q. Ikkalasi bir so'rovda: WHERE qatorlar uchun (yetkazilgan), HAVING guruhlar uchun (15+).

Oy bo'yicha: birinchi vaqt qatori

SELECT
    substr(order_date, 1, 7)          AS oy,
    COUNT(*)                          AS buyurtmalar,
    SUM(total_amount) / 1000000.0     AS summa_mln
FROM orders
WHERE status = 'delivered'
GROUP BY oy
ORDER BY oy

12 qator, 179 ms

substr(order_date, 1, 7) sananing birinchi 7 belgisi: '2025-06'. ISO format shuning uchun qulay. Bu 1-bobdagi jadval va 4-bobdagi pivot. Uch vosita, bitta javob: iyun 1 618 mln, dekabr 6 848 mln. Agar uchta vositada uch xil raqam chiqsa, bittasida filtr boshqacha: shu yerda status = 'delivered' ekanini eslang.

GROUP BY oy da alias ishlatildi: SQLite va PostgreSQL ikkalasi ham GROUP BY da alias ni qabul qiladi (WHERE dan farqli). Xavfsiz yo'l: ifodani takrorlash, GROUP BY substr(order_date, 1, 7).

Hikoya 3: o'rtacha chek hudud bo'yicha

orders da hudud yo'q, customers da bor. Ularni bog'lash 10-bob (JOIN). Hozircha hudud o'rniga kanal, u ham customers da... demak u ham 10-bobda. GROUP BY ning haqiqiy kuchi JOIN bilan ochiladi: keyingi bob.

Shu bobda mumkin bo'lgani: kategoriya bo'yicha mahsulotlar:

SELECT
    category,
    COUNT(*)            AS mahsulotlar,
    ROUND(AVG(price))   AS ortacha_narx,
    MIN(price)          AS eng_arzon,
    MAX(price)          AS eng_qimmat,
    SUM(stock)          AS omborda
FROM products
GROUP BY category
ORDER BY ortacha_narx DESC

8 qator, 11 ms

COUNT: yulduzcha, ustun va DISTINCT

Uchta COUNT bor va ular uch xil raqam beradi:

SELECT
    COUNT(*)                     AS qatorlar,
    COUNT(delivery_days)         AS yetkazish_toldirilgan,
    COUNT(DISTINCT customer_id)  AS nechta_mijoz,
    ROUND(AVG(delivery_days), 2) AS ortacha_kun
FROM orders

1 qator, 86 ms

COUNT(*) — qatorlar soni. COUNT(ustun) — shu ustunda qiymati bor qatorlar: 20 000 buyurtmadan 19 235 tasida yetkazish kuni bor, qolgan 765 tasida NULL (bekor qilinganlarda yetkazish bo'lmaydi). COUNT(DISTINCT ...) — takrorlanmaydigan qiymatlar: 20 000 buyurtmani 2 819 mijoz bergan.

Shu yerda jim xato yashiringan: AVG(delivery_days) = 3.99 kun — bu 19 235 buyurtmaning o'rtachasi, 20 000 niki emas. Agregatlar NULL ni tashlab ketadi. Agar "har bir buyurtma bo'yicha o'rtacha" kerak bo'lsa, NULL ni ataylab to'ldiring: AVG(COALESCE(delivery_days, 0)) — lekin avval o'ylang, bekor qilingan buyurtmaning yetkazish kuni nol bo'lishi to'g'rimi yoki uni hisobdan chiqarish kerakmi (13-bob).

Shuning uchun "mijozlar soni" degan savolga javob berishdan oldin so'rang: nechta qator yoki nechta odam?

Nisbatlarning o'rtachasini olmang

Har to'lov usuli bo'yicha bekor qilish foizi:

SELECT
    payment_method,
    COUNT(*) AS buyurtma,
    ROUND(100.0 * SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) / COUNT(*), 2) AS bekor_foiz
FROM orders
GROUP BY payment_method
ORDER BY bekor_foiz DESC

4 qator, 192 ms

Endi savol: do'konning umumiy bekor qilish foizi qancha? To'rt foizning o'rtachasini olish oson, lekin noto'g'ri:

SELECT
    ROUND(AVG(foiz), 2)                  AS foizlar_ortachasi,
    ROUND(100.0 * SUM(bekor) / SUM(n), 2) AS togri_foiz
FROM (
    SELECT
        100.0 * SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) / COUNT(*) AS foiz,
        SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS bekor,
        COUNT(*) AS n
    FROM orders
    GROUP BY payment_method
)

1 qator, 210 ms

4.40% va 3.83% — farq sezilarli. Sabab: cash ning bekor foizi eng yuqori (8.88%), lekin u eng kichik guruh (3 075 buyurtma). Oddiy o'rtacha kichik guruhga katta guruh bilan teng vazn beradi.

Qoida: nisbat har doim jami hisob / jami maxraj. Guruhlarning foizini o'rtachalash kerak bo'lsa, og'irlikli o'rtacha oling. Bu xato hisobotlarda juda ko'p uchraydi — 25-bobda yana ko'ramiz.

Xulosa

Talaba ishi

1. Har status uchun o'rtacha total_amount. Bekor qilinganlar o'rtachasi yetkazilganlardan farq qiladimi? Nega bo'lishi mumkin?
2. Har kategoriya uchun order_items da sotilgan miqdor (SUM(quantity)) — bu so'rovni yozib ko'ring va nima yetishmayotganini ayting (kategoriya qaysi jadvalda?). 10-bobga tayyorgarlik.
3. Hafta kunlari bo'yicha buyurtmalar soni: strftime('%w', order_date) 0 (yakshanba) dan 6 gacha beradi. Qaysi kun eng band?
4. 20 dan ko'p buyurtma bergan mijozlar nechta? (HAVING, keyin tashqi COUNT yoki 11-bobdagi CTE bilan.)