CTE: WITH ve özyinelemeli sorgular

Karmaşık bir sorguyu adlandırılmış adımlara böl, birkaç CTE'yi zincir gibi bağla ve özyinelemeli CTE'lerle sayı dizileri ve hiyerarşiler kur.

  • İleri
  • 20 dk
  • 10 soru
  • Ders 16 / 28

Bu derste öğreneceklerin

  • WITH ile adlandırılmış ara sonuçlar oluşturmak ve bunları yeniden kullanmak
  • Birkaç CTE'yi ardışık adımlar olarak zincirlemek
  • WITH RECURSIVE ile sayı dizisi ve hiyerarşi kurmak, sonsuz döngüden kaçınmak

Üç kat iç içe alt sorgusu olan bir sorguyu okumak, bir bulmacayı içeriden dışarıya çözmeye benzer. Programlamada böyle anlarda ara sonuca bir ad verir ve onu bir değişkene koyarız. SQL'de bunun karşılığı CTE'dir (common table expression, ortak tablo ifadesi): sorguyu adlandırılmış adımlara böleriz ve her adım öncekileri kullanabilir. Üstelik bir CTE kendine de başvurabilir; bu, hiyerarşiler ve diziler için güçlü bir araçtır.

WITH: ara sonuca ad vermek

CTE, ana sorgudan önce WITH ad AS (sorgu) biçiminde yazılır ve yalnızca o sorgu çalışırken var olur. Alt sorguya göre büyük üstünlüğü, aynı CTE'ye birkaç kez başvurulabilmesidir. Aşağıda customer_totals iki yerde kullanılıyor: hem birleştirmede hem de ortalama tutarı hesaplayan alt sorguda.

SQL
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;
Beklenen çıktı
name | total
Anar Mustafayev | 1723
Emre Yılmaz | 941.99
Zəhra Hüseynli | 935.99
Ortalamadan (563,15) fazla harcayan müşteriler.

Okumaya devam etmek için ücretsiz kaydol

Dersin geri kalan bölümleri, test ve notlar Educora hesabıyla açılır. Kayıt bir dakika sürer; ilerlemen tüm cihazlarında saklanır.

CTE zinciri: adım adım sorgu

Tek bir WITH içinde virgülle ayrılmış birkaç CTE yazılabilir ve her biri kendinden öncekilere başvurabilir. Böylece bir “veri hattı” oluşur: 1) her siparişin tutarını ve ülkesini hesapla, 2) ülkelere göre topla, 3) her ülkenin toplam gelirdeki payını bul.

SQL
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;
Beklenen çıktı
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

Özyinelemeli CTE: kendine başvuran sorgu

Özyinelemeli CTETanım

WITH RECURSIVE ile yazılan ve iki bölümden oluşan CTE: başlangıç bölümü (anchor) ilk satırları üretir, UNION ALL sonrasındaki özyinelemeli bölüm ise CTE'nin kendisine başvurarak önceki adımın satırlarından yenilerini kurar. Özyinelemeli bölüm yeni satır döndürmediğinde süreç durur.

SQL
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;
Beklenen çıktı
n | square
1 | 1
2 | 4
3 | 9
4 | 16
5 | 25

Böyle bir dizi ne işe yarar? Raporda boş grupları da göstermeye. Örneğin puanların 10'ar puanlık aralıklara dağılımı: GROUP BY yalnızca verisi olan aralıkları döndürür. Aralıkları özyinelemeli bir CTE ile üretip LEFT JOIN yaparsak kimsenin düşmediği 40–49 aralığı da 0 ile görünür.

SQL
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;
Beklenen çıktı
score_range | enrollments
40-49 | 0
50-59 | 1
60-69 | 2
70-79 | 5
80-89 | 4
90-99 | 8

Hiyerarşide gezinmek

Özyinelemeli CTE'lerin klasik kullanımı ağaç yapılarıdır: organizasyon şeması, kategoriler ve alt kategoriler, klasörler. Her çalışanın satırında yöneticisinin id değeri (manager_id) tutulur. Başlangıç bölümü yöneticisi olmayan müdürü alır, özyinelemeli bölüm ise her adımda bir düzey aşağı iner ve yolu (path) uzatır.

SQL
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;
Beklenen çıktı
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ı klasör düzeninde sıralar: her yöneticinin altında onun çalışanları.
VTYSSıradan CTEÖzyinelemeli CTE
SQLiteWITHWITH RECURSIVE
PostgreSQLWITHWITH RECURSIVE
MySQL 8.0+WITHWITH RECURSIVE
SQL ServerWITHWITH (RECURSIVE sözcüğü yazılmaz)
SQL Server'da CTE'den önceki komut noktalı virgülle bitmelidir; bu yüzden çoğu zaman ;WITH yazılır.

Alıştırma

course_stats adlı bir CTE oluştur: her ders için course_id, kayıt sayısı (students) ve ortalama puan (avg_score). Sonra onu courses ile birleştir ve en az 3 öğrencisi olan derslerin adını, öğrenci sayısını ve virgülden sonra 1 basamağa yuvarlanmış ortalama puanını ortalamaya göre azalan sırada göster.

Alıştırma · SQL
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
Beklenen çıktı
title | students | avg_score
Python Basics | 3 | 92.7
Algebra | 4 | 87.3
Geometry | 3 | 81.3
Mechanics | 4 | 80

Alıştırma

Özyinelemeli bir CTE ile 0, 100, 200, 300, 400 fiyat aralıklarını (low) üret. Her aralığa düşen ürün sayısını (products) göster: ürün, low <= price < low + 100 olduğunda o aralıktadır. Boş aralıklar da 0 ile görünmeli. Sonucu low değerine göre sırala.

Alıştırma · SQL
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;
Beklenen çıktı
low | products
0 | 6
100 | 1
200 | 0
300 | 1
400 | 0

Unutma

  • CTE (WITH ad AS (...)), yalnızca tek bir sorgu için var olan adlandırılmış bir ara sonuçtur.
  • Aynı CTE'ye birkaç kez başvurulabilir; virgülle ayrılmış CTE'ler öncekileri kullanabilir.
  • Özyinelemeli CTE = başlangıç bölümü + UNION ALL + kendine başvuran bölüm + durma koşulu.
  • Özyinelemeli CTE'ler sayı dizileri, boş grupları gösteren raporlar ve hiyerarşiler (organizasyon şeması, kategoriler) için kullanılır.
  • SQL Server'da özyinelemeli CTE RECURSIVE sözcüğü olmadan yazılır ve varsayılan olarak 100 adımla sınırlıdır.
Bu derste bir hata ya da yanlışlık mı gördün?

Kendini test et

10 soru. Her doğru cevap XP kazandırır.

Soru 1 / 10

Soru 1 / 10. Bir CTE'nin FROM içindeki alt sorguya göre temel üstünlüğü nedir?