CTE (WITH) — so'rovni bosqichlarga bo'lish

So'rov uch-to'rt qavat subquery ga aylanganda uni o'qish ham, tuzatish ham qiyinlashadi. CTE (Common Table Expression) — so'rov boshida WITH bilan e'lon qilinadigan nomlangan vaqtinchalik natija. U muammoni bosqichlarga bo'lib, har biriga tushunarli nom berishga imkon beradi.

Jadval: orders

Vazifa

Sentabr oyida jami xaridi o'rtacha mijoz xarididan ko'p bo'lgan mijozlar.

Subquery bilan:

SELECT customer, total
FROM (SELECT customer, SUM(amount) AS total
      FROM orders
      WHERE order_date >= '2026-09-01' AND order_date < '2026-10-01'
      GROUP BY customer) t
WHERE total > (SELECT AVG(total)
               FROM (SELECT customer, SUM(amount) AS total
                     FROM orders
                     WHERE order_date >= '2026-09-01' AND order_date < '2026-10-01'
                     GROUP BY customer) t2);

Ishlaydi, lekin bir xil hisob ikki marta yozilgan va mantiqni topish qiyin.

Xuddi shu narsa CTE bilan

WITH september AS (
  SELECT customer, SUM(amount) AS total
  FROM orders
  WHERE order_date >= '2026-09-01' AND order_date < '2026-10-01'
  GROUP BY customer
),
average AS (
  SELECT AVG(total) AS avg_total FROM september
)
SELECT s.customer, s.total
FROM september s, average a
WHERE s.total > a.avg_total
ORDER BY s.total DESC;

Sentabrda: Aziz 370 000, Malika 300 000, Sardor 40 000; o'rtacha 236 666.67. Har bir bosqich o'z nomiga ega: avval september, keyin average, oxirida natija. Bir CTE keyingisida bemalol ishlatiladi.

CTE qachon foydali

• O'qilishi: murakkab so'rov yuqoridan pastga "hikoya" kabi o'qiladi.
• Takrorlanmaslik: bir natijani so'rovda ikki-uch joyda ishlatish mumkin.
• Tuzatish: har bir CTE ni alohida SELECT * FROM september qilib tekshirish oson.
• Rekursiya: WITH RECURSIVE bilan ierarxiya (xodim → rahbar) va sanalar qatori quriladi — bu alohida mavzu.

Ko'p uchraydigan xatolar

CTE lar orasiga vergul qo'yish va har birini WITH bilan boshlamaslik kerak:

WITH a AS (SELECT 1 AS x)
WITH b AS (SELECT x FROM a)
SELECT * FROM b;

WITH a AS (SELECT 1 AS x),
     b AS (SELECT x FROM a)
SELECT * FROM b;

Yana bir xato: CTE faqat shu bitta so'rov ichida yashaydi. Keyingi so'rovda SELECT * FROM september yozsangiz — relation "september" does not exist. Natija qayta-qayta kerak bo'lsa, VIEW yoki vaqtinchalik jadval yarating.

Xulosa

• WITH nom AS (...) — so'rov ichidagi nomlangan vaqtinchalik natija.
• Bir nechta CTE vergul bilan ajratiladi va bir-biriga tayanadi.
• CTE ni ichma-ich subquery larni tushunarli bosqichlarga ajratish uchun ishlating.
• CTE faqat o'zi yozilgan so'rov ichida mavjud.