PostgreSQLの再帰SQL

PostgreSQLの再帰SQL（1）
――再帰SQLの構文

20から30までの自然数を出力など

山岸賢治[著]

2010/02/08 14:00

ポスト

ダウンロード SourceCode (753.0 B)

3. 20から30までの自然数を出力

　次は、再帰のあるwith句の構文に慣れておきましょう。例として、20から30までの自然数を出力してみます。下記のようにgenerate_series関数を使えばいいのですが、あえて再帰SQLで求めてみます。

generate_seriesを使う方法

select Val
  from generate_series(20,30) as tmp(Val);

出力結果

Val
20
21
22
23
24
25
26
27
28
29
30

　再帰SQLを使う方法は次のとおりです。なお、再帰のあるwith句では、withの直後のrecursiveキーワードが必須となります。

再帰SQLを使う方法

with recursive rec(Val) as(
select 20
union all
select Val+1
  from rec
 where Val+1 <=30)
select Val from rec;

　select 20を実行して、select Val+1 from (前回のselect文の結果(20)) where Val+1 <=30を実行して、 select Val+1 from (前回のselect文の結果(21)) where Val+1 <=30を実行して、……（以下select文の結果が空集合になるまで続く）といった処理を行ってると理解しておけばいいでしょう。select Val+1 from rec where Val+1 <=30のfrom句のrecは、前回のselect文の結果となるのです。

4. フィボナッチ数列を求める

　次はフィボナッチ数列を求めてみます。フィボナッチ数列は、1項目が0、2項目が1、3項目以降は前の2項の和となる数列で、「0, 1, 1, 2, 3, 5, 8, 13, 21,……」といったものです。

フィボナッチ数列を求める

with recursive rec(N3,N2,N1) as(
select 1,1,0
union all
select N3+N2,N3,N2
  from rec)
select * from rec Limit 15;

出力結果

N3	N2	N1
1	1	0
2	1	1
3	2	1
5	3	2
8	5	3
13	8	5
21	13	8
34	21	13
55	34	21
89	55	34
144	89	55
233	144	89
377	233	144
610	377	233
987	610	377

　最初のselect 1,1,0で、最初のフィボナッチ数を求めてます。

　次の、select N3+N2,N3,N2 from recで、1つ前の項と2つ前の項の和をN3+N2として求めてます。そして、次のフィボナッチ数の計算に使用するので、前のselect文の結果のN3をN2に移して、前のselect文の結果のN2をN1に移してます。最後に、with句の外側のselect文でLimit 15を指定して、再帰SQLの結果の行数を15行に制限してます。