CTE — WITH, rekursiv CTE, materialized CTE

~25 daq · PostgreSQL · Ilg'or SQL

Muammodan boshlaylik

Murakkab so'rov yozasiz: avval foydalanuvchilarni filtrlab, ularning buyurtma statistikasini hisoblaysiz, keyin shu statistikadan yana filtraysiz. Subquery bilan yozilsa — so'rov ichida so'rov, u ichida yana so'rov. O'qiganda qayerdan boshlashni bilmaysiz.

SELECT ism, jami FROM (
  SELECT f.ism, SUM(b.summa) AS jami
  FROM (SELECT * FROM foydalanuvchilar WHERE shahar = 'Toshkent') AS f
  JOIN buyurtmalar b ON f.id = b.foydalanuvchi_id
  GROUP BY f.id, f.ism
) AS stats
WHERE jami > 1000000;

Ishlaydi — lekin o'qish qiyin. Ichidan tashqariga qarab tushunish kerak.

CTE bu muammoni hal qiladi: murakkab so'rovni qadamma-qadam, nom berib yozish.

CTE nima?

CTE (Common Table Expression) — WITH kalit so'zi bilan belgilanadigan vaqtinchalik nomlangan so'rov. U asosiy so'rovdan oldin yoziladi va o'sha so'rov ichida oddiy jadval kabi ishlatiladi.

Hayotiy o'xshatma: oshpaz va retsept. Murakkab taom pishirishda oshpaz har bir qadamni alohida tayyorlaydi — avval sous, alohida go'sht, alohida sabzavotlar. Keyin barchasini birlashtiradi. CTE — shu "alohida tayyorlash" qadamlari. Har birining nomi bor, birini boshqasida ishlatish mumkin.

WITH
  qadam1 AS (...),   ← birinchi tayyor
  qadam2 AS (...),   ← qadam1 dan foydalanishi mumkin
  qadam3 AS (...)    ← qadam1, qadam2 dan foydalanishi mumkin
SELECT ... FROM qadam3;

Asosiy CTE

Yuqoridagi murakkab subquery CTE bilan:

WITH
  toshkentliklar AS (
    SELECT id, ism
    FROM foydalanuvchilar
    WHERE shahar = 'Toshkent'
  ),
  statistika AS (
    SELECT
      t.ism,
      SUM(b.summa) AS jami
    FROM toshkentliklar t
    JOIN buyurtmalar b ON t.id = b.foydalanuvchi_id
    GROUP BY t.id, t.ism
  )
SELECT ism, jami
FROM statistika
WHERE jami > 1000000;

Natija bir xil — lekin o'qish ancha oson. Har bir qadam nomi bilan ajratilgan, qaysi qadam nima qilayotgani tushunish mumkin.

toshkentliklar → Toshkentdagi foydalanuvchilarni ajratadi
statistika     → ularning buyurtma summalarini hisoblaydi
asosiy so'rov  → 1 000 000 dan yuqorilarini filtrlab chiqaradi

CTE ni qachon ishlatish kerak?

CTE har doim ham kerak emas. Oddiy so'rovda CTE ortiqcha. Quyidagi hollarda foydali:

So'rov bir necha bosqichdan iborat bo'lganda — har bir bosqichni nom bilan ajratish o'qilishni osonlashtiradi.

Bir xil subquery bir necha joyda kerak bo'lganda — CTE bir marta yoziladi, bir necha joyda ishlatiladi:

WITH eng_yaxshi AS (
  SELECT foydalanuvchi_id
  FROM buyurtmalar
  GROUP BY foydalanuvchi_id
  HAVING SUM(summa) > 5000000
)
SELECT f.ism, b.mahsulot
FROM foydalanuvchilar f
JOIN buyurtmalar b    ON f.id = b.foydalanuvchi_id
JOIN eng_yaxshi ey    ON f.id = ey.foydalanuvchi_id;
--   ↑ CTE ikki joyda ishlatilishi mumkin edi

