CTE

CTE — bitta SQL statement doirasida nom berilgan vaqtinchalik query natijasi. Uning to‘liq nomi Common Table Expression hisoblanadi.

CTE odatda WITH clause orqali yaratiladi va asosiy SELECT, INSERT, UPDATE yoki DELETE statementida table kabi ishlatiladi.

Oddiy CTE

Misol:

WITH active_users AS (
    SELECT id, name
    FROM users
    WHERE active = true
)
SELECT *
FROM active_users;

active_users faqat shu statement davomida mavjud.

U database’da doimiy view yoki table yaratmaydi.

Readability

Murakkab query bir nechta mantiqiy bosqichga ajratiladi.

raw orders
→ paid orders
→ daily totals
→ final report

Har bosqichga aniq nom beriladi.

Bu chuqur nested subqueryga qaraganda o‘qishni osonlashtiradi.

Bir nechta CTE

Bitta WITH ichida bir necha CTE e’lon qilinadi.

WITH
paid_orders AS (...),
daily_totals AS (...),
top_days AS (...)
SELECT ...

Keyingi CTE oldingi CTEga murojaat qilishi mumkin.

Dependency tartibi aniq ko‘rinadi.

CTE scope

CTE faqat bitta statement doirasida mavjud.

Keyingi alohida query undan foydalana olmaydi.

Natijani bir nechta statement uchun saqlash kerak bo‘lsa:

• temporary table;
• view;
• materialized view

ishlatiladi.

CTE va view

CTE query ichidagi lokal nomlangan natija.

View schema’da doimiy obyekt.

CTE:

• bir statement;
• permission obyekti emas;
• parameter kontekstiga yaqin;
• vaqtinchalik.

View esa ko‘p consumer uchun reusable interface.

CTE va subquery

CTE ko‘pincha nomlangan subqueryga o‘xshaydi.

Afzalligi:

• nesting kamayadi;
• bir bosqich bir necha marta ko‘rsatiladi;
• recursive query yoziladi;
• debugging osonlashadi.

Performance farqi optimizer va database versiyasiga bog‘liq.

Materialization

Ayrim database yoki versiyalarda CTE natijasi to‘liq materializatsiya qilinishi mumkin.

Bu:

• queryni bir marta hisoblaydi;
• qayta foydalanishda foydali;
• temp memory yoki disk ishlatadi;
• outer filter pushdownini to‘sishi mumkin.

Boshqa tizim CTE’ni inline qilib oddiy subquerydek optimallashtiradi.

Explicit materialization

Ba’zi database’lar CTE materializatsiyasini:

• majburiy;
• taqiqlangan;
• optimizer tanlovi

sifatida boshqarishga imkon beradi.

Bir xil CTE bir necha marta ishlatilsa materialization foydali bo‘lishi mumkin.

Kichik filter faqat outer query’da bo‘lsa inline samaraliroq bo‘lishi mumkin.

Recursive CTE

Recursive CTE o‘ziga murojaat qilib hierarchiya yoki ketma-ketlikni bosqichma-bosqich quradi.

Tuzilma ikki qismdan iborat:

• anchor query;
• recursive query.

Misol:

root category
→ child categories
→ keyingi avlod

Iteration yangi row qolmaguncha yoki limitgacha davom etadi.

Anchor qism

Recursive CTEning boshlang‘ich rowlarini beradi.

Masalan, parent_id IS NULL bo‘lgan rootlar.

Anchor recursive reference ishlatmaydi.

Recursive qism

Oldingi iteration natijasini base table bilan join qilib yangi rowlar hosil qiladi.

Depth, path yoki parent ma’lumotini hisoblab borish mumkin.

Cycle bo‘lsa cheksiz recursion xavfi mavjud.

Hierarchy

Recursive CTE quyidagilar uchun ishlatiladi:

• organization tree;
• category tree;
• file hierarchy;
• dependency graph;
• bill of materials;
• comment thread.

Natijada depth va path columnlari yaratilishi mumkin.

Cycle detection

Graph tree bo‘lmasa node qayta uchrashi mumkin.

Path array, visited set yoki database’ning cycle clause’i orqali takroriy node aniqlanadi.

Cycle detection bo‘lmasa query maksimal recursion limitgacha davom etishi mumkin.

Recursion limiti

Database recursive iteration sonini cheklashi mumkin.

Bu noto‘g‘ri query va cycle’dan himoya qiladi.

Haqiqiy hierarchy chuqurligi limitdan oshsa explicit boshqarish kerak.

DML bilan CTE

CTE update yoki delete qilinadigan rowlarni oldindan aniqlashi mumkin.

WITH expired AS (
    SELECT id
    FROM sessions
    WHERE expires_at < CURRENT_TIMESTAMP
    LIMIT 1000
)
DELETE FROM sessions
WHERE id IN (SELECT id FROM expired);

Bu katta cleanupni batchlarga ajratishga yordam beradi.

Data-modifying CTE

Ayrim database’lar CTE ichida INSERT, UPDATE yoki DELETE natijasini boshqa statementda ishlatishga ruxsat beradi.

Masalan, o‘chirilgan rowlarni archive table’ga ko‘chirish.

Execution tartibi va snapshot semantikasi database hujjatiga bog‘liq.

Window va aggregate

CTE ichida aggregation va window function bilan oraliq model yaratiladi.

Masalan:

1. har category bo‘yicha product rank;
2. faqat top uchta product;
3. final display.

Bu analytical SQLni bosqichlarga ajratadi.

Reuse

Bir CTE query ichida bir necha marta ishlatilishi mumkin.

Optimizer natijani qayta hisoblash yoki materializatsiya qilishni tanlaydi.

Volatile function mavjud bo‘lsa har reference semantikasi muhim.

Debugging

Murakkab queryni tekshirishda final statement o‘rniga har CTE natijasi vaqtincha SELECT * bilan ko‘riladi.

Row grain, duplicate va null bosqichma-bosqich aniqlanadi.

CTE nomlari business ma’noni ifodalashi kerak.

Column nomlari

CTE nomidan keyin output column nomlari explicit berilishi mumkin.

Bu expression va recursive branchlar orasidagi type hamda nom contractini aniq qiladi.

Ayniqsa UNION ALL ishlatiladigan recursive CTE’da column pozitsiyasi mos bo‘lishi zarur.

CTE va permission

CTE alohida database obyekt emas, shuning uchun unga mustaqil permission berilmaydi.

Caller ichki query murojaat qilgan table va functionlar uchun kerakli huquqlarga ega bo‘lishi kerak.

Naming convention

CTE nomi uning bosqichdagi row grainini bildiradi: daily_sales, ranked_products, invalid_rows. Bu duplicate va noto‘g‘ri joinni review vaqtida tez aniqlashga yordam beradi.

Bog‘liq tushunchalar

Common Table Expression, WITH clause, Recursive CTE, Subquery, Derived table, Materialization, Hierarchy, Query optimizer, Temporary table, View