16-dars. CTE (Common Table Expression)

CTE nima?

CTE (Common Table Expression) — query davomida foydalanish uchun oldindan tayyorlab qo‘yiladigan, nom berilgan vaqtinchalik natija.

Oddiyroq qilib:

CTE katta yoki murakkab query'ni kichik bosqichlarga ajratib, tushunarliroq yozishga yordam beradi.

Masalan, maoshi 5000 dan yuqori xodimlarni topishimiz kerak.

Oddiy query:

SELECT
    full_name,
    salary
FROM employees
WHERE salary > 5000;

CTE bilan:

WITH cte_salary AS
(
    SELECT
        full_name,
        salary
    FROM employees
    WHERE salary > 5000
)

SELECT
    full_name,
    salary
FROM cte_salary;

Bu yerda cte_salary — biz yaratgan CTE.

CTE qanday ishlaydi?

CTE'ni 2 bosqich deb tasavvur qilish oson.

1-bosqich — ma'lumotni tayyorlash

WITH cte_salary AS
(
    SELECT
        full_name,
        salary
    FROM employees
    WHERE salary > 5000
)

Bu yerda kerakli ma'lumotni ajratib oldik.

2-bosqich — tayyor ma'lumotdan foydalanish

SELECT *
FROM cte_salary;

Demak:

employees
    ↓
filter
    ↓
cte_salary
    ↓
SELECT

Asosiy fikr:

CTE ichida — ma'lumotni tayyorlaymiz.

Tashqi query'da — tayyorlangan ma'lumotdan foydalanamiz.

CTE sintaksisi

WITH cte_name AS
(
    SELECT ...
    FROM ...
    WHERE ...
)

SELECT ...
FROM cte_name;

Bu yerda:

Nima uchun CTE kerak?

CTE quyidagi holatlarda foydali:

• query juda uzun bo‘lib ketganda;

• katta query'ni bosqichlarga ajratish kerak bo‘lganda;

• birinchi bosqichda hisoblangan natijadan keyingi bosqichda foydalanish kerak bo‘lganda;

• GROUP BY, AVG(), SUM(), COUNT() kabi hisob-kitoblardan keyin yana filter qilish kerak bo‘lganda;

• murakkab query'ni o‘qishni osonlashtirish kerak bo‘lganda.

Eng sodda ta'rif:

CTE — murakkab query'ni bosqichma-bosqich yozish uchun qulay vosita.

CTE vs Subquery

Subquery:

SELECT *
FROM
(
    SELECT
        full_name,
        salary
    FROM employees
    WHERE salary > 5000
) AS salary_data;

Xuddi shu ishni CTE bilan:

WITH salary_data AS
(
    SELECT
        full_name,
        salary
    FROM employees
    WHERE salary > 5000
)

SELECT *
FROM salary_data;

Ikkalasining vazifasi o‘xshash.

Subquery — query ichida query.

CTE — query'dan oldin natijaga nom berib, keyin undan foydalanish.

CTE ayniqsa query bir nechta bosqichdan iborat bo‘lganda o‘qishni osonlashtiradi.

CTE'ni boshqa funksiyalar bilan qo'llanilishi

CTE + WHERE

Endi amaliyot.

Vazifa:

Maoshi 5000 dan yuqori bo‘lgan xodimlarni toping.

WITH cte_salary AS
(
    SELECT
        full_name,
        salary,
        department
    FROM employees
    WHERE salary > 5000
)

SELECT
    full_name,
    salary
FROM cte_salary;

Bu yerda CTE maoshi 5000 dan yuqori xodimlarni tayyorlab beradi.

CTE + ORDER BY

Vazifa:

IT bo‘limidagi xodimlarni oling va maoshi bo‘yicha kamayish tartibida chiqaring.

WITH cte_it AS
(
    SELECT
        full_name,
        salary,
        department
    FROM employees
    WHERE department = 'IT'
)

SELECT
    full_name,
    salary
FROM cte_it
ORDER BY salary DESC;

Bu yerda:

CTE:

faqat IT xodimlari

Tashqi query:

maosh bo‘yicha tartiblash

CTE + GROUP BY

Vazifa:

Har bir bo‘limning o‘rtacha maoshini hisoblang.

WITH cte_avg_salary AS
(
    SELECT
        department,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)

SELECT *
FROM cte_avg_salary;

Bu yerda:

employees
    ↓
GROUP BY department
    ↓
AVG(salary)
    ↓
cte_avg_salary

Keyin tashqi query shu natijadan foydalanadi.

CTE natijasini tashqarida filterlash

Vazifa:

O‘rtacha maoshi 5000 dan yuqori bo‘lgan bo‘limlarni toping.

WITH cte_avg_salary AS
(
    SELECT
        department,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)

SELECT *
FROM cte_avg_salary
WHERE avg_salary > 5000;

Bu yerda ikki xil ish bajarildi:

CTE ichida:

Har bir bo‘limning o‘rtacha maoshini hisobladik.

Tashqarida:

O‘rtacha maoshi 5000 dan yuqori bo‘lganlarini oldik.

CTE + COUNT()

Vazifa:

Har bir bo‘limda nechta xodim borligini toping.

