Ekspresi Tabel Umum (CTE)

Berlaku untuk: Databricks SQL Databricks Runtime

Menentukan tataan hasil sementara yang dapat Anda rujuk mungkin beberapa kali dalam lingkup pernyataan SQL. CTE digunakan terutama dalam pernyataan SELECT.

Sintaks

WITH [ RECURSIVE ] common_table_expression [, ...]

common_table_expression
  view_identifier [ ( column_identifier [, ...] ) ] [ recursion_limit ] [ AS ] ( query | recursive_query )

recursion_limit
  MAX RECURSION LEVEL maxLevel

recursive_query
  base_case_query UNION ALL step_case_query

Parameter

• REKURSIF

  Berlaku untuk: Databricks SQL Databricks Runtime 17.0 dan versi yang lebih baru

  Saat ditentukan, ekspresi tabel umum dapat berisi recursive_query.

• pengidentifikasi_tampilan

  Sebuah pengidentifikasi yang dapat digunakan untuk merujuk common_table_expression

• column_identifier

  Pengidentifikasi opsional yang digunakan untuk merujuk kolom common_table_expression.

  Jika column_identifiers ditentukan maka nomor mereka harus cocok dengan jumlah kolom yang dihasilkan oleh query. Jika tidak ada nama yang ditentukan, nama kolom berasal dari query.

• MAX RECURSION LEVEL maxLevel

  Berlaku untuk: Databricks SQL Databricks Runtime 17.0 dan versi yang lebih baru

  Menentukan jumlah maksimum langkah rekursif yang dapat dilakukan oleh CTE rekursif. maxLevel harus berupa BILANGAN BULAT harfiah > 0. Nilai default adalah 100.

  Jika maxLimit terlampaui, maka RECURSION_LEVEL_LIMIT_EXCEEDED akan dinaikkan. Jika CTE tidak rekursif, klausa ini diabaikan.

• kueri

  Kueri yang menghasilkan tataan hasil.

• recursive_query

  Berlaku untuk: Databricks SQL Databricks Runtime 17.0 dan versi yang lebih baru

  Ketika RECURSIVE ditentukan, kueri CTE dapat merujuk ke dirinya sendiri. Ini membuat CTE menjadi CTE rekursif.

  • base_case_subquery

    Subkueri ini menyediakan benih atau jangkar untuk rekursi. Ini tidak boleh mereferensikan view_identifier.

  • step_subquery

    Subkueri ini menghasilkan sekumpulan baris baru berdasarkan langkah sebelumnya. Untuk menjadi rekursif, itu harus mereferensikan view_identifier.

Keterbatasan

• CTEs rekursif tidak didukung untuk pernyataan UPDATE, DELETE, dan MERGE.

• step_subquery tidak boleh menyertakan referensi kolom yang berkorelasi ke view_identifier.

• Memanggil generator angka acak dari bagian rekursif dapat menghasilkan angka acak yang sama di setiap iterasi.

• Ukuran total kumpulan hasil dibatasi hingga 1.000.000 baris. Batas ini dapat ditimpa jika SELECT pernyataan yang menggerakkan CTE rekursif mencakup LIMIT klausul, yang secara efektif mengendalikan rekursi.

  Berlaku untuk: Databricks Runtime 17.2 ke atas

  LIMIT ALL menghentikan batas sepenuhnya.

Kondisi kesalahan umum

• AMBIGUOUS_ALIAS_IN_NESTED_CTE
• CANNOT_MERGE_INCOMPATIBLE_DATA_TYPE
• DUPLICATED_CTE_NAMES
• INVALID_RECURSIVE_CTE
• INVALID_RECURSIVE_REFERENCE. TEMPAT
• RECURSION_LEVEL_LIMIT_EXCEEDED
• RECURSION_ROW_LIMIT_EXCEEDED
• UNION_NOT_SUPPORTED_IN_RECURSIVE_CTE

Contoh

Contoh CTE dasar

CTE dengan beberapa alias kolom

> WITH t(x, y) AS (SELECT 1, 2)
  SELECT * FROM t WHERE x = 1 AND y = 2;
   1   2

CTE dalam definisi CTE

> WITH t AS (
    WITH t2 AS (SELECT 1)
    SELECT * FROM t2)
  SELECT * FROM t;
   1

CTE dalam subkueri

> SELECT max(c) FROM (
    WITH t(c) AS (SELECT 1)
    SELECT * FROM t);
      1

CTE dalam ekspresi subkueri

> SELECT (WITH t AS (SELECT 1)
          SELECT * FROM t);
                1

CTE dalam CREATE VIEW pernyataan

> CREATE VIEW v AS
    WITH t(a, b, c, d) AS (SELECT 1, 2, 3, 4)
    SELECT * FROM t;
> SELECT * FROM v;
   1   2   3   4

Nama CTE dibatasi ruang lingkupnya

> WITH t  AS (SELECT 1),
       t2 AS (
        WITH t AS (SELECT 2)
        SELECT * FROM t)
    SELECT * FROM t2;
   2

Contoh kueri rekursif

Contoh grafik yang diarahkan

Contoh berikut menunjukkan cara menemukan semua tujuan dari New York ke kota lain, termasuk rute langsung dan yang dirutekan melalui kota lain:

> DROP VIEW IF EXISTS routes;
> CREATE TEMPORARY VIEW routes(origin, destination) AS VALUES
  ('New York', 'Washington'),
  ('New York', 'Boston'),
  ('Boston', 'New York'),
  ('Washington', 'Boston'),
  ('Washington', 'Raleigh');

> WITH RECURSIVE destinations_from_new_york AS (
    SELECT 'New York' AS destination, ARRAY('New York') AS path, 0 AS length
    UNION ALL
    SELECT r.destination, CONCAT(d.path, ARRAY(r.destination)), d.length + 1
      FROM routes AS r
      JOIN destinations_from_new_york AS d
        ON d.destination = r.origin AND NOT ARRAY_CONTAINS(d.path, r.destination)
  )
  SELECT * FROM destinations_from_new_york;
  destination path                                length
  ----------- ----------------------------------- ------
  New York    ["New York"]                        0
  Washington  ["New York","Washington"]           1
  Boston      ["New York","Boston"]               1
  Boston      ["New York","Washington","Boston"]  2
  Raleigh     ["New York","Washington","Raleigh"] 2

Contoh berikut menunjukkan cara melintasi graf berarah dan memeriksa keberadaan perulangan tak terbatas selama pelintasan.

> DROP TABLE IF EXISTS graph;
> CREATE TABLE IF NOT EXISTS graph( f int, t int, label string );
> INSERT INTO graph VALUES
    (1, 2, 'arc 1 -> 2'),
    (1, 3, 'arc 1 -> 3'),
    (2, 3, 'arc 2 -> 3'),
    (1, 4, 'arc 1 -> 4'),
    (4, 5, 'arc 4 -> 5'),
    (5, 1, 'arc 5 -> 1');
> WITH RECURSIVE search_graph(f, t, label, path, cycle) AS (
    SELECT *, array(struct(g.f, g.t)), false FROM graph g
    UNION ALL
    SELECT g.*, path || array(struct(g.f, g.t)), array_contains(path, struct(g.f, g.t))
      FROM graph g, search_graph sg
      WHERE g.f = sg.t AND NOT cycle
  )
  SELECT path FROM search_graph;

Hasilnya adalah serangkaian jalur dengan panjang yang meningkat sambil menghindari siklus dan berakhir dengan jalur yang tidak terbatas

[{"f":1,"t":4},{"f":4,"t":5},{"f":5,"t":1},{"f":1,"t":2},{"f":2,"t":3}]`

yang mengunjungi semua node dengan perhentian yang jelas di sink "3". Pencarian yang mengutamakan kedalaman dicapai dengan memodifikasi baris terakhir untuk mengurutkan berdasarkan kolom path.

SELECT * FROM search_graph ORDER BY path;

Teknik rekursif dasar

Contoh seperti perulangan sederhana

> WITH RECURSIVE r(col) AS (
    SELECT 'a'
    UNION ALL
    SELECT col || char(ascii(substr(col, -1)) + 1) FROM r WHERE LENGTH(col) < 10
  )
  SELECT * FROM r;
  col
  ----------
  a
  ab
  abc
  abcd
  abcde
  abcdef
  abcdefg
  abcdefgh
  abcdefghi
  abcdefghij

Contoh seperti perulangan lain

> WITH RECURSIVE t(n) AS (
    SELECT 'foo'
    UNION ALL
    SELECT n || ' bar' FROM t WHERE length(n) < 20
  )
  SELECT n AS is_text FROM t;
  is_text
  -------
  foo
  foo bar
  foo bar bar
  foo bar bar bar
  foo bar bar bar bar
  foo bar bar bar bar bar

Memanggil CTE rekursif dari CTE lain

> WITH RECURSIVE x(id) AS (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
                 y(id) AS (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 10)
  SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
  id  id
  --  --
   1  1
   4  4
   6  null
   3  3
   5  5
   2  2

Memanggil CTE non-rekursif dari CTE rekursif

> WITH RECURSIVE
    y (id) AS (VALUES (1)),
    x (id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5)
  SELECT * FROM x;
  id
  --
   1
   2
   3
   4
   5

Tampilan sementara

> CREATE OR REPLACE TEMPORARY VIEW nums AS
    WITH RECURSIVE nums (n) AS (
      VALUES (1)
      UNION ALL
      SELECT n+1 FROM nums WHERE n < 6
    )
    SELECT * FROM nums;
> SELECT * FROM nums;
  n
  __
   1
   2
   3
   4
   5
   6

INSERT ke dalam tabel

> CREATE TABLE rt(level INT);

> WITH RECURSIVE r(level) AS (
    VALUES (0)
    UNION ALL
    SELECT level + 1 FROM r WHERE level < 9
  )
  INSERT INTO rt SELECT * FROM r;

> SELECT * from rt;
  level
  -----
      0
      1
      2
      3
      4
      5
      6
      7
      8
      9

CTE rekursif berlapis

-- Nested recursive CTE are supported (only inside anchor)
-- Returns a triangular half – total of 55 rows – of a 10x10 square of numbers 1-10
> WITH RECURSIVE t(j) AS (
    WITH RECURSIVE s(i) AS (
      VALUES (1)
      UNION ALL
      SELECT i+1 FROM s WHERE i < 10
    )
    SELECT i FROM s
    UNION ALL
    SELECT j+1 FROM t WHERE j < 10
  )
  SELECT * FROM t;
  j
  --
   1
   2
   2
   3
   3
   3
   4
  ...
   9
  10
  10
  10
  10
  10
  10
  10
  10
  10
  10

Menghormati LIMIT

> WITH RECURSIVE t(n) AS (
    VALUES (1)
    UNION ALL
    SELECT n+1 FROM t
  )
  SELECT * FROM t LIMIT 10;
  n
  --
   1
   2
   3
   4
   5
   6
   7
   8
   9
  10

Contoh pohon organisasi

Ekstraksi semua departemen di bawah 'A'

> CREATE TABLE department (
    id INTEGER, -- department ID
    parent_department INTEGER, -- upper department ID
    name string -- department name
  );

> INSERT INTO department VALUES
    (0, NULL, 'ROOT'),
    (1, 0, 'A'),
    (2, 1, 'B'),
    (3, 2, 'C'),
    (4, 2, 'D'),
    (5, 0, 'E'),
    (6, 4, 'F'),
    (7, 5, 'G');

> WITH RECURSIVE subdepartment AS (
    SELECT name as root_name, * FROM department WHERE name = 'A'
    UNION ALL
    SELECT sd.root_name, d.* FROM department AS d, subdepartment AS sd
     WHERE d.parent_department = sd.id
  )
  SELECT * FROM subdepartment ORDER BY name;
  root_name id  parent_department  name
  --------- --  -----------------  ----
          A	 1                  0     A
          A	 2	                1     B
          A	 3                  2     C
          A	 4                  2     D
          A	 6	                4     F

Mengekstrak nomor tingkat

> WITH RECURSIVE subdepartment(level, id, parent_department, name) AS (
    SELECT 1, * FROM department WHERE name = 'A'
    UNION ALL
    SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
     WHERE d.parent_department = sd.id
  )
  SELECT * FROM subdepartment ORDER BY name;
  level  id  parent_department  name
  -----  --  -----------------  ----
      1   1                  0     A
      2   2                  1     B
      3   3                  2     C
      3   4                  2     D
      4   6                  4     F

Pemfilteran menurut tingkat (tingkat 2 atau lebih)

> WITH RECURSIVE subdepartment(level, id, parent_department, name) AS (
    SELECT 1, * FROM department WHERE name = 'A'
    UNION ALL
    SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
     WHERE d.parent_department = sd.id
  )
  SELECT * FROM subdepartment WHERE level >= 2 ORDER BY name;
  level id  parent_department  name
  ----- --  -----------------  ----
  2      2                  1     B
  3      3                  2     C
  3      4                  2     D
  4      6                  4     F

Contoh pohon biner

Menemukan semua jalur dari simpul tingkat kedua

-- Another tree example is to find all paths from the second level nodes "2" and "3" to all other nodes.
> CREATE TABLE tree(id INTEGER, parent_id INTEGER);
> INSERT INTO tree
    VALUES (1, NULL), (2, 1), (3, 1), (4, 2), (5, 2), ( 6, 2), ( 7, 3), ( 8, 3),
           (9,    4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11);

> WITH RECURSIVE t(id, path) AS (
    VALUES(1,cast(array() as array<Int>))
    UNION ALL
    SELECT tree.id, t.path || array(tree.id)
      FROM tree JOIN t ON (tree.parent_id = t.id)
  )
  SELECT t1.*, t2.*
    FROM t AS t1 JOIN t AS t2
      ON (t1.path[0] = t2.path[0] AND
          size(t1.path) = 1 AND
          size(t2.path) > 1)
  ORDER BY t1.id, t2.id;
  id  path  id  path
  --  ----  --  ------------
   2  [2]    4  [2,4]
   2  [2]    5  [2,5]
   2  [2]    6  [2,6]
   2  [2]    9  [2,4,9]
   2  [2]   10  [2,4,10]
   2  [2]   14  [2,4,9,14]
   3  [3]    7  [3,7]
   3  [3]    8  [3,8]
   3  [3]   11  [3,7,11]
   3  [3]   12  [3,7,12]
   3  [3]   13  [3,7,13]
   3  [3]   15  [3,7,11,15]
   3  [3]   16  [3,7,11,16]

Menghitung simpul daun yang dapat dijangkau dari simpul

> CREATE TABLE tree(id INTEGER, parent_id INTEGER);
> INSERT INTO tree
    VALUES (1, NULL), ( 2,1), (3,1 ), ( 4,2), ( 5,2), ( 6,2), ( 7, 3), ( 8, 3),
           (9,    4), (10,4), (11,7), (12,7), (13,7), (14,9), (15,11), (16,11);

> WITH RECURSIVE t(id, path) AS (
    VALUES(1,cast(array() as array<Int>))
    UNION ALL
    SELECT tree.id, t.path || array(tree.id)
      FROM tree JOIN t ON (tree.parent_id = t.id)
  )
  SELECT t1.id, count(*)
    FROM t AS t1 JOIN t AS t2
      ON (t1.path[0] = t2.path[0] AND
          size(t1.path) = 1 AND
          size(t2.path) > 1)
  GROUP BY t1.id
  ORDER BY t1.id;
  id  count(1)
  --  --------
   2         6
   3         7

Menampilkan jalur ke simpul daun

> CREATE TABLE tree(id INTEGER, parent_id INTEGER);
> INSERT INTO tree
    VALUES (1, NULL), ( 2,1), ( 3,1), ( 4,2), ( 5,2), ( 6,2), ( 7, 3), ( 8, 3),
           (9,    4), (10,4), (11,7), (12,7), (13,7), (14,9), (15,11), (16,11);

> WITH RECURSIVE t(id, path) AS (
    VALUES(1,cast(array() as array<Int>))
    UNION ALL
    SELECT tree.id, t.path || array(tree.id)
      FROM tree JOIN t ON (tree.parent_id = t.id)
  )
  SELECT id, path FROM t
    ORDER BY id;
  id  path
  --  -----------
   1  []
   2  [2]
   3  [3]
   4  [2,4]
   5  [2,5]
   6  [2,6]
   7  [3,7]
   8  [3,8]
   9  [2,4,9]
  10  [2,4,10]
  11  [3,7,11]
  12  [3,7,12]
  13  [3,7,13]
  14  [2,4,9,14]
  15  [3,7,11,15]
  16  [3,7,11,16]

Contoh tingkat lanjut

Pemecah Sudoku

-- Sudoku solver (https://sqlite.org)
-- Highlighted text represent the row-wise scan of a 9x9 Sudoku grid where missing values are represented with spaces.
> WITH RECURSIVE generate_series(gs) AS (
    SELECT 1
    UNION ALL
    SELECT gs+1 FROM generate_series WHERE gs < 9
  ),
  x( s, ind ) AS
    ( SELECT sud, position( ' ' IN sud )
      FROM  (SELECT '4  8725  5  64 213 29   8      6 73 1 8 2 4  97  15 2 3  2  1    659   28 2 37946'::STRING
        AS sud) xx
    UNION ALL
    SELECT substr( s, 1, ind - 1 ) || z || substr( s, ind + 1 ),
           position(' ' in repeat('x',ind) || substr( s, ind + 1 ) )
    FROM x,
     (SELECT gs::STRING AS z FROM generate_series) z
    WHERE ind > 0
      AND NOT EXISTS ( SELECT *
        FROM generate_series
        WHERE z.z = substr( s, ( (ind - 1 ) DIV 9 ) * 9 + gs, 1 )
           OR    z.z = substr( s, mod( ind - 1, 9 ) - 8 + gs * 9, 1 )
           OR    z.z = substr( s, mod( ( ( ind - 1 ) DIV 3 ), 3 ) * 3
                       + ( ( ind - 1 ) DIV 27 ) * 27 + gs
                       + ( ( gs - 1 ) DIV 3 ) * 6, 1 )
     )
  )
  SELECT s FROM x WHERE ind = 0;
  431872569587649213629351874245968731168723495973415628394286157716594382852137946

Anda dapat membuat masalah kurang spesifik, misalnya dengan mengganti '4' awal dengan spasi ' ' yang akan menghasilkan dua solusi.

431872569587649213629351874245968731168723495973415628394286157716594382852137946
631872594587649213429351867245968731168723459973415628394286175716594382852137946

Menemukan bilangan prima (Saringan Eratosthenes)

-- This is an implementation of the Sieve of Erastothenes algorithm for finding the prime numbers up to 150.
> WITH RECURSIVE t1(n) AS (
    VALUES(2)
    UNION ALL
    SELECT n+1 FROM t1 WHERE n < 100
  ),
  t2 (n, i) AS (
    SELECT 2*n, 2
      FROM t1 WHERE 2*n <= 150
    UNION ALL
    (
      WITH t3(k) AS (
        SELECT max(i) OVER () + 1 FROM t2
      ),
      t4(k) AS (
        SELECT DISTINCT k FROM t3
      )
      SELECT k*n, k
        FROM t1 CROSS JOIN t4
        WHERE k*k <= 150
    )
  )
  SELECT n FROM t1 EXCEPT
    SELECT n FROM t2
  ORDER BY 1;
  2
  3
  5
  7
  11
  13
  17
  19
  ...

Menggunakan TINGKAT REKURSI MAKS untuk membatasi kedalaman rekursi

> WITH RECURSIVE t(n) MAX RECURSION LEVEL 10 AS (
    VALUES(1)
    UNION ALL
    SELECT n+1 FROM t WHERE n < 100
  )
  SELECT * FROM t;
Error: RECURSION_LEVEL_LIMIT_EXCEEDED

Gunakan LIMIT untuk membatasi tataan hasil dan kedalaman rekursi

> WITH RECURSIVE t(n) AS (
    VALUES(1)
    UNION ALL
    SELECT n+1 FROM t
  )
  SELECT * FROM t LIMIT 10;
   n
  ---
   1
   2
   3
   4
   5
   6
   7
   8
   9
  10

LIMIT dengan ORDER BY mencegah penghentian dini

-- LIMIT does not help if an ORDER BY clause is used, which prevents early termination of the recursion
> WITH RECURSIVE t(n) AS (
    VALUES(1)
    UNION ALL
    SELECT n+1 FROM t
  )
SELECT * FROM t ORDER BY n LIMIT 10;
Error: RECURSION_LEVEL_LIMIT_EXCEEDED

Nama CTE duplikat

-- Two CTEs with the same name at the same scope.
> WITH t AS (SELECT 1), t AS (SELECT 2) SELECT * FROM t;
  Error: DUPLICATED_CTE_NAMES

Artikel terkait

• Kueri