features, command format, and examples of common table expressions - MaxCompute

Ortak Tablo İfadesi (CTE), SQL sorgularını basitleştiren geçici adlandırılmış bir sonuç kümesidir. MaxCompute, SQL deyimlerinin okunabilirliğini ve çalıştırma verimliliğini artırmak için standart SQL CTE'lerini destekler. Bu konuda CTE'lerin özellikleri, söz dizimi ve örnekleri açıklanmaktadır.

Genel Bakış

CTE, tek bir DML deyiminin çalıştırma kapsamı içinde tanımlanan geçici bir sonuç kümesi olarak düşünülebilir. Türetilmiş tabloya benzer şekilde CTE bir nesne olarak depolanmaz ve yalnızca sorgu süresince geçerlidir. Geliştirme sırasında CTE kullanmak SQL okunabilirliğini artırır ve karmaşık sorguların bakımını kolaylaştırır.
CTE, WITH anahtar sözcüğüyle başlayan ve ifade adının geldiği bir deyim düzeyi yan tümcesidir. MaxCompute iki tür CTE'yi destekler:
- NON RECURSIVE CTE: Kendini referans almayan ve yineleme yapmayan bir CTE'dir. Aynı alt sorgu mantığını yeniden kullanan sorguları basitleştirmek için bu türü kullanın.
- RECURSIVE CTE: Kendini yinelemeli olarak referans alabilen ve SQL'de özyinelemeli sorgu yetenekleri sağlayan bir CTE'dir. Genellikle organizasyon şemaları gibi hiyerarşik verileri gezinmek için kullanılır.
Materialize Edilmiş CTE: Bir CTE tanımlarken, SELECT deyimine MATERIALIZE HINT ekleyerek CTE sonucunu geçici bir tabloda önbelleğe alın. Sonraki başvurular önbellekten okuma yapar; bu durum, derin iç içe geçmiş CTE senaryolarında bellek sınırı sorunlarını önler ve performansı artırır.

NON RECURSIVE CTE

Söz Dizimi

WITH
  <cte_name> [(col_name [, col_name] ...)] AS (
    <cte_query>
  )
  [, <cte_name> [(col_name [, col_name] ...)] AS (
    <cte_query2>
  )
  , ...]

Parametreler

Parametre	Zorunlu	Açıklama
`cte_name`	Evet	CTE'nin adı. `WITH` yan tümcesi içinde benzersiz olmalıdır. Deyimde daha sonra `cte_name` yapılan herhangi bir başvuru bu CTE'den okuma yapar.
`col_name`	Hayır	CTE için çıktı sütun adları. Atlanırsa, sütun adları `cte_query` içindeki `SELECT` listesinden devralınır.
`cte_query`	Evet	Sonuç kümesi CTE'yi tanımlayan bir `SELECT` deyimidir.

Örnek

Aşağıdaki sorgu, iki JOIN işlemini birleştirmek için bir UNION ALL kullanır. Her iki birleştirme de aynı sol taraflı alt sorguyu paylaşır; CTE olmadan bu alt sorgunun çoğaltılması gerekir:

INSERT OVERWRITE TABLE srcp PARTITION (p='abc')
SELECT * FROM (
    SELECT a.key, b.value
    FROM (
        SELECT * FROM src WHERE key IS NOT NULL) a
    JOIN (
        SELECT * FROM src2 WHERE value > 0) b
    ON a.key = b.key
) c
UNION ALL
SELECT * FROM (
    SELECT a.key, b.value
    FROM (
        SELECT * FROM src WHERE key IS NOT NULL) a
    LEFT OUTER JOIN (
        SELECT * FROM src3 WHERE value > 0) b
    ON a.key = b.key AND b.key IS NOT NULL
) d;

Bir CTE ile yeniden yazıldığında çoğaltma ortadan kalkar. Alt sorgu a bir kez tanımlanır ve her iki birleştirme tarafından yeniden kullanılır:

WITH
  a AS (SELECT * FROM src WHERE key IS NOT NULL),
  b AS (SELECT * FROM src2 WHERE value > 0),
  c AS (SELECT * FROM src3 WHERE value > 0),
  d AS (SELECT a.key, b.value FROM a JOIN b ON a.key = b.key),
  e AS (SELECT a.key, c.value FROM a LEFT OUTER JOIN c ON a.key = c.key AND c.key IS NOT NULL)
INSERT OVERWRITE TABLE srcp PARTITION (p='abc')
SELECT * FROM d UNION ALL SELECT * FROM e;

RECURSIVE CTE

Söz Dizimi

WITH RECURSIVE <cte_name> [(col_name [, col_name] ...)] AS (
  <initial_part> UNION ALL <recursive_part>
)
SELECT ... FROM ...;

Parametreler

Parametre	Zorunlu	Açıklama
`RECURSIVE`	Evet	Özyinelemeli CTE yan tümcesi `WITH RECURSIVE` ile başlamalıdır.
`cte_name`	Evet	CTE'nin adı. Geçerli `WITH` yan tümcesi içinde benzersiz olmalıdır.
`col_name`	Hayır	Çıktı sütun adları. Atlanırsa, sütun adları `initial_part` bölümünden çıkarılır.
`initial_part`	Evet	Tohum veri kümesini (yineleme 0) üreten bir `SELECT` deyimidir. `cte_name` öğesine başvuramaz.
`recursive_part`	Evet	Bir önceki yinelemeden sonraki yinelemeyi hesaplamak üzere `cte_name` öğesine başvuran bir `SELECT` deyimidir.
`UNION ALL`	Evet	`initial_part` ve `recursive_part` bölümlerini birbirine bağlar. `ALL` içermeyen `UNION` desteklenmez.

Sınırlamalar

Özyinelemeli CTE'ler IN, EXISTS veya skaler alt sorgularda yer alamaz.
Varsayılan maksimum yineleme sayısı 10'dur. odps.sql.rcte.max.iterate.num parametresini ayarlayarak sınırı artırın (maksimum değer: 100).
Yinelemeler arasında ara sonuçlar kaydedilmez. Görev başarısız olursa, çalıştırma baştan başlar. Uzun süren özyinelemeli hesaplamalar için ya yineleme sayısını sınırlayın ya da ara sonuçları geçici bir tabloda saklayın.
Özyinelemeli CTE'ler Query Acceleration (MaxQA/MCQA) modunda desteklenmez.
- MCQA modunda, interactive_auto_rerun=true ayarlanmışsa görev normal moda geri döner. Aksi takdirde görev başarısız olur.
- MaxQA modunda otomatik geri dönüş desteklenmez. İş doğrudan başarısız olur ve yeniden denemek için manuel olarak toplu işlem kota grubuna gönderilmelidir.

Örnekler

Örnek 1: cte_name adlı bir özyinelemeli CTE tanımlama.

-- Method 1: Explicitly specify output column names
WITH RECURSIVE cte_name(a, b) AS (
  SELECT 1L, 1L                                   -- initial_part: iteration 0
  UNION ALL
  SELECT a+1, b+1 FROM cte_name WHERE a+1 <= 5    -- recursive_part: references previous iteration
)
SELECT * FROM cte_name ORDER BY a LIMIT 100;

-- Method 2: Infer column names from initial_part
WITH RECURSIVE cte_name AS (
  SELECT 1L AS a, 1L AS b
  UNION ALL
  SELECT a+1, b+1 FROM cte_name WHERE a+1 <= 5
)
SELECT * FROM cte_name ORDER BY a LIMIT 100;

Not

Sonsuz döngüyü önlemek için recursive_part bölümünde bir sonlandırma koşulu belirleyin. Bu örnekte, WHERE a + 1 <= 5 sonlandırma kriteri olarak hizmet eder. WHERE koşulu karşılanmazsa, geçerli yinelemede oluşturulan veri kümesi boş olur ve yineleme durur.
Çıktı sütun adları açıkça belirtilmezse sistem otomatik çıkarsamayı destekler. Örneğin, Yöntem 2'de initial_part bölümünün çıktı sütun adları, özyinelemeli CTE'nin çıktı sütun adları olarak kullanılır.

Sonuç:

+------------+------------+
| a          | b          |
+------------+------------+
| 1          | 1          |
| 2          | 2          |
| 3          | 3          |
| 4          | 4          |
| 5          | 5          |
+------------+------------+

Örnek 2: Alt sorguda özyinelemeli CTE (derleme hatası)

Özyinelemeli CTE'lerin IN, EXISTS veya skaler alt sorgular içinde kullanılmasına izin verilmez. Aşağıdaki sorgu derlenemez:

WITH RECURSIVE cte_name(a, b) AS (
  SELECT 1L, 1L
  UNION ALL
  SELECT a+1, b+1 FROM cte_name WHERE a+1 <= 5)
SELECT x, x IN (SELECT a FROM cte_name) FROM VALUES (1L), (2L) AS t(x);

Hata:

FAILED: ODPS-0130071:[5,31] Semantic analysis exception - using Recursive-CTE cte_name in scalar/in/exists sub-query is not allowed, please check your query, the query text location is from [line 5, column 13] to [line 5, column 40]

Örnek 3: Organizasyon hiyerarşisini gezinme

Bir employees tablosu oluşturun ve veri ekleyin:

CREATE TABLE employees(name STRING, boss_name STRING);
INSERT INTO TABLE employees VALUES
  ('zhang_3', null),
  ('li_4',    'zhang_3'),
  ('wang_5',  'zhang_3'),
  ('zhao_6',  'li_4'),
  ('qian_7',  'wang_5');

Üç çıktı sütunu içeren company_hierarchy adlı bir özyinelemeli CTE tanımlayın: çalışanın adı, yöneticisinin adı ve hiyerarşideki seviyesi:

WITH RECURSIVE company_hierarchy(name, boss_name, level) AS (
    SELECT name, boss_name, 0L FROM employees WHERE boss_name IS NULL
    UNION ALL
    SELECT e.name, e.boss_name, h.level + 1
      FROM employees e
      JOIN company_hierarchy h ON e.boss_name = h.name
)
SELECT * FROM company_hierarchy ORDER BY level, boss_name, name LIMIT 1000;

Çalıştırma aşağıdaki gibi ilerler:

Yineleme 0 (initial_part): boss_name IS NULL olan çalışanları seçer ve level = 0 atar. Sonuç: ('zhang_3', NULL, 0).
Yineleme 1 (recursive_part): employees tablosunu çalışma tablosuyla (yineleme 0) birleştirir. e.boss_name = h.name koşulu, zhang_3 tarafından yönetilen çalışanları bulur. Sonuç: level = 1 seviyesinde li_4 ve wang_5.
Yineleme 2: li_4 veya wang_5 tarafından yönetilen çalışanları bulur. Sonuç: level = 2 seviyesinde zhao_6 ve qian_7.
Yineleme 3: Yöneticisi zhao_6 veya qian_7 olan çalışan yoktur. Çalışma tablosu boştur ve yineleme durur.

Sonuç:

+---------+-----------+------------+
| name    | boss_name | level      |
+---------+-----------+------------+
| zhang_3 | NULL      | 0          |
| li_4    | zhang_3   | 1          |
| wang_5  | zhang_3   | 1          |
| zhao_6  | li_4      | 2          |
| qian_7  | wang_5    | 2          |
+---------+-----------+------------+

Örnek 4: Döngüsel veriler sonsuz döngüye neden olur

Örnek 3'teki employees tablosuna, bir çalışanın kendi yöneticisi olduğu bir kayıt ekleyin.
```
INSERT INTO TABLE employees VALUES('qian_7', 'qian_7');
```
Bu kayıt, qian_7 kişisinin yöneticisinin qian_7 kendisi olduğunu belirtir. Daha önce tanımlanan özyinelemeli CTE'yi çalıştırmak sonsuz döngüye yol açar. Sistem maksimum yineleme sayısını sınırlar. Sorgu sonunda başarısız olur.

Hata:
```
FAILED: ODPS-0010000:System internal error - recursive-cte: company_hierarchy exceed max iterate number 10
```

Materialize Edilmiş CTE

Özyinelemeli olmayan CTE'ler için MaxCompute, bir çalıştırma planı oluştururken tüm CTE'leri satır içi olarak genişletir. Örneğin:

WITH v1 AS (SELECT SIN(1.0) AS a)
SELECT a FROM v1 UNION ALL SELECT a FROM v1;

Sonuçlar:

+------------+
| a          |
+------------+
| 0.8414709848078965 |
| 0.8414709848078965 |
+------------+

Bu, SIN(1.0) işlevinin iki kez çalıştırılmasına eşdeğerdir:

SELECT a FROM (SELECT SIN(1.0) AS a)
UNION ALL
SELECT a FROM (SELECT SIN(1.0) AS a);

Derin iç içe geçmiş CTE'ler içeren karmaşık senaryolarda, tüm CTE'ler en temel yaprak düğümlere genişletilirse çok büyük bir söz dizimi ağacı ortaya çıkar. Bu durum, çalıştırma planı oluşturma sırasında aşırı söz dizimi ağacı düğümleri nedeniyle başarısızlıklara ve bellek sınırı sorunlarına yol açabilir. Örneğin:

WITH
  v1 AS (SELECT 1L AS a, 2L AS b, 3L AS c),
  v2 AS (SELECT * FROM v1 UNION ALL SELECT * FROM v1 UNION ALL SELECT * FROM v1),
  v3 AS (SELECT * FROM v2 UNION ALL SELECT * FROM v2 UNION ALL SELECT * FROM v2),
  v4 AS (SELECT * FROM v3 UNION ALL SELECT * FROM v3 UNION ALL SELECT * FROM v3),
  v5 AS (SELECT * FROM v4 UNION ALL SELECT * FROM v4 UNION ALL SELECT * FROM v4),
  v6 AS (SELECT * FROM v5 UNION ALL SELECT * FROM v5 UNION ALL SELECT * FROM v5),
  v7 AS (SELECT * FROM v6 UNION ALL SELECT * FROM v6 UNION ALL SELECT * FROM v6),
  v8 AS (SELECT * FROM v7 UNION ALL SELECT * FROM v7 UNION ALL SELECT * FROM v7),
  v9 AS (SELECT * FROM v8 UNION ALL SELECT * FROM v8 UNION ALL SELECT * FROM v8)
SELECT * FROM v9;

Bu sorunu çözmek için MaxCompute, CTE hesaplama sonuçlarını WITH yan tümcesinin dışındaki SQL tarafından tam genişletme olmadan başvurmak üzere önbelleğe alan materialize edilmiş CTE özelliğini sağlar. Bu mekanizma, iç içe geçmiş CTE genişletmesinden kaynaklanan bellek sınırı sorunlarını etkili bir şekilde önler ve CTE deyimi performansını artırır.

Kullanım örneği

Özyinelemeli olmayan bir CTE'nin üst düzey SELECT ifadesine /*+ MATERIALIZE */ ipucunu ekleyerek sonucunu geçici bir tabloda önbelleğe alın. Sonraki başvurular sorguyu yeniden çalıştırmak yerine önbellekten okuma yapar:

WITH v1 AS (SELECT /*+ MATERIALIZE */ SIN(1.0) AS a)
SELECT a FROM v1 UNION ALL SELECT a FROM v1;

-- Result:
+------------+
| a          |
+------------+
| 0.8414709848078965 |
| 0.8414709848078965 |
+------------+

İpucu etkin olduğunda, LogView'deki Job Details sekmesi birden fazla Fuxi Job gösterir; bu durum ara sonucun depolandığını doğrular.

Sınırlamalar

MATERIALIZE ipucu, özyinelemeli olmayan bir CTE'nin üst düzey SELECT deyimine uygulanmalıdır. Özyinelemeli CTE'lerin MATERIALIZE ipucuna ihtiyacı yoktur.
- Yanlış örnek: Aşağıdaki CTE'de üst düzey deyim SELECT yerine UNION olduğundan MATERIALIZE ipucu etkili olmaz.
```
WITH v1 AS (
  SELECT /*+ MATERIALIZE */ SIN(1.0) AS a
  UNION ALL
  SELECT /*+ MATERIALIZE */ SIN(1.0) AS a)
SELECT a FROM v1 UNION ALL SELECT a FROM v1;
```
- Doğru örnek: Yukarıdaki yanlış örneği bir alt sorguyla sararak yeniden yazın.
```
WITH 
  v1 AS (SELECT /*+ MATERIALIZE */ * FROM 
           (SELECT SIN(1.0) AS a
            UNION ALL 
            SELECT SIN(1.0) AS a)
        ) 
SELECT a FROM v1 UNION ALL SELECT a FROM v1;
```
CTE, RAND gibi deterministik olmayan bir işlev veya deterministik olmayan bir Java/Python UDF kullanıyorsa, CTE'yi materialize etmek işlevin tek bir değerlendirmesini önbelleğe alır. Sonraki başvurular önbelleğe alınan değeri döndürür; bu durum, çağrı başına bağımsız rastgele değerler bekleyen sorguların anlamsal davranışını değiştirir.
Materialize edilmiş CTE'ler MCQA(Query Acceleration 1.0) -kullanımdan kaldırıldı içinde desteklenmez. Görev interactive_auto_rerun=true ayarıyla MCQA modunda çalışırsa normal moda geri döner. Aksi takdirde görev başarısız olur.