SQLのWITH RECURSIVEの使い方とユースケース

こんにちは！kossyです！

今回はSQLのWITH RECURSIVEの使い方とユースケースを考えてみたので、備忘録としてブログに残してみたいと思います。

環境

PostgreSQL 12系

使い方

公式のドキュメントを見てみましょう。

www.postgresql.jp

オプションのRECURSIVE修飾子は、WITHを、単に構文上の利便性の高めるだけでなく標準的なSQLでは不可能な機能を実現させます。 RECURSIVEを使用すれば、WITH問い合わせが行った自己の結果を参照できるようになります。1から100までの数を合計する非常に単純な問い合わせは以下のようなものです。

WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

再帰的WITH問い合わせの汎用形式は常に、非再帰的表現（non-recursiveterm）、そしてUNION（またはUNION ALL）、そして再帰的表現（recursive term）です。 再帰的表現だけが、その問い合わせ自身の出力への参照を含むことができます。

例文をSQLクライアントで実行してみます。

繰り返し処理された結果を得られています。

ドキュメントを読み進めると、

厳密には、この手順は反復(iteration)であって再帰(recursion)ではありませんが、RECURSIVEはSQL標準化委員会で選ばれた用語です。

という一文が出てきます。反復処理だということを頭に入れておくと、多少は理解が進むのではないかと思います。

ユースケース

例えば任意の範囲の日付を取得したい場合ですと、以下のように書くことができます。

with recursive calculate_range(i, date_start, date_end) AS (
  select date '2021-01-01', date '2022-01-31'
  union all
  select date (date_start + interval '1 month'), date (date_start + interval '2 month' - interval '1 day')
  from calculate_range
  where date_start < '2022-01-01'
)
select calculate_range.*
from calculate_range

このSQLを実行すると、以下のような結果が得られます。

反復して処理を行った結果が欲しい時に使えそうです。