MaxCompute：Common Table Expression (CTE)

Fitur

• CTE berfungsi sebagai hasil set sementara yang didefinisikan dalam lingkup satu pernyataan DML. Mirip dengan tabel turunan, CTE tidak disimpan sebagai objek dan hanya ada selama durasi kueri. CTE meningkatkan kemampuan pemeliharaan dan keterbacaan pernyataan SQL yang kompleks selama pengembangan.

• CTE adalah klausa tingkat pernyataan yang dimulai dengan WITH, diikuti oleh nama ekspresi. Ini mencakup dua jenis:

  • Non-rekursif CTE: Jenis ini tidak melibatkan rekursi atau iterasi referensi diri.

  • Rekursif CTE: Jenis ini memungkinkan iterasi referensi diri, memungkinkan SQL melakukan kueri rekursif, biasanya untuk menelusuri data hierarkis.

• Dukungan untuk CTE materialisasi: Saat mendefinisikan CTE, Anda dapat menggunakan petunjuk materialize dalam pernyataan SELECT untuk menyimpan hasil perhitungan CTE ke dalam tabel sementara. Hal ini memungkinkan akses berikutnya ke CTE membaca langsung dari cache, mencegah masalah melebihi batas memori dalam skenario nesting CTE yang kompleks dan meningkatkan kinerja pernyataan CTE.

Non-rekursif CTE

Sintaksis Perintah

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

Parameter

Contoh Penggunaan

Kode berikut menunjukkan sebuah contoh:

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;

UNION tingkat atas menggabungkan dua operasi JOIN, dengan tabel kiri dari JOIN menjadi pernyataan kueri yang sama. Tanpa CTE, kode ini harus diduplikasi dalam subkueri.

Menggunakan CTE untuk menulis ulang pernyataan, contoh perintahnya adalah sebagai berikut:

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;

Dengan penulisan ulang ini, subkueri untuk a hanya ditulis sekali dan dapat digunakan kembali. Beberapa subkueri dapat didefinisikan dalam klausa WITH CTE dan digunakan kembali di seluruh pernyataan, menghilangkan kebutuhan untuk pengulangan bersarang.

Rekursif CTE

Sintaksis Perintah

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

Parameter

Batasan

• CTE rekursif tidak dapat digunakan dalam subkueri IN, EXISTS, dan SCALAR.

• Batasan default untuk jumlah operasi rekursif dalam CTE rekursif adalah 10. Batasan ini dapat disesuaikan dengan mengatur odps.sql.rcte.max.iterate.num, dengan maksimum 100 iterasi.

• CTE rekursif tidak mendukung pencatatan hasil antara selama iterasi. Jika terjadi kegagalan, perhitungan akan dimulai ulang dari awal. Kami menyarankan Anda untuk mengontrol jumlah rekursi atau menyimpan hasil antara dalam tabel sementara jika komputasi memerlukan waktu lama.

• CTE rekursif tidak didukung dalam MaxCompute Query Acceleration. Jika digunakan dalam mode MCQA, tugas akan kembali ke mode eksekusi normal ketika interactive_auto_rerun=true diatur atau gagal jika tidak.

Contoh Penggunaan

• Contoh 1: Tentukan CTE rekursif bernama cte_name.

  -- Metode 1: Tentukan CTE rekursif bernama cte_name, berisi dua kolom keluaran bernama a dan b
  WITH RECURSIVE cte_name(a, b) AS (       
    SELECT 1L, 1L                                 -- initial_part: dataset iterasi 0
    UNION ALL                                      -- Hubungkan initial_part dan recursive_part CTE menggunakan UNION ALL
    SELECT a+1, b+1 FROM cte_name WHERE a+1 <= 5) -- recursive_part: di mana cte_name menunjuk ke hasil perhitungan iterasi sebelumnya
  SELECT * FROM cte_name ORDER BY a LIMIT 100;    -- Keluarkan hasil CTE rekursif, yaitu gabungkan semua data dari semua iterasi
  
  -- Metode 2: Tentukan CTE rekursif bernama cte_name tanpa menentukan nama kolom keluaran secara eksplisit
  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;    

  null

  • Dalam recursive_part, untuk mencegah loop tak terbatas, atur kondisi terminasi untuk iterasi, seperti WHERE a + 1 <= 5, yang mengakhiri iterasi ketika dataset yang dihasilkan kosong.

  • Jika nama kolom keluaran tidak ditentukan, sistem dapat menginferensinya secara otomatis. Misalnya, dalam Metode 2, nama kolom keluaran dari initial_part digunakan untuk CTE rekursif.

  Hasil berikut dikembalikan:

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