Rekursiv CTE

Bu CTE ning eng kuchli tomoni. Rekursiv CTE — o'z ichida o'zini chaqiradi. Ierarxik ma'lumotlar uchun — daraxt tuzilmalar, kategoriyalar, xodim-rahbar zanjiri.

Avval tuzilmani tasavvur qiling:

Direktor (id=1, rahbar_id=NULL)
  └── Bo'lim boshlig'i (id=2, rahbar_id=1)
        ├── Xodim A (id=3, rahbar_id=2)
        └── Xodim B (id=4, rahbar_id=2)
              └── Stajyor (id=5, rahbar_id=4)

Bu ma'lumot bir jadvalda saqlanadi:

CREATE TABLE xodimlar (
  id        INT PRIMARY KEY,
  ism       TEXT,
  rahbar_id INT REFERENCES xodimlar(id)
);

INSERT INTO xodimlar VALUES
  (1, 'Kamol',   NULL),
  (2, 'Jasur',   1),
  (3, 'Malika',  2),
  (4, 'Sherzod', 2),
  (5, 'Dilnoza', 4);

Direktordan boshlab barcha qo'l ostidagilarni topish — oddiy JOIN bilan imkonsiz, chunki qancha daraja chuqur ekanini bilmaymiz.

Rekursiv CTE:

WITH RECURSIVE xodimlar_daraxti AS (

  -- Boshlang'ich qism: direktorni olamiz
  SELECT id, ism, rahbar_id, 0 AS daraja
  FROM xodimlar
  WHERE rahbar_id IS NULL

  UNION ALL

  -- Rekursiv qism: har bir xodimning qo'l ostidagilarini qo'shamiz
  SELECT x.id, x.ism, x.rahbar_id, xd.daraja + 1
  FROM xodimlar x
  JOIN xodimlar_daraxti xd ON x.rahbar_id = xd.id
  -- ↑ CTE ning o'ziga murojaat

)
SELECT
  REPEAT('  ', daraja) || ism AS tuzilma,  -- ← darajaga qarab bo'shliq
  daraja
FROM xodimlar_daraxti
ORDER BY daraja, ism;

 tuzilma            │ daraja
--------------------+--------
 Kamol              │ 0
   Jasur            │ 1
     Malika         │ 2
     Sherzod        │ 2
       Dilnoza      │ 3

Rekursiv CTE qanday ishlaydi:

1-bosqich:  Kamol (daraja=0)          ← boshlang'ich qism
2-bosqich:  Jasur (daraja=1)          ← Kamolning qo'l ostidag'i
3-bosqich:  Malika, Sherzod (2)       ← Jasurning qo'l ostidagilar
4-bosqich:  Dilnoza (3)               ← Sherzodning qo'l ostidag'i
5-bosqich:  natija yo'q → to'xtaydi

Rekursiv CTE tuzilmasi

