🌅 🐾 🤛 PostgreSQLの再帰クエリ（WITH RECURSIVE） 👋🏽 👨🏼‍🚒 🙎🏽

奇妙なことに、再帰を理解するために、PostgreSQLは再帰を理解する必要はありません。進行中（およびその他の深刻なデータベース）に存在するWITH RECURSIVEは、ある条件が満たされる前に反復によって何かを計算する可能性が高いためです。

それでも、これは、たとえばテーブルがフォーム（id、parent_id、...）で指定されている場合、特定のカテゴリのすべてのサブカテゴリを表示するために使用できる非常に便利なデータベース機能です。

簡単にするために階乗を計算してみましょう。 javascriptでは、次のようにします。

function factorial(i) { if (i > 1) { return i * factorial(i-1); } else { return 1; } } console.log(factorial(10));

進行中、これはまったく異なる方法で計算されます。 CTEの再帰部分には、開始部分と再帰部分があり、UNIONという単語で区切られている必要があります。

 WITH RECURSIVE r AS ( --    (.. "anchor") SELECT 1 AS i, 1 AS factorial UNION --   SELECT i+1 AS i, factorial * (i+1) as factorial FROM r WHERE i < 10 ) SELECT * FROM r; i | factorial ----+----------- 1 | 1 2 | 2 3 | 6 4 | 24 5 | 120 6 | 720 7 | 5040 8 | 40320 9 | 362880 10 | 3628800 (10 rows)

このクエリを読んだ場合、いわば直感的には、そのままでは、進行状況は永遠のサイクルに入り、何を理解していないように見えることに注意してください。

実際、このFROM rはクエリ全体を再度実行するのではなく、次のように動作します：再帰（アンカー）の開始部分にあるものを初めて取得し、次の反復では前の反復の結果を取得します。

アルゴリズムは次のようなものです。

開始データを取得します
リクエストの「再帰的」部分を置換します。
何が起こったのか見てください：
- 再帰部分の排気が空ではない場合、結果の選択にそれを追加し、この排気を再帰部分の次の呼び出しのデータとして使用します。後藤2
- 空の場合、処理を終了します

一般に、階乗の例はあまり示唆的ではありません。なぜなら、postgresqlはすでに階乗を計算する方法を知っているからです。

 SELECT 10000! --    ,

約束の木材を選択してください。

 CREATE TABLE geo ( id int not null primary key, parent_id int references geo(id), name varchar(1000) ); INSERT INTO geo (id, parent_id, name) VALUES (1, null, ' '), (2, 1, ' '), (3, 1, '  '), (4, 2, ''), (5, 4, ''), (6, 4, ''), (7, 5, ''), (8, 5, '-'), (9, 6, '');

ヨーロッパに当てはまるものをすべて選択してください。

 WITH RECURSIVE r AS ( SELECT id, parent_id, name FROM geo WHERE parent_id = 4 UNION SELECT id, parent_id FROM geo WHERE parent_id IN ( SELECT id FROM r ) ) SELECT * FROM r; ERROR: recursive reference to query "r" must not appear within a subquery

別の制約である再帰は、サブクエリでは使用しないでください。

わかりました、書き直して参加します：

 WITH RECURSIVE r AS ( SELECT id, parent_id, name FROM geo WHERE parent_id = 4 UNION SELECT geo.id, geo.parent_id, geo.name FROM geo JOIN r ON geo.parent_id = r.id ) SELECT * FROM r; id | parent_id | name ----+-----------+----------------- 5 | 4 |  6 | 4 |  7 | 5 |  8 | 5 | - 9 | 6 |  (5 rows)

別の例。たとえば、ヨーロッパに関連するすべてのものをヨーロッパ自体と一緒に配布したり、ネストのレベルを計算したりすることもできます

 WITH RECURSIVE r AS ( SELECT id, parent_id, name, 1 AS level FROM geo WHERE id = 4 UNION ALL SELECT geo.id, geo.parent_id, geo.name, r.level + 1 AS level FROM geo JOIN r ON geo.parent_id = r.id ) SELECT * FROM r; id | parent_id | name | level ----+-----------+-----------------+------- 4 | 2 |  | 1 5 | 4 |  | 2 6 | 4 |  | 2 7 | 5 |  | 3 8 | 5 | - | 3 9 | 6 |  | 3 (6 rows)

このトピックで興味深いことがわかっている場合は、コメントに書いてください。 SQLで実際に再帰をどこで使用しましたか？落とし穴は何ですか？

PostgreSQLの再帰クエリ（WITH RECURSIVE）

More articles: