CTE: WITH və rekursiv sorğular

Bu dərsdə öyrənəcəksən

WITH ilə adlı aralıq nəticələr yaratmaq və onları təkrar istifadə etmək
Bir neçə CTE-ni ardıcıl addımlar kimi zəncirləmək
WITH RECURSIVE ilə ədəd sırası və iyerarxiya qurmaq, sonsuz dövrdən qaçmaq

Üç qat iç-içə alt sorğusu olan sorğunu oxumaq içəridən bayıra doğru tapmaca həll etməyə bənzəyir. Proqramlaşdırmada belə vaxtlarda aralıq nəticəyə ad verib dəyişənə yazırıq. SQL-də bunun analoqu CTE-dir (common table expression — ümumi cədvəl ifadəsi): sorğunu adlı addımlara bölürük və hər addım əvvəlkilərdən istifadə edə bilir. Üstəlik, CTE özünə də müraciət edə bilər — bu, iyerarxiyalar və ardıcıllıqlar üçün güclü alətdir.

WITH: aralıq nəticəyə ad vermək

CTE WITH ad AS (sorğu) şəklində əsas sorğudan əvvəl yazılır və yalnız həmin sorğu icra olunanda mövcuddur. Onun alt sorğudan böyük üstünlüyü: eyni CTE-yə bir neçə dəfə müraciət etmək olar. Aşağıda customer_totals iki yerdə işlənir — həm birləşmədə, həm də orta məbləği hesablayan alt sorğuda.

SQL· Kodu dəyiş və yenidən işə sal

WITH customer_totals AS (
  SELECT o.customer_id, SUM(o.quantity * p.price) AS total
  FROM orders AS o
  JOIN products AS p ON p.id = o.product_id
  GROUP BY o.customer_id
)
SELECT c.name, ROUND(ct.total, 2) AS total
FROM customer_totals AS ct
JOIN customers AS c ON c.id = ct.customer_id
WHERE ct.total > (SELECT AVG(total) FROM customer_totals)
ORDER BY ct.total DESC;

▸ Gözlənilən nəticə

name | total
Anar Mustafayev | 1723
Emre Yılmaz | 941.99
Zəhra Hüseynli | 935.99

Orta xərcdən (563,15) çox pul xərcləyən müştərilər.

CTE zənciri: addım-addım sorğu

Bir WITH-də vergüllə bir neçə CTE yazmaq olar və hər biri özündən əvvəlkilərə müraciət edə bilər. Bu, «məlumat konveyeri» yaradır: 1) hər sifarişin məbləğini və ölkəsini hesabla, 2) ölkələr üzrə yekunla, 3) hər ölkənin ümumi gəlirdəki payını tap.

SQL· Kodu dəyiş və yenidən işə sal

WITH lines AS (
  SELECT c.country, o.quantity * p.price AS amount
  FROM orders AS o
  JOIN products  AS p ON p.id = o.product_id
  JOIN customers AS c ON c.id = o.customer_id
),
by_country AS (
  SELECT country, COUNT(*) AS orders, ROUND(SUM(amount), 2) AS revenue
  FROM lines
  GROUP BY country
)
SELECT country, orders, revenue,
       ROUND(100.0 * revenue / (SELECT SUM(revenue) FROM by_country), 1) AS share_pct
FROM by_country
ORDER BY revenue DESC;

▸ Gözlənilən nəticə

country | orders | revenue | share_pct
Azerbaijan | 7 | 2843.49 | 72.1
Türkiye | 3 | 973.59 | 24.7
United Kingdom | 1 | 69.98 | 1.8
Russia | 1 | 55 | 1.4

Rekursiv CTE: özünə müraciət edən sorğu

Tərif

Rekursiv CTE

WITH RECURSIVE ilə yazılan və iki hissədən ibarət CTE: başlanğıc hissə (anchor) ilk sətirləri verir, UNION ALL-dan sonrakı rekursiv hissə isə CTE-nin özünə müraciət edib əvvəlki addımın sətirlərindən yenilərini qurur. Rekursiv hissə yeni sətir qaytarmayanda proses dayanır.

SQL· Kodu dəyiş və yenidən işə sal

WITH RECURSIVE numbers(n) AS (
  SELECT 1                                -- anchor
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 5   -- step + stop
)
SELECT n, n * n AS square
FROM numbers;

▸ Gözlənilən nəticə

n | square
1 | 1
2 | 4
3 | 9
4 | 16
5 | 25

Belə sıra nə üçün lazımdır? Hesabatda boş qrupları da göstərmək üçün. Məsələn, balların 10 ballıq aralıqlar üzrə paylanması: GROUP BY yalnız məlumatı olan aralıqları verir. Aralıqları rekursiv CTE ilə yaradıb LEFT JOIN etsək, heç kimin düşmədiyi 40–49 aralığı da 0 ilə görünür.

SQL· Kodu dəyiş və yenidən işə sal

WITH RECURSIVE buckets(low) AS (
  SELECT 40
  UNION ALL
  SELECT low + 10 FROM buckets WHERE low < 90
)
SELECT b.low || '-' || (b.low + 9) AS score_range,
       COUNT(e.id) AS enrollments
FROM buckets AS b
LEFT JOIN enrollments AS e ON e.score BETWEEN b.low AND b.low + 9
GROUP BY b.low
ORDER BY b.low;