WITH cte_employee_count AS
(
    SELECT
        department,
        COUNT(*) AS employee_count
    FROM employees
    GROUP BY department
)

SELECT *
FROM cte_employee_count;

Keyin:

Eng ko‘p xodimga ega bo‘limni toping.

WITH cte_employee_count AS
(
    SELECT
        department,
        COUNT(*) AS employee_count
    FROM employees
    GROUP BY department
)

SELECT TOP 1 *
FROM cte_employee_count
ORDER BY employee_count DESC;

CTE + SUM()

Vazifa:

Har bir shahar bo‘yicha xodimlarning jami maoshini hisoblang.

WITH cte_sum_salary AS
(
    SELECT
        city,
        SUM(salary) AS total_salary
    FROM employees
    GROUP BY city
)

SELECT *
FROM cte_sum_salary;

Keyin:

Jami maoshi eng katta bo‘lgan shaharni toping.

WITH cte_sum_salary AS
(
    SELECT
        city,
        SUM(salary) AS total_salary
    FROM employees
    GROUP BY city
)

SELECT TOP 1 *
FROM cte_sum_salary
ORDER BY total_salary DESC;

CTE + Aggregate + WHERE

Vazifa:

Maoshi 5000 dan yuqori bo‘lgan xodimlarni ajrating. Keyin ularning o‘rtacha maoshini hisoblang.

WITH cte_salary AS
(
    SELECT
        full_name,
        salary
    FROM employees
    WHERE salary > 5000
)

SELECT
    AVG(salary) AS avg_salary
FROM cte_salary;

Bu yerda:

1. WHERE orqali xodimlarni tanladik
              ↓
2. CTE yaratdik
              ↓
3. CTE'dagi ma'lumotlarning AVG()ini hisobladik

CTE turlari

CTE
│
├── Non-recursive CTE
│      │
│      ├── Standalone CTE
│      └── Nested CTE
│
└── Recursive CTE

Standalone CTE

Standalone CTE — mustaqil ravishda yaratilgan oddiy CTE.

Beginner kursimizda asosiy o‘rganadigan CTE aynan shu.

Masalan:

WITH cte_salary AS
(
    SELECT
        full_name,
        salary
    FROM employees
    WHERE salary > 5000
)

SELECT *
FROM cte_salary;

Bu yerda bitta CTE bor:

cte_salary

va undan tashqi query foydalanmoqda.

Nested CTE

Nested CTE'ni chuqur o‘rganmaymiz.

Oddiy tushuncha:

Bir nechta CTE'ni ketma-ket ishlatish yoki bir CTE natijasidan boshqa CTE'da foydalanish.

Masalan:

WITH cte_salary AS
(
    SELECT *
    FROM employees
    WHERE salary > 5000
),

cte_it AS
(
    SELECT *
    FROM cte_salary
    WHERE department = 'IT'
)

SELECT *
FROM cte_it;

Bu yerda:

employees
    ↓
cte_salary
    ↓
cte_it
    ↓
SELECT

Ya'ni ikkinchi CTE birinchi CTE'dan foydalanmoqda.

Recursive CTE

Recursive CTE oddiy CTE'dan biroz boshqacha.

U o‘zining oldingi natijasiga yana murojaat qilib, ma'lumotlarni bosqichma-bosqich hosil qilish uchun ishlatiladi.

Ko‘pincha:

• hierarchy;

• parent → child;

• employee → manager;

• category → subcategory;

• folder → subfolder kabi ma'lumotlarda uchraydi.

Masalan, kompaniya tuzilmasi:

CEO
 ├── Manager
 │    ├── Employee
 │    └── Employee
 └── Manage9r
      └── Employee

Bunday hierarchy'ni bosqichma-bosqich yurib chiqishda Recursive CTE ishlatilishi mumkin.

Sintaksis (faqat tanishuv):

WITH employee_hierarchy AS
(
    -- Boshlang‘ich qism
    SELECT
        employee_id,
        full_name,
        manager_id
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Keyingi bosqich
    SELECT
        e.employee_id,
        e.full_name,
        e.manager_id
    FROM employees e
    JOIN employee_hierarchy h
        ON e.manager_id = h.employee_id
)

SELECT *
FROM employee_hierarchy;

"Recursive CTE o‘zidan foydalanib, hierarchy kabi ma'lumotlarni bosqichma-bosqich topadi."

Bir nechta CTE

Bir query ichida bir nechta CTE yaratish mumkin:

WITH cte_avg AS
(
    SELECT
        department,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
),

cte_count AS
(
    SELECT
        department,
        COUNT(*) AS employee_count
    FROM employees
    GROUP BY department
)

SELECT *
FROM cte_avg;

Bu yerda:

cte_avg
    ↓
o‘rtacha maosh

cte_count
    ↓
xodimlar soni

Keyinchalik bu CTE'larni JOIN qilish ham mumkin.

Darsga tegishli SQL fayllar

⬇️ Darsda ishlatilgan .sql fayli: 📎 dars_16.sql (714 B)

⬇️ Uyga vazifa .sql fayli: 📎 dars_16_uy_ishi.sql (2.7 KB)