WITH RECURSIVE cte_nomi AS (

  -- 1. Boshlang'ich qism (bir marta ishlaydi)
  SELECT ...
  FROM jadval
  WHERE boshlang'ich_shart

  UNION ALL

  -- 2. Rekursiv qism (natija bo'lguncha ishlaydi)
  SELECT ...
  FROM jadval
  JOIN cte_nomi ON bog'lanish_sharti  -- ← o'ziga murojaat

)
SELECT * FROM cte_nomi;

UNION ALL — ikki qismni birlashtiradi. Rekursiv qism bo'sh natija qaytarguncha davom etadi.

⚠️ Diqqat: Rekursiv CTE cheksiz aylanib qolishi mumkin — agar tsikl bo'lsa (A → B → A). PostgreSQL standart holatda 100 daraja chuqurlikdan keyin to'xtatadi. SET max_recursion_depth bilan o'zgartirish mumkin.

Materialized CTE

Oddiy CTE — ba'zan PostgreSQL uni optimize qilib, har safar qayta hisoblaydi. MATERIALIZED kalit so'zi CTE natijasini bir marta hisoblash va keshda saqlashni buyuradi.

WITH katta_buyurtmalar AS MATERIALIZED (
  SELECT * FROM buyurtmalar WHERE summa > 1000000
)
SELECT * FROM katta_buyurtmalar WHERE mahsulot LIKE 'N%';

MATERIALIZED qachon foydali:

CTE bir necha joyda ishlatilganda   → bir marta hisob, ko'p marta foydalanish
Ichki so'rov og'ir hisoblashda      → natija keshlanadi

NOT MATERIALIZED — aksincha, PostgreSQL ni optimize qilishga ruxsat beradi:

WITH oddiy AS NOT MATERIALIZED (
  SELECT * FROM foydalanuvchilar
)
SELECT * FROM oddiy WHERE id = 1;
-- PostgreSQL bu yerda CTE ni inline qilib, indeks ishlatishi mumkin

CTE va subquery — qaysi biri?

Subquery yaxshi:
  → Oddiy, bir martalik filtr
  → So'rov qisqa va tushunish oson

CTE yaxshi:
  → Ko'p bosqichli so'rov
  → Bir xil subquery bir necha joyda kerak
  → Rekursiv ma'lumotlar (daraxt, ierarxiya)
  → So'rovni qadamma-qadam tushuntirish kerak

Ikkalasi ko'pincha bir xil natija beradi. CTE — o'qilishi oson, subquery — ba'zan tezroq (PostgreSQL optimizatsiyasi tufayli).

Tez-tez uchraydigan xatolar

Rekursiv CTE da RECURSIVE kalit so'zini unutish:

-- ❌ — RECURSIVE yo'q — xato beradi
WITH daraxt AS (
  SELECT id FROM xodimlar WHERE rahbar_id IS NULL
  UNION ALL
  SELECT x.id FROM xodimlar x JOIN daraxt d ON x.rahbar_id = d.id
)

-- ✅
WITH RECURSIVE daraxt AS ( ... )

RECURSIVE bo'lmasa PostgreSQL CTE ni oddiy deb hisoblaydi — o'z ichida o'ziga murojaat qilishga ruxsat bermaydi.

Cheksiz rekursiya:

-- ❌ — ma'lumotda tsikl bo'lsa (id=1 rahbari id=2, id=2 rahbari id=1)
-- PostgreSQL 100 darajadan keyin xato beradi:
-- ERROR: infinite recursion detected in policy for relation

Ishonchli bo'lish uchun chuqurlikni cheklash:

WITH RECURSIVE daraxt AS (
  SELECT id, 0 AS daraja FROM xodimlar WHERE rahbar_id IS NULL
  UNION ALL
  SELECT x.id, d.daraja + 1
  FROM xodimlar x JOIN daraxt d ON x.rahbar_id = d.id
  WHERE d.daraja < 10  -- ← 10 darajadan chuqurga tushmasin
)

Xulosa

CTE — murakkab so'rovni o'qilishi oson qadamlarga bo'lish usuli.

WITH
  1_qadam AS (...),
  2_qadam AS (...),
SELECT ... FROM 2_qadam;

Uch tur:

Tur	Qachon
Oddiy CTE	Ko'p bosqichli so'rov, takroriy subquery
Rekursiv CTE	Daraxt, ierarxiya, cheksiz chuqurlikdagi ma'lumotlar
Materialized CTE	CTE bir necha joyda — bir marta hisoblash

CTE so'rovni tushunarsiz subquery uyumidan tushunarli qadamlarga aylantiradi. Ayniqsa rekursiv CTE — ierarxik ma'lumotlarsiz muqobili yo'q.

Keyingi dars

18-dars: Window Functions — ROW_NUMBER, RANK, DENSE_RANK, NTILE