▸ Gözlənilən nəticə

score_range | enrollments
40-49 | 0
50-59 | 1
60-69 | 2
70-79 | 5
80-89 | 4
90-99 | 8

İyerarxiyanı gəzmək

Rekursiv CTE-nin klassik tətbiqi ağac strukturlarıdır: təşkilat sxemi, kateqoriyalar və alt kateqoriyalar, qovluqlar. Hər işçinin sətrində rəhbərinin id-si (manager_id) saxlanılır. Başlanğıc hissə rəhbəri olmayan direktoru götürür, rekursiv hissə isə hər addımda bir səviyyə aşağı enir və yolu (path) uzadır.

SQL· Kodu dəyiş və yenidən işə sal

CREATE TABLE staff (id INTEGER PRIMARY KEY, name TEXT, role TEXT,
                    manager_id INTEGER REFERENCES staff(id));
INSERT INTO staff VALUES
  (1, 'Samir',   'Director',            NULL),
  (2, 'Ramin',   'Head of Science',     1),
  (3, 'Nərmin',  'Head of Humanities',  1),
  (4, 'Ülviyyə', 'Physics teacher',     2),
  (5, 'Elnur',   'Informatics teacher', 2),
  (6, 'Sara',    'English teacher',     3);

WITH RECURSIVE chain(id, name, level, path) AS (
  SELECT id, name, 0, name
  FROM staff
  WHERE manager_id IS NULL
  UNION ALL
  SELECT s.id, s.name, c.level + 1, c.path || ' > ' || s.name
  FROM staff AS s
  JOIN chain AS c ON s.manager_id = c.id
)
SELECT level, path
FROM chain
ORDER BY path;

▸ Gözlənilən nəticə

level | path
0 | Samir
1 | Samir > Nərmin
2 | Samir > Nərmin > Sara
1 | Samir > Ramin
2 | Samir > Ramin > Elnur
2 | Samir > Ramin > Ülviyyə

ORDER BY path ağacı «qovluq» qaydasında düzür: hər rəhbərin altında onun işçiləri.

VBİS	Adi CTE	Rekursiv CTE
SQLite	WITH	WITH RECURSIVE
PostgreSQL	WITH	WITH RECURSIVE
MySQL 8.0+	WITH	WITH RECURSIVE
SQL Server	WITH	`WITH` (RECURSIVE sözü yazılmır)

SQL Server-də CTE-dən əvvəlki əmr nöqtəli vergüllə bitməlidir, ona görə çox vaxt ;WITH yazırlar.

Tapşırıq

course_stats adlı CTE yarat: hər kurs üçün course_id, yazılışların sayı (students) və orta bal (avg_score). Sonra onu courses ilə birləşdirib ən azı 3 şagirdi olan kursların adını, şagird sayını və 1 onluq rəqəmə qədər yuvarlaqlaşdırılmış orta balını göstər — orta bala görə azalan sırada.

Tapşırıq · SQL· Kodu dəyiş və yenidən işə sal

WITH course_stats AS (
  -- course_id, students, avg_score
)
SELECT c.title, cs.students, ROUND(cs.avg_score, 1) AS avg_score
FROM course_stats AS cs
JOIN courses AS c ON c.id = cs.course_id
-- filter and sort

▸ Gözlənilən nəticə

title | students | avg_score
Python Basics | 3 | 92.7
Algebra | 4 | 87.3
Geometry | 3 | 81.3
Mechanics | 4 | 80

Tapşırıq

Rekursiv CTE ilə 0, 100, 200, 300, 400 qiymət aralıqlarını (low) yarat. Hər aralığa düşən məhsulların sayını (products) göstər: məhsul low <= price < low + 100 olanda aralığa düşür. Boş aralıqlar da 0 ilə görünməlidir. Nəticəni low-a görə düz.

Tapşırıq · SQL· Kodu dəyiş və yenidən işə sal

WITH RECURSIVE brackets(low) AS (
  SELECT 0
  UNION ALL
  -- next bracket, stop at 400
)
SELECT b.low, COUNT(p.id) AS products
FROM brackets AS b
-- LEFT JOIN products
GROUP BY b.low
ORDER BY b.low;

▸ Gözlənilən nəticə

low | products
0 | 6
100 | 1
200 | 0
300 | 1
400 | 0

Əsas fikirlər

CTE (WITH ad AS (...)) yalnız bir sorğu üçün mövcud olan adlı aralıq nəticədir.
Eyni CTE-yə bir neçə dəfə müraciət etmək olar; vergüllə ayrılmış CTE-lər əvvəlkilərdən istifadə edə bilir.
Rekursiv CTE = başlanğıc hissə + UNION ALL + özünə müraciət edən hissə + dayanma şərti.
Rekursiv CTE ədəd sıraları, boş qrupları göstərən hesabatlar və iyerarxiyalar (təşkilat sxemi, kateqoriyalar) üçün işlədilir.
SQL Server-də rekursiv CTE RECURSIVE sözü olmadan yazılır və susmaya görə 100 addımla məhdudlaşır.

Özünü yoxla

10 sual. Hər düzgün cavab XP qazandırır.

1 / 10

CTE-nin FROM-dakı alt sorğudan əsas üstünlüyü nədir?