MariaDB'de CTE: WITH ve WITH RECURSIVE ile Okunur ve Hiyerarşik Sorgular

WITH sözdizimi

WITH [RECURSIVE] table_reference [(columns_list)] AS (
  SELECT ...
) [CYCLE cycle_column_list RESTRICT]
[, table_reference [(columns_list)] AS (
  SELECT ...
) [CYCLE cycle_column_list RESTRICT]] ...
SELECT ...

Belgedeki kurallar şöyle özetlenebilir:

• Tek bir WITH yan tümcesi virgülle ayrılmış birden fazla CTE tanımlayabilir ve her CTE'nin adı yan tümce içinde benzersiz olmalıdır.
• Bir CTE, aynı yan tümcede kendisinden önce tanımlanmış herhangi bir CTE'ye başvurabilir. RECURSIVE yoksa kendisinden sonra tanımlanana başvuramaz; RECURSIVE varsa yan tümcedeki CTE'ler birbirine istedikleri sırayla başvurabilir.
• RECURSIVE kelimesi WITH'ten sonra yalnızca bir kez yazılır ve bütün yan tümceye uygulanır.
• WITH alt sorgularda da, EXPLAIN ile birlikte de kullanılabilir.
• İsteğe bağlı sütun listesi (columns_list), CTE'nin sütunlarına ad verir; özyinelemeli CTE'lerde bu listeyi yazmak sorguyu okunur kılar.

Özyinelemesiz CTE

Özyinelemesiz CTE, belgenin deyimiyle temelde sorguya özel bir görünümdür (view) ve türetilmiş tabloya (FROM (SELECT ...) AS ad) çok benzer. Aşağıdaki iki sorgu aynı işi yapar; ilki CTE, ikincisi türetilmiş tablo kullanır:

WITH engineers AS 
   ( SELECT * FROM employees
     WHERE dept = 'Engineering' )
SELECT * FROM engineers;

SELECT * FROM
   ( SELECT * FROM employees
     WHERE dept = 'Engineering' ) AS engineers;

CTE'nin iki somut avantajı vardır. Birincisi okunabilirlik: iç içe geçmiş FROM (SELECT ...) yapıları yukarıdan aşağıya okunan adımlara dönüşür. İkincisi yeniden kullanım: aynı CTE'ye sorgunun birden fazla yerinden başvurabilirsiniz. Belgedeki küçük örnek ikinci CTE'nin birinciyi kullandığını gösterir:

WITH a AS (SELECT 1 AS x),
     b AS (SELECT x + 1 AS y FROM a)
SELECT a.x, b.y FROM a, b;
+---+---+
| x | y |
+---+---+
| 1 | 2 |
+---+---+

Aynı CTE'yi birden çok kez kullanmak

Belgenin yıllık karşılaştırma örneği, CTE'nin aynı sorgu içinde iki farklı takma adla kullanılmasını gösterir. Önce ürün ve yıl bazında toplam satış hesaplanır, sonra bu sonuç kendisiyle birleştirilerek önceki yıla göre artan ürünler bulunur:

WITH sales_product_year AS (
SELECT product, YEAR(ship_date) AS year,
SUM(price) AS total_amt
FROM item_sales
GROUP BY product, year )

SELECT *
FROM sales_product_year CUR,
sales_product_year PREV
WHERE CUR.product=PREV.product 
AND  CUR.year=PREV.year + 1 
AND CUR.total_amt > PREV.total_amt;

Türetilmiş tabloyla aynı işi yapmak için gruplama sorgusunu iki kez yazmanız gerekirdi. Aynı sayfadaki diğer örnekler CTE'yi bir "anti self join" için (NOT EXISTS ile kendi içinde eşi olmayan satırları bulmak) ve her ürünü yıllık toplamın belli bir oranıyla karşılaştırmak için kullanır. Toplama fonksiyonları ve GROUP BY için MariaDB GROUP BY ve toplama fonksiyonları yazısına bakın.

