【SQL】SQLで再帰処理を実現する

with句とunion allを使用することで、SQLで再帰処理を実現することが可能になります。
再帰を用いることで、大量のダミーデータを簡単に作成したり、階層構造になっているデータの取得が簡単に実現できるようになります。
ただしこのこの構文はDBMSによっては実装されていなかったり、若干書き方が変わる可能性があるので、注意が必要です。

再帰を用いたダミーデータの作成

以下は、PostgreSQLとOracle DBで、再帰を使用してダミーデータを表示できるSQL文を示します。

-- PostgreSQLの場合
with recursive DUMMY(i) as
(select 1 i
union all
select i+1 from DUMMY where i < 10000)
select i as id, 'テスト' as name from DUMMY;

-- Oracle DBの場合
with DUMMY(i) as
(select 1 i from dual
union all
select i+1 from DUMMY where i < 10000)
select i as id, 'テスト' as name from DUMMY;

結果はどちらも以下のようになります。

id	name
1	テスト
2	テスト
...	テスト
10000	テスト

union allの下のselect文のwhere句の数値を変えることで、表示されるレコード件数を変更することができます。
大量のテストデータを作成する場合などに使用できるテクニックです。

簡単に中身を解説すると、
union all の上に書いているselect文は、それ単体で実行できるselect文を指定します。
union all の下に書いているselect文では、with句で指定した名前をテーブル名として、union allの上で書いたselectのカラムそのテーブルのカラムとして使用することができます。

階層構造のデータを取得する

データベースで階層構造のデータを扱いたい場合があります。
階層構造とは、PCのフォルダ（ディレクトリ）のような、入れ子構造になっているデータのことです。

業務系のシステムだと「部門」「カテゴリ」「製品」などは階層構造で表現されることも多いです。
部門の場合、例えば「東京事業所」という部門があり、その中に「開発部」と「営業部」があり、それぞれに「１課」「２課」と分かれていたりします。

• 東京事業所
  • 開発部
    • １課
    • ２課
  • 営業部
    • １課
    • ２課

カテゴリの場合、例えば「料理」というカテゴリがあり、その中に「和食」「中華」「イタリアン」などがあり、その中にさらに細かいカテゴリがあります。
以下は、興味のある分野を洗濯する際に使用することをイメージしたカテゴリの例です。

• 料理
  • 和食
    • 寿司
    • 天ぷら
  • イタリアン
    • パスタ
    • ピザ
• スポーツ
  • 球技
    • 野球
    • サッカー
  • 陸上
• 音楽
  • ヒップホップ
  • J-POP

今回はこの例をテーブルで扱う方法を考えます。
まずはサンプルデータを作成します。

create table category as 
select 1 id, '料理' name, null parent_id
union all
select 2, 'スポーツ', null
union all
select 3, '音楽', null
union all 
select 4, '和食', 1
union all
select 5, 'イタリアン', 1
union all
select 6, '球技', 2
union all
select 7, '陸上', 2
union all
select 8, '寿司', 4
union all
select 9, '天ぷら', 4
union all
select 10, 'パスタ', 5
union all
select 11, '野球', 6
union all
select 12, 'サッカー',  6
union all
select 13, 'ヒップホップ', 3;
union all
select 14, 'J-POP', 3;

テーブルで見ると以下のようになる。

id	name	parent_id
1	料理	null
2	スポーツ	null
3	音楽	null
4	和食	1
5	イタリアン	1
6	球技	2
7	陸上	2
8	寿司	4
9	天ぷら	4
10	パスタ	5
11	野球	6
12	サッカー	6
13	ヒップホップ	3
14	J-POP	3

idはテーブルのプライマリーキーで適当な連番を振っています。
nameはカテゴリの名称。parent_idは、そのカテゴリが属している親カテゴリのidが入っています。
最上位のカテゴリの場合はparent_idはnullを指定します。

この時、料理のカテゴリに含まれるサブカテゴリを全て（階層をたどって最下層のカテゴリのデータも含めて）取得する場合を考えます。
PostgreSQLの場合、以下のようなSQLで実現することができます。

with recursive ca(id, name, i) as (
  select id, name, 1 i from category p where id = 1
  union all
  select category.id, category.name, i + 1
  from category
  join ca
  on parent_id = ca.id
)
select id , name, i  from ca

結果は以下のようになります。

id	name	i
1	料理	1
4	和食	2
5	イタリアン	2
8	寿司	3
9	天ぷら	3
10	パスタ	3

料理のレコードと、そのサブカテゴリである和食とイタリアン、さらにそのサブカテゴリである寿司・天ぷら・パスタが表示されています。
階層がどこまで深くなっても、料理のカテゴリに紐づいているレコードは全て表示されます。
ここでiは、階層の深さを示しています。
union allの上のselectで指定したレコードの階層を１として、そこから階層が１つ深くなるごとに１ずつ加算されていく仕組みです。

※Oracle DBの場合は、connect by 句を使用することで、with句を使用するよもシンプルに階層構造のデータを取得することも可能です。