• Contoh 2: CTE rekursif tidak diizinkan dalam subkueri IN, EXISTS, dan SCALAR. Kueri berikut akan gagal dikompilasi:

  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);

  Hasil berikut dikembalikan:

  -- Mengembalikan kesalahan, ada sub-kueri in pada baris 5, dan CTE rekursif cte_name direferensikan dalam sub-kueri
  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]

• Contoh 3: Buat tabel employees untuk memetakan karyawan perusahaan ke manajer mereka dan sisipkan data:

  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');

  Tentukan CTE rekursif bernama company_hierarchy untuk menurunkan struktur organisasi dari tabel ini. CTE mengeluarkan tiga bidang: nama karyawan, nama manajer mereka, dan level mereka dalam struktur organisasi.

  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 WHERE e.boss_name = h.name
    )
  SELECT * FROM company_hierarchy ORDER BY level, boss_name, name LIMIT 1000;

  • Initial_part mewakili catatan dalam tabel employees di mana boss_name adalah null, memberikan level 0 untuk entri ini.

  • Baris keempat, recursive_part, menjalankan operasi JOIN antara tabel employees dan company_hierarchy. Kondisi JOIN, e.boss_name = h.name, dirancang untuk mengambil catatan dari tabel employees yang sesuai dengan karyawan yang manajernya diidentifikasi dalam iterasi sebelumnya.

  Hasil berikut dikembalikan:

  +------+-----------+------------+
  | 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          |
  +------+-----------+------------+

  Proses perhitungan adalah sebagai berikut:

  • Iterasi 0: Pilih catatan awal dari employees di mana boss_name IS NULL, menghasilkan satu catatan.

    'zhang_3',   null,  0

  • Iterasi 1: Lakukan kueri berikut menggunakan hasil dari iterasi 0 sebagai company_hierarchy.

    SELECT e.name, e.boss_name, h.level+1 FROM employees e JOIN company_hierarchy h  WHERE e.boss_name = h.name

    Ini menghasilkan dua catatan dengan level = 1, keduanya dikelola oleh zhang_3.

    'li_4',      'zhang_3', 1
    'wang_5',    'zhang_3',  1

  • Iterasi 2: Mirip dengan iterasi 1, tetapi dengan company_hierarchy sesuai dengan hasil iterasi 1. Ini menghasilkan dua catatan dengan level = 2, dikelola oleh li_4 atau wang_5.

    'zhao_6',    'li_4',   2
    'qian_7',    'wang_5', 2

  • Iterasi 3: Tidak ada karyawan lebih lanjut dalam tabel yang memiliki zhao_6 atau qian_7 sebagai manajer, menghasilkan set kosong dan mengakhiri rekursi.

• Contoh 4: Jika catatan tambahan dimasukkan ke dalam tabel employees dari Contoh 2, kesalahan akan dikembalikan.

  INSERT INTO TABLE employees VALUES('qian_7', 'qian_7');

  Catatan ini menunjukkan bahwa qian_7 adalah manajernya sendiri. Menjalankan CTE rekursif yang telah didefinisikan sebelumnya akan menghasilkan loop tak terbatas. Sistem memberlakukan batasan pada jumlah maksimum iterasi. Untuk informasi lebih lanjut, lihat Batasan. Kueri ini akhirnya gagal dan menghentikan tugas.

  Hasil berikut dikembalikan:

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

Materialize CTE

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

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

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

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;

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

-- Hasil berikut dikembalikan.
+------------+
| a          |
+------------+
| 0.8414709848078965 |
| 0.8414709848078965 |
+------------+