Postgresqlで「with recursive」使った再帰クエリ
PostgreSQL8.4から利用可能な「with recursive」使った再帰クエリの利用方法。
会社の下に営業部とシステム開発部があり、それぞれの下に課があるテーブル「department」を用意する。
parent_idには、上位部署のidが格納されている。
| id | parent_id | name |
|---|---|---|
| 1 | 会社 | |
| 2 | 1 | 営業部 |
| 3 | 1 | システム開発部 |
| 4 | 2 | 営業１課 |
| 5 | 2 | 営業２課 |
| 6 | 3 | システム開発１課 |
このテーブルから、「営業部」とその配下の部署を検索するSQLは以下の通り。
1 2 3 4 5 6 7 8 9 10 | with recursive temp(id, parent_id, name) as ( select id, parent, name from dept where name = '営業部' union all select dept.id, dept.parent, dept.name from temp, dept where dept.parent = temp.id ) select * from temp; |
結果。
| id | parent_id | name |
|---|---|---|
| 2 | 1 | 営業部 |
| 4 | 2 | 営業１課 |
| 5 | 2 | 営業２課 |
SQLの解説。（with句とオプションのrecursive修飾子について）
まずは、with句。
with句は、「検索結果に名前を付けて、メモリ上に仮想のテーブルを作る」ための文。
構文は以下の通り。
1 2 3 4 | with 仮想テーブル名[ ( カラム名.....) ] as ( select文1 ) select文2 |
上記の記述では、まずselect文1が実行されて、その検索結果がメモリ上で仮想テーブルになる。
そして、select文2の中で、そのテーブルを検索対象として使用できる。
例えば、「会社の部署の中で、”営業”に関係している”課”」を検索したい場合。
１．部署名が「課」で終わってる行を検索
２．１．の結果から、さらに部署名が「営業」で始まっている行を検索
1 2 3 4 5 6 7 8 | with temp(id, name) as ( select id, name from dept where name like '%課' ) select id, name from temp where name like '営業%' ; |
結果。
| id | parent_id | name |
|---|---|---|
| 4 | 2 | 営業１課 |
| 5 | 2 | 営業２課 |
このwith句を前半と後半に分けて考える。
まず、SQLの前半部分。
1 2 3 4 5 | with temp(id, name) as ( select id, name from dept where name like '%課' ) |
これは、「かっこ内のselect文を実行し、その結果をtempテーブルとする」という意味になる。
このテーブルは実際にはDB上には作られない、SQLが終わると消えてしまう仮想テーブルである。
かっこ内のselect文は「課」で終わってる部門の検索なので、結果は下記のようになる。
| id | parent_id | name |
|---|---|---|
| 4 | 2 | 営業１課 |
| 5 | 2 | 営業２課 |
| 6 | 3 | システム開発１課 |
次に、with句の後半部分。
1 2 3 | select id, name from temp where name like '営業%'; |
このselect文では、with句の前半で作られた仮想テーブル「temp」の中で、「営業」で始まっている行を検索している。
結果は以下の通り。
| id | parent_id | name |
|---|---|---|
| 4 | 2 | 営業１課 |
| 5 | 2 | 営業２課 |
つまり、もう一度前半と後半を一緒に見てみると、
1 2 3 4 5 6 7 8 | with temp(id, name) as ( select id, name from dept where name like '%課' ) select id,name from temp where name like '営業%'; |
１．まず、部署名が「課」で終わってる行を検索
２．１．の結果から、さらに部署名が「営業」で始まっている行を検索
再帰検索をするには、with句にオプションのrecursive修飾子を付けて、with recursiveとする。
recursive修飾子を付加すると、with句で一時テーブルを作成するselect文で検索する対象テーブルに、その自分自身（つまりその一時テーブル自身）を指定することができる。
例えば、手続き型プログラミングでの再帰呼び出しは、関数が自分自身を呼ぶ。
それと同じように、一時テーブルを作成する時に、その作成途中の自分自身を検索対象にしてしまう。
「with recursive」のSQLを前半、中盤、後半に分けてみてみる。
１．前半
この部分は、再帰の先頭部分を決めるSQLになる。
1 2 3 4 | with recursive temp(id, parent_id, name) as( select id, parent_id, name from dept where name='営業部' |
1行目は、tempという一時テーブルを作成するSQLの最初の部分。
そのtempの構成内容が記述されているのが2行目以降。
このselect文がまず実行される。
1 2 3 | select id, parent_id, name from dept where name='営業部' |
これによって、nameが「営業部」の以下の結果が返る。
| id | parent_id | name |
|---|---|---|
| 2 | 1 | 営業部 |
現時点では、これが一時テーブル「temp」の中身になる。（1行だけのテーブル）
２．中盤
ここが実質上、再帰をしている部分。
1 2 3 4 | union all select dept.id, dept.parent_id, dept.name from temp, dept where dept.parent_id = temp.id |
union allはとりあえず置いておいて、その次のselect文の中身を見てみる。
1 2 3 | select dept.id, dept.parent_id, dept.name from temp, dept where dept.parent_id = temp.id |
元のテーブル「dept」全体の「parent_id」カラムの内容と、一時テーブル「temp」の「id」の内容が一致しているものを検索する。
つまり、一時テーブルで検索された部署（営業部）を「自分の親だ」と思っている部署が検索される。
今現在の一時テーブルの内容が、
| id | parent_id | name |
|---|---|---|
| 2 | 1 | 営業部 |
で、idは「2」であり、deptからは「parent_id」が「2」の、以下の行が検索される。
| id | parent_id | name |
|---|---|---|
| 4 | 2 | 営業１課 |
| 5 | 2 | 営業２課 |
というわけで、2行が検索された。
で、ここで忘れてはいけないのは、この段階ではwithで作成される一時テーブル「temp」が「まだ作られてる最中だ」ということ。
つまり、この検索結果が一時テーブル「temp」の内容に書き換わる。
さっきまで1行しかなかった「temp」が、今回の2行の検索結果に書き換わったということ。（ただし、最初の1行のtempは消えない。どこかに保存されている）
そして、さらにこの2行の内容を元にして、先ほどの以下のselect文がまた実行される。
1 2 3 | select dept.id, dept.parent_id, dept.name from temp, dept where dept.parent_id = temp.id |
つまり今度は、「親の部署のidが、4か5の部署はないか？」という検索が実行される。
この様に次々に親が書き換わって、その子供が検索されて、その結果またが親になって、その子供が検索されて、それがまた親になって、、、、、というのが繰り返される。
今回の例では、これ以上の子供はないので、ここで子供の検索は終わる。
そして最後に、さっき置いておいたunion all。
これが指定されているので、全ての検索結果（この場合は1行の検索結果と2行の検索結果）が結合されて、以下のようになる。
これで一時テーブル「temp」の作成が完了した。
| id | parent_id | name |
|---|---|---|
| 2 | 1 | 営業部 |
| 4 | 2 | 営業１課 |
| 5 | 2 | 営業２課 |
以上がwith recursiveを使った再帰検索の中身。
もう一度、with recursiveを使ったselect文全体を見てみる。
1 2 3 4 5 6 7 8 9 10 | with recursive temp(id, parent_id, name) as ( select id, parent_id, name from dept where name = '営業部' union all select dept.id, dept.parent_id, dept.name from temp, dept where dept.parent_id = temp.id ) select * from temp; |
最後に再帰検索は、where句の条件を間違えると無限ループに陥りやすい為、実行には最新の注意が必要となる。
