CTE — WITH, rekursiv CTE

~25 daq · MySQL · Subquery va CTE

Muammodan boshlaylik

Hisobot: "O'rtacha chekdan katta buyurtma bergan mijozlar — har birining shunday buyurtmalari soni bilan". Subquery lar bilan:

SELECT m.ism, COUNT(*) AS katta_buyurtmalar
FROM mijozlar m
JOIN (
    SELECT b.id, b.mijoz_id, SUM(q.soni * q.narx) AS jami
    FROM buyurtmalar b JOIN buyurtma_qatorlari q ON q.buyurtma_id = b.id
    WHERE b.holat <> 'bekor'
    GROUP BY b.id, b.mijoz_id
) AS cheklar ON cheklar.mijoz_id = m.id
WHERE cheklar.jami > (
    SELECT AVG(jami) FROM (
        SELECT SUM(q.soni * q.narx) AS jami
        FROM buyurtmalar b JOIN buyurtma_qatorlari q ON q.buyurtma_id = b.id
        WHERE b.holat <> 'bekor'
        GROUP BY b.id
    ) AS t
)
GROUP BY m.id, m.ism;

Ishlaydi. Lekin: "cheklar" mantig'i ikki marta yozilgan, so'rov ichkaridan tashqariga o'qiladi, uch qavat qavslar. Bir oydan keyin bu so'rovni o'zgartirish — azob.

CTE (Common Table Expression) — so'rov boshida nomlangan vaqtinchalik natija e'lon qilish. MySQL 8.0 dan beri bor.

WITH — asosiy sintaksis

WITH cheklar AS (
    SELECT b.id, b.mijoz_id, SUM(q.soni * q.narx) AS jami
    FROM buyurtmalar b
    JOIN buyurtma_qatorlari q ON q.buyurtma_id = b.id
    WHERE b.holat <> 'bekor'
    GROUP BY b.id, b.mijoz_id
)
SELECT m.ism, COUNT(*) AS katta_buyurtmalar
FROM cheklar c
JOIN mijozlar m ON m.id = c.mijoz_id
WHERE c.jami > (SELECT AVG(jami) FROM cheklar)
GROUP BY m.id, m.ism;

+-----------------+-------------------+
| ism             | katta_buyurtmalar |
+-----------------+-------------------+
| Malika Rahimova |                 1 |
+-----------------+-------------------+

WITH nom AS (...) — "bundan keyin nom deganimda, shu so'rov natijasini nazarda tutaman"
cheklar ikki marta ishlatildi, lekin bir marta yozildi
So'rov yuqoridan pastga o'qiladi: avval "cheklar nima", keyin "ular bilan nima qilamiz"

CTE — faqat shu so'rov davomida yashaydi. Bazada hech narsa yaratilmaydi.

Bir nechta CTE

Vergul bilan ajratiladi. Keyingi CTE oldingisidan foydalana oladi:

WITH
cheklar AS (
    SELECT b.id, b.mijoz_id, SUM(q.soni * q.narx) AS jami
    FROM buyurtmalar b
    JOIN buyurtma_qatorlari q ON q.buyurtma_id = b.id
    WHERE b.holat <> 'bekor'
    GROUP BY b.id, b.mijoz_id
),
mijoz_statistika AS (
    SELECT mijoz_id, COUNT(*) AS buyurtmalar, SUM(jami) AS jami, AVG(jami) AS ortacha
    FROM cheklar
    GROUP BY mijoz_id
),
segmentlar AS (
    SELECT
        mijoz_id, buyurtmalar, jami,
        CASE
            WHEN jami >= 20000000 THEN 'VIP'
            WHEN jami >= 5000000  THEN 'Faol'
            ELSE 'Oddiy'
        END AS segment
    FROM mijoz_statistika
)
SELECT m.ism, s.buyurtmalar, s.jami, s.segment
FROM segmentlar s
JOIN mijozlar m ON m.id = s.mijoz_id
ORDER BY s.jami DESC;

+-----------------+-------------+----------+---------+
| ism             | buyurtmalar | jami     | segment |
+-----------------+-------------+----------+---------+
| Malika Rahimova |           2 | 21360000 | VIP     |
| Aziz Karimov    |           2 |  8140000 | Faol    |
+-----------------+-------------+----------+---------+

Har bir CTE — bitta mantiqiy qadam, o'z nomi bilan. Murakkab hisobot — oddiy qadamlar zanjiri. Xatoni qidirishda har bir qadamni alohida tekshirish oson: oxirgi SELECT ni SELECT * FROM mijoz_statistika ga almashtiring.

(CASE — 28-darsda.)

CTE yoki subquery?

	Subquery (derived table)	CTE
Qayta ishlatish	Har safar qayta yozish	Bir marta e'lon, ko'p ishlatish
O'qish yo'nalishi	Ichkaridan tashqariga	Yuqoridan pastga
Rekursiya	❌	✅
Tezlik	Odatda bir xil	Odatda bir xil

Oddiy, bir martalik ichki so'rov — subquery yetarli. Ikkidan ortiq qadam yoki takrorlanish bo'lsa — CTE.

Rekursiv CTE

CTE ning eng kuchli imkoniyati — o'ziga o'zi murojaat qilish. Bu ierarxik ma'lumotlar (daraxtlar) va ketma-ketliklar uchun.

Sonlar ketma-ketligi

WITH RECURSIVE sonlar AS (
    SELECT 1 AS n                -- 1. boshlanish (anchor)
    UNION ALL
    SELECT n + 1 FROM sonlar     -- 2. rekursiv qadam
    WHERE n < 5                  -- 3. to'xtash sharti
)
SELECT n FROM sonlar;
-- 1, 2, 3, 4, 5

Qanday ishlaydi:

1-qadam (anchor):    n = 1
2-qadam:             1 + 1 = 2   (1 < 5 ✅)
3-qadam:             2 + 1 = 3
4-qadam:             3 + 1 = 4
5-qadam:             4 + 1 = 5
6-qadam:             5 < 5 ❌ → yangi qator yo'q → to'xtaydi

Amaliy misol: oyning har bir kuni

"Noyabrdagi har bir kun uchun savdo — savdo bo'lmagan kunlar 0 bilan":

WITH RECURSIVE kunlar AS (
    SELECT DATE('2026-10-01') AS kun
    UNION ALL
    SELECT kun + INTERVAL 1 DAY FROM kunlar WHERE kun < '2026-10-31'
)
SELECT k.kun, COUNT(b.id) AS buyurtmalar
FROM kunlar k
LEFT JOIN buyurtmalar b ON DATE(b.yaratilgan) = k.kun
GROUP BY k.kun
ORDER BY k.kun;

Faqat buyurtmalar dan guruhlasak — buyurtmasiz kunlar umuman chiqmaydi va grafikda "teshik" paydo bo'ladi. Kunlar ro'yxatini yasab, LEFT JOIN qilish — bu muammoning standart yechimi.

Ierarxiya: barcha bo'ysunuvchilar

17-darsdagi xodimlar jadvali. SELF JOIN faqat bir darajani ko'rsatardi. Bekzodning (direktor) barcha bo'ysunuvchilari — har qanday chuqurlikda:

WITH RECURSIVE jamoa AS (
    -- anchor: boshlang'ich odam
    SELECT id, ism, lavozim, rahbar_id, 0 AS daraja
    FROM xodimlar
    WHERE id = 1

    UNION ALL

    -- rekursiv: jamoadagi har bir odamning to'g'ridan-to'g'ri bo'ysunuvchilari
    SELECT x.id, x.ism, x.lavozim, x.rahbar_id, j.daraja + 1
    FROM xodimlar x
    JOIN jamoa j ON x.rahbar_id = j.id
)
SELECT CONCAT(REPEAT('    ', daraja), ism) AS tuzilma, lavozim
FROM jamoa
ORDER BY daraja, ism;

+----------------+---------------+
| tuzilma        | lavozim       |
+----------------+---------------+
| Bekzod         | Direktor      |
|     Gulnora    | Savdo bo'limi |
|     Sardor     | IT bo'limi    |
|         Kamola | Sotuvchi      |
|         Otabek | Dasturchi     |
|         Zarina | Dasturchi     |
+----------------+---------------+

Teskari yo'nalish — Otabekdan yuqoriga, barcha rahbarlari:

WITH RECURSIVE zanjir AS (
    SELECT id, ism, rahbar_id FROM xodimlar WHERE id = 5
    UNION ALL
    SELECT x.id, x.ism, x.rahbar_id
    FROM xodimlar x
    JOIN zanjir z ON x.id = z.rahbar_id
)
SELECT ism FROM zanjir;
-- Otabek, Sardor, Bekzod

Xuddi shu naqsh: ichma-ich toifalar (Elektronika → Telefonlar → Smartfonlar), izohlarga javoblar daraxti, sayt menyusi.

Cheksiz rekursiyadan himoya

To'xtash sharti unutilsa yoki ma'lumotda sikl bo'lsa (A ning rahbari B, B ning rahbari A):

ERROR 3636: Recursive query aborted after 1001 iterations.
Try increasing @@cte_max_recursion_depth to a larger value.

MySQL standart holatda 1000 qadamdan keyin to'xtatadi. Haqiqatan ko'proq kerak bo'lsa (masalan, 3 yillik kunlar):

SET SESSION cte_max_recursion_depth = 5000;

Nima uchun shunday?

Nima uchun rekursiv CTE da UNION ALL ishlatiladi, UNION emas?

UNION har qadamda takrorlarni qidirib olib tashlaydi — bu qo'shimcha ish va ko'pincha keraksiz. UNION ALL esa natijalarni shunchaki ketma-ket qo'shadi — tezroq. Lekin ma'lumotda sikl bo'lishi mumkin bo'lsa (graf, daraxt emas), UNION takror qatorlarni tashlab, cheksiz aylanishni to'xtatishga yordam beradi. Daraxtlar uchun — UNION ALL, sikl bo'lishi mumkin bo'lgan graflar uchun — UNION yoki qo'shimcha "ko'rilganlar" tekshiruvi.

Xulosa

Eslab qoling:

WITH nom AS (...) SELECT ... — nomlangan vaqtinchalik natija
Bir nechta CTE — vergul bilan; keyingisi oldingisidan foydalanadi
Murakkab hisobot — oddiy nomlangan qadamlar zanjiri
WITH RECURSIVE — anchor UNION ALL rekursiv qadam + to'xtash sharti
Ishlatilishi: sanalar ketma-ketligi, ierarxiya (xodimlar, toifalar, izohlar)
Standart chegara — 1000 qadam: cte_max_recursion_depth
MySQL 8.0+ kerak

Keyingi dars

24-dars: UNION, UNION ALL, INTERSECT, EXCEPT