再帰SQL

https://qiita.com/Shoyu_N/items/f1786f99545fa5053b75
再帰SQLは、階層的なデータを「再帰的に」取得するときなどに使うSQL構文。
WITH RECURSIVE キーワードを使って記述する。
例えばツリー状データがテーブルに入っているとき、特定のノードの子孫のみを取得する場合などに使う。
一番単純な例は以下。

WITH RECURSIVE number_sequence AS (
  SELECT 1 AS num
  
  UNION ALL
  
  SELECT number_sequence.num + 1 AS num FROM number_sequence WHERE num < 5
)
SELECT * FROM number_sequence;

-- 【結果】
-- num
-- -----
-- 1
-- 2
-- 3
-- 4
-- 5

再帰SQLを作るときの考え方は、まず「WITH RECURSIVEで定義した仮想テーブル」に作りたい再帰データが無限に入っていることを想定して SELECT や WEHRE を抜きにしたSQLを書く。
その後、その目的とするデータを入れるためにはSELECTやWHEREがどうなっているべきかを考える。
上の例について考えてみる。
1. 「WITH RECURSIVEで定義した仮想テーブル」に作りたい再帰データが無限に入っていることを想定したSQLを書く

SELECT 1 AS num

UNION ALL

SELECT 《不明》 as num FROM number_sequence《この中には1,2,3,4,5,6...が入っている》 WHERE 《不明》

2. どうやったら作りたい再帰データが入るSQLになるかを考えて、SELECTを書く

SELECT 1 AS num

UNION ALL

SELECT (number_sequence.num + 1) AS num FROM number_sequence《この中には1,2,3,4,5,6...が入っている》 WHERE 《不明》

3. どうやったら最終的に得たい結果である「1～5」になるかを考えて、WHEREやテーブル内部結合を書く

SELECT 1 AS num

UNION ALL

SELECT (number_sequence.num + 1) AS num FROM number_sequence《この中には1,2,3,4,5,6...が入っている》 WHERE number_sequence.num < 5

以上で完成。

---

ツリー状データに対して、あるノード子孫を全て取得したい場合の例は以下のようになる。

-- 【データのイメージ】
-- a -+- a1 -+- a1-1
--    |      |
--    |      +- a1-2
--    +- a2 
-- 
-- b -+- b1
--    |
--    +- b2 

-- 【データ作成】
CREATE TABLE node_list (
  node VARCHAR(10),
  parent_node VARCHAR(10)
);
INSERT INTO node_list VALUES ('a'   , null);
INSERT INTO node_list VALUES ('a1'  , 'a');
INSERT INTO node_list VALUES ('a1-1', 'a1');
INSERT INTO node_list VALUES ('a1-2', 'a1');
INSERT INTO node_list VALUES ('a2'  , 'a');
INSERT INTO node_list VALUES ('b'   , null);
INSERT INTO node_list VALUES ('b1'  , 'b');
INSERT INTO node_list VALUES ('b2'  , 'b');

-- 【'a' の子孫を取得するSQL 】
WITH RECURSIVE node_childlen AS (
  SELECT node, parent_node, 1 AS deep_level FROM node_list WHERE node = 'a'
  
  UNION ALL
  
  SELECT n.node, n.parent_node, (p.deep_level+1) AS deep_level
    FROM node_list n
   INNER JOIN node_childlen p ON n.parent_node = p.node
)
SELECT * FROM node_childlen;

-- 【結果】
node    parent_node deep_level
------- ----------- -------------
a       null        1
a1      a           2
a2      a           2
a1-1    a1          3
a1-2    a1          3