Ortak tablo ifadesi (CTE)

Şunlar için geçerlidir: onay işareti evet olarak işaretlenmiş Databricks SQL Databricks Runtime

SQL deyimi kapsamında muhtemelen birden çok kez başvurabileceğiniz geçici bir sonuç kümesi tanımlar. CTE, çoğunlukla bir SELECT deyimde kullanılır.

Sözdizimi

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

Parametreler

ÖZYİNELEMELİ

Şunun için geçerlidir: Databricks SQL Databricks Runtime 17.0 ve sonrası

Belirtildiğinde, ortak tablo ifadeleri bir recursive_queryiçerebilir.
view_identifier

common_table_expression'ye referans verilebilecek bir tanımlayıcı
column_identifier

common_table_expression sütununa başvurmak için kullanılabilecek isteğe bağlı bir tanımlayıcı.

column_identifierbelirtilirse, sayılarının querytarafından döndürülen sütun sayısıyla eşleşmesi gerekir. Eğer isim belirtilmemişse, sütun adları query'dan kaynak alınarak türetilir.
MAX ÖZYINELEME DÜZEYİ maxLevel

Şunun için geçerlidir: Databricks SQL Databricks Runtime 17.0 ve sonrası

Özyinelemeli bir CTE tarafından gerçekleştirilebilecek en fazla özyinelemeli adım sayısını belirtir. maxLevel TAMSAYı sabiti > 0 olmalıdır. Varsayılan değer 100 şeklindedir.

maxLimit aşılırsa, RECURSION_LEVEL_LIMIT_EXCEEDED tetiklenir. CTE özyinelemeli değilse bu yan tümce göz ardı edilir.
sorgu

Sonuç kümesi oluşturan sorgu.
recursive_query

Şunun için geçerlidir: Databricks SQL Databricks Runtime 17.0 ve sonrası

RECURSIVE belirtildiğinde, bir CTE sorgusu kendisine başvurabilir. Bu, CTE'yiözyinelemeli bir CTE yapar.
- base_case_subquery
  
  Bu alt sorgu özyineleme için tohumu veya tutturucuyu sağlar. Buna view_identifieratıfta bulunmamalıdır.
- step_subquery
  
  Bu alt sorgu, önceki adıma göre yeni bir satır kümesi oluşturur. Özyinelemeli olması için view_identifier öğesine başvurması gerekir.

Sınırlamalar

Özyinelemeli CTE'ler, UPDATE, DELETE ve MERGE deyimleri için desteklenmez.
step_subquery ile ilgili sütun başvuruları view_identifier içermemelidir.
Özyinelemeli bölümden rastgele sayı oluşturucuları çağırmak, her yinelemede aynı rastgele sayıyı oluşturabilir.
Sonuç kümesinin toplam boyutu 1.000.000 satırla sınırlıdır. Özyinelemeli CTE'yi SELECT yönlendiren deyim özyineleni etkili bir şekilde denetleyen bir LIMIT yan tümce içeriyorsa bu sınır geçersiz kılınabilir.

Şunun için geçerlidir: Databricks Runtime 17.2 ve üzeri

LIMIT ALL sınırı tamamen askıya alır.

Örnekler

Temel CTE örnekleri

CTE takma adları bulunan birden çok sütun

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

CTE tanımında CTE

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

Alt sorgulamada CTE

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

Alt sorgu ifadesinde CTE

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

CREATE VIEW ifadesinde CTE

> 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

CTE adlarının kapsamı belirlenmiş

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

Özyinelemeli sorgu örnekleri

Yönlendirilmiş grafik örnekleri

Aşağıdaki örnekte, doğrudan rotalar ve diğer şehirler üzerinden yönlendirilenler de dahil olmak üzere New York'tan diğer şehirlere giden tüm hedeflerin nasıl buluneceği gösterilmektedir:

> 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

Aşağıdaki örnek, yönlendirilmiş bir grafta nasıl geçiş yapılacağını ve çapraz geçişte sonsuz döngü olup olmadığını denetlemeyi gösterir:

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

Sonuç, döngüleri önlerken ve sonsuz olmayan bir yolla sonuçlanırken uzunluğu artan bir yol kümesidir

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

"3" çıkış noktasında belirgin bir duraklama ile tüm düğümleri ziyaret eder. Derinlik öncelikli arama, son satırı path sütununa göre sıralayarak değiştirerek elde edilir.

SELECT * FROM search_graph ORDER BY path;

Temel özyinelemeli teknikler

Basit döngü benzeri örnek

> 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

Başka bir döngü benzeri örnek

> 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

Başka bir CTE'den özyinelemeli bir CTE çağırma

> 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

Özyinelemeli bir CTE'den özyinelemeli olmayan bir CTE'yi çağırma

> 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

Geçici görünümler

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

> 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

İç içe özyinelemeli CTE

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

Onurlandırmak 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

Kuruluş ağacı örnekleri

'A' altındaki tüm departmanları ayıklama

> 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

Düzey numarasını ayıklama

> 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

Düzeye göre filtreleme (düzey 2 veya daha fazla)

> 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

İkili ağaç örnekleri

İkinci düzey düğümlerden tüm yolları bulma

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

Düğümlerden erişilebilen yaprak düğümleri sayma

> 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

Yaprak düğümlere giden yolların listelenmesi

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

İleri düzey örnekler

Sudoku çözücü

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

Örneğin, baştaki '4' karakterini boşluk ' ' ile değiştirerek sorunu alt düzeyde belirtebilirsiniz, bu da iki çözüm sağlayacaktır.

431872569587649213629351874245968731168723495973415628394286157716594382852137946
631872594587649213429351867245968731168723459973415628394286175716594382852137946

Asal sayıları bulmak (Eratosthenes'in eleği)

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

Özyineleme derinliğini sınırlamak için MAX RECURSION LEVEL komutunu kullanma

> 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

Sonuç kümesini ve özyineleme derinliğini sınırlamak için kullanın 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

LIMIT ile ORDER BY erken sonlandırmayı önler

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

Sorgu

Geri Bildirim

Bu sayfayı yararlı buldunuz mu?

Last updated on 2025-10-08