Aynı sayfanın son örneği, bireyi kendi grubuyla karşılaştırmayı gösterir: CTE yine ürün ve yıl bazında toplamları hesaplar, dış sorgu ise yalnızca o yılın toplam satışının yüzde onundan fazlasını yapan ürünleri döndürür. Burada CTE hem dış sorguda hem de ilişkili alt sorguda kullanılır:

WITH sales_product_year AS (
SELECT product,
YEAR(ship_date) AS year,
SUM(price) AS total_amt
FROM item_sales
GROUP BY product, year
)

SELECT * 
FROM sales_product_year S1
WHERE
total_amt > 
    (SELECT 0.1 * SUM(total_amt)
     FROM sales_product_year S2
     WHERE S2.year = S1.year);

Bu tür sorguları yazarken pratik bir yöntem, her CTE'yi önce tek başına çalıştırıp sonucunu kontrol etmek, ardından bir sonraki adımı eklemektir. CTE'ler adlandırılmış ara adımlar olduğu için hata ayıklama, iç içe alt sorgulara göre çok daha kolaydır. Sorgunun nasıl yürütüleceğini görmek için başına EXPLAIN ekleyebilirsiniz; belge WITH yan tümcesinin EXPLAIN ile birlikte kullanılabildiğini belirtir. Plan okuma için MariaDB EXPLAIN ve ANALYZE yazısına bakın.

Sorgu birçok yerde tekrar ediyorsa kalıcı bir görünüm daha uygundur; görünümler için MariaDB view oluşturma yazısına bakın.

Özyinelemeli CTE nasıl çalışır?

Özyinelemeli CTE'nin gövdesi iki parçadan oluşur: başlangıç sorgusu olan çapa (anchor) kısmı ve CTE'nin kendisine başvuran özyinelemeli kısım. İkisi UNION veya UNION ALL ile birleştirilir. Belge hesaplamayı üç adımda özetler:

WITH RECURSIVE R AS (
  SELECT anchor_data
  UNION [all]
  SELECT recursive_part
  FROM R, ...
)
SELECT ...

• Çapa verisini hesapla.
• Yeni veriyi elde etmek için özyinelemeli kısmı hesapla.
• Yeni veri boş değilse 2. adıma dön.

Yani özyinelemeli kısım her turda yalnızca bir önceki turda üretilen satırları görür ve hiç yeni satır üretmediğinde döngü durur. Belgedeki soy ağacı örneği bunu anlatır: çapa kısmı adı "Alex" olan kişiyi seçer, özyinelemeli kısım her turda bulunan kişilerin anne ve babasını ekler.

WITH RECURSIVE ancestors AS 
 ( SELECT * FROM folks
   WHERE name="Alex"
   UNION
   SELECT f.*
   FROM folks AS f, ancestors AS a
   WHERE f.id = a.father OR f.id = a.mother )
SELECT * FROM ancestors;

Belgedeki adım adım anlatımda ilk turda yalnızca çapa kısmı çalışır ve sonuç tablosunda tek bir satır oluşur: kimliği 100 olan Alex, babasının kimliği 20, annesinin kimliği 30. İkinci turda özyinelemeli kısım bu satırı ancestors olarak görür ve folks tablosunda kimliği 20 ya da 30 olan kişileri bulur. Üçüncü turda yalnızca yeni bulunan bu iki kişinin anne ve babası aranır. Hiçbir yeni ata bulunamadığında sonuç kümesi tamamlanır. Bu mantığı kafanızda canlandırmak, kendi sorgularınızı yazarken "özyinelemeli kısım bir önceki turun çıktısıyla ne yapmalı?" sorusunu doğru sormanızı sağlar.

Örnek: bir şehirden ulaşılabilen tüm duraklar

Belgenin "transitive closure" örneği bir otobüs hattı tablosu kullanır. Bazı hatlar iki yönlüdür, yani graf döngü içerir:

CREATE TABLE bus_routes (origin VARCHAR(50), dst VARCHAR(50));
INSERT INTO bus_routes VALUES 
  ('New York', 'Boston'), 
  ('Boston', 'New York'), 
  ('New York', 'Washington'), 
  ('Washington', 'Boston'), 
  ('Washington', 'Raleigh');

New York'tan başlayarak ulaşılabilen tüm hedefler şöyle bulunur:

WITH RECURSIVE bus_dst as ( 
    SELECT origin as dst FROM bus_routes WHERE origin='New York' 
  UNION
    SELECT bus_routes.dst FROM bus_routes JOIN bus_dst ON bus_dst.dst= bus_routes.origin 
) 
SELECT * FROM bus_dst;
+------------+
| dst        |
+------------+
| New York   |
| Boston     |
| Washington |
| Raleigh    |
+------------+

Belge hesaplamayı şöyle açıklar: çapa kısmı New York ile başlar ve ilk adımda doğrudan bağlı olan Boston ile Washington eklenir. Ardından özyinelemeli kısım bu iki şehirden devam eder; Washington üzerinden Raleigh bulunur. Boston'dan çıkan tek hat New York'a döner, ama New York zaten sonuçta olduğu için tekrar eklenmez. Sonraki turda Raleigh'den çıkan hat olmadığından yeni satır üretilmez ve sorgu biter. Çıktının dört satırdan oluşması bu yüzdendir.

Burada UNION (yani UNION DISTINCT) seçimi kritiktir: zaten bulunan düğümler tekrar eklenmez. Boston'dan New York'a dönüş hattı olmasına rağmen sorgu durur, çünkü bir turda hiç yeni satır üretilmediğinde döngü biter. Aynı sorguyu UNION ALL ile yazsaydınız her tur aynı şehirleri yeniden üretir ve özyineleme sınırına kadar dönerdi.

Örnek: yolları ve güzergâhları hesaplamak

İkinci örnek yalnızca hedefleri değil "New York,Washington,Raleigh" gibi güzergâhları üretir. Döngüyü önlemek için, eklenecek durak güzergâhta zaten varsa satır alınmaz; bunun için FIND_IN_SET kullanılır:

WITH RECURSIVE paths (cur_path, cur_dest) AS (
    SELECT origin, origin FROM bus_routes WHERE origin='New York' 
  UNION
    SELECT CONCAT(paths.cur_path, ',', bus_routes.dst), bus_routes.dst 
     FROM paths
     JOIN bus_routes 
       ON paths.cur_dest = bus_routes.origin AND 
         NOT FIND_IN_SET(bus_routes.dst, paths.cur_path)
) 
SELECT * FROM paths;
+-----------------------------+------------+
| cur_path                    | cur_dest   |
+-----------------------------+------------+
| New York                    | New York   |
| New York,Boston             | Boston     |
| New York,Washington         | Washington |
| New York,Washington,Boston  | Boston     |
| New York,Washington,Raleigh | Raleigh    |
+-----------------------------+------------+

Güzergâhlar her satırda farklı olduğu için burada UNION tekrarları tek başına engelleyemezdi; döngüyü kesen şey NOT FIND_IN_SET koşuludur. CONCAT ve diğer metin fonksiyonları için MariaDB string fonksiyonları yazısına bakın.

Kendi verinizde: kategori ağacı

Aynı kalıbı bir e-ticaret kategori tablosuna uygulamak kolaydır. Her kategorinin ust_id sütunu üst kategoriyi gösterir, kök kategorilerde bu değer NULL'dır. Aşağıdaki sorgu her kategorinin derinliğini ve kökten itibaren tam yolunu hesaplar:

WITH RECURSIVE agac (id, ad, derinlik, yol) AS (
    SELECT id, ad, 1, CAST(ad AS CHAR(500))
    FROM kategoriler WHERE ust_id IS NULL
  UNION ALL
    SELECT k.id, k.ad, a.derinlik + 1, CONCAT(a.yol, ' > ', k.ad)
    FROM kategoriler AS k
    JOIN agac AS a ON k.ust_id = a.id
)
SELECT * FROM agac ORDER BY yol;

İki ayrıntıya dikkat edin. Ağaçta döngü olmadığı varsayıldığından UNION ALL kullanılır; verinizde hatalı bir kayıt bir kategoriyi kendi alt kategorisine bağlıyorsa bu varsayım bozulur ve sorgu özyineleme sınırına takılır. İkincisi, yol sütunu çapa kısmında CAST(ad AS CHAR(500)) ile geniş tanımlanır; bunun nedenini aşağıdaki tip bölümü açıklıyor.

Sonsuz döngüleri önlemek

Kötü kurulmuş bir özyinelemeli CTE kuramsal olarak sonsuz döngüye girebilir. max_recursive_iterations sistem değişkeni yineleme sayısını sınırlar; belgeye göre varsayılan değeri 1000'dir, aralığı 0 ile 4294967295 arasıdır ve oturum düzeyinde değiştirilebilir. Belge, döngü içeren bir tabloda sınırı 10'a düşürerek ne olduğunu gösterir:

CREATE TABLE t1 (from_ int, to_ int);
INSERT INTO t1 VALUES (1,2), (1,100), (2,3), (3,4), (4,1);

SET max_recursive_iterations=10;

WITH RECURSIVE cte (depth, from_, to_) AS ( 
  SELECT 0,1,1 UNION DISTINCT SELECT depth+1, t1.from_, t1.to_ 
    FROM t1, cte  WHERE t1.from_ = cte.to_ 
) 
SELECT * FROM cte;

Bu sorgu depth sütunu her turda arttığı için hiçbir satırı tekrar olarak görmez; UNION DISTINCT döngüyü durduramaz ve belgedeki çıktıda derinlik 0'dan 10'a kadar aynı kenarlar tekrar tekrar listelenir. Sınır olmasaydı sorgu durmayacaktı.

CYCLE ... RESTRICT

MariaDB, SQL standardındaki CYCLE ... SET ... USING biçimini desteklemez ama gevşetilmiş, standart dışı bir biçim sunar: CYCLE sütun_listesi RESTRICT. Bu yan tümce, belirttiğiniz sütunların aynı değer birleşimini tekrar üretmesini engeller; benzersizlik tüm satıra değil yalnızca o sütunlara uygulanır. Aynı sorgu bu yan tümceyle beklenen sonucu verir:

WITH RECURSIVE cte (depth, from_, to_) AS ( 
  SELECT 0,1,1 UNION SELECT depth+1, t1.from_, t1.to_ 
    FROM t1, cte WHERE t1.from_ = cte.to_ 
) 
CYCLE from_, to_ RESTRICT 
SELECT * FROM cte;
+-------+-------+------+
| depth | from_ | to_  |
+-------+-------+------+
|     0 |     1 |    1 |
|     1 |     1 |    2 |
|     1 |     1 |  100 |
|     2 |     2 |    3 |
|     3 |     3 |    4 |
|     4 |     4 |    1 |
+-------+-------+------+

Belgeye göre CYCLE ... RESTRICT kullanıldığında UNION ALL ile UNION DISTINCT arasındaki seçim artık fark etmez. Döngü riski olan graf verilerinde, FIND_IN_SET ile elle yol denetimi yapmaktan genellikle daha sade bir çözümdür.

Sütun tipleri çapa kısmında belirlenir

Özyinelemeli CTE'de bir sütunun veri tipini çapa kısmı belirler. Belgeye göre eski sürümlerde özyinelemeli kısım daha büyük bir değer ürettiğinde veri sessizce kesiliyor ve yanlış sonuç çıkıyordu. MDEV-12325 düzeltmesini içeren sürümlerden itibaren sunucu, özyinelemeli kısmın ürettiği verinin çapa kısmındaki tiplere sığıp sığmadığını denetler ve kesilmeye yol açacak bir uyuşmazlıkta sorguyu hatayla durdurur. Belgedeki örnekte çapa kısmı mid sütununu standart tamsayı olarak seçer, özyinelemeli kısım ise ona 1000000000000 ekler ve şu hata gelir:

Error 1264 (22003): Out of range value for column 'mid' in 'cteReports'

Çözüm, beklenen büyümeye yetecek tipi çapa kısmında açıkça belirtmektir. Belge CAST kullanır:

WITH RECURSIVE 
cteReports (level, id, mid, name) AS 
(
  SELECT 1, id, cast(mid as double), name FROM t1 WHERE mid IS NULL 
  UNION ALL
  SELECT r.level + 1, e.id, e.mid + 1000000000000, e.name FROM t1 e 
   INNER JOIN cteReports r ON e.mid = r.id 
)
SELECT level, id, mid, name FROM cteReports 
ORDER BY level, mid;

Kategori örneğinde aynı ilkeyi metin sütununa uyguladık: yol sütunu her turda uzar, ama tipini yalnızca çapa kısmı belirler. Bu yüzden çapada CAST(ad AS CHAR(500)) yazarak sütuna beklediğiniz en uzun yol kadar yer bırakırsınız. Uygun genişliği verinize göre seçin ve sorguyu en derin dalınızla deneyin.

Sık yapılan hatalar

• RECURSIVE yazmayı unutmak: kendine başvuran bir CTE, WITH RECURSIVE olmadan tanımlanamaz.
• Döngülü veride UNION ALL kullanmak: graf iki yönlü bağlantı içeriyorsa UNION ALL aynı satırları sonsuza kadar üretir; UNION, yol denetimi veya CYCLE ... RESTRICT kullanın.
• Her turda değişen bir sayaç eklemek: derinlik gibi bir sütun satırları hep farklı kıldığı için UNION DISTINCT döngüyü durduramaz.
• Çapa kısmında dar tip bırakmak: özyinelemeli kısım büyüyen bir değer üretiyorsa çapa kısmında CAST ile genişletin, aksi hâlde 1264 hatası alırsınız.
• max_recursive_iterations değerini körü körüne artırmak: sınıra takılan sorgu çoğu zaman bir döngü belirtisidir; önce sorgunun mantığını kontrol edin.
• CTE'yi sonradan tanımlanana bağlamak: RECURSIVE olmadan bir CTE yalnızca kendisinden önce tanımlanmış CTE'lere başvurabilir.

Sık Sorulan Sorular

CTE görünüm (view) yerine geçer mi?

Kısmen. Özyinelemesiz CTE sorguya özel bir görünüm gibidir ama yalnızca yazıldığı sorgu süresince yaşar. Aynı mantık birçok sorguda gerekiyorsa kalıcı bir görünüm tanımlamak daha uygundur.

CTE'yi INSERT veya UPDATE ile kullanabilir miyim?

Belgedeki WITH sayfası CTE'nin dış SELECT içinde, alt sorgularda ve EXPLAIN ile kullanımını gösterir. Yazma ifadeleriyle kullanım bu sayfada ele alınmadığı için kendi sürümünüzün belgesini kontrol etmeden buna güvenmeyin.

Özyineleme sınırının varsayılan değeri nedir?

max_recursive_iterations varsayılan olarak 1000'dir. Hem genel hem oturum kapsamında dinamik olarak değiştirilebilir.

CTE ile window fonksiyonlarını birlikte kullanabilir miyim?

Evet ve sık kullanılan bir kalıptır: window fonksiyonları WHERE içinde kullanılamadığı için sonucu önce bir CTE'de hesaplayıp dış sorguda süzersiniz. Örnek için MariaDB window fonksiyonları yazısına bakın.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: MariaDB öğrenme rehberi · JOIN ve alt sorgular · Window fonksiyonları · View oluşturma · GROUP BY · SQL nedir