PostgreSQL の WITH で再帰させる

これは弥生 Advent Calendar 2021 の７日目の記事です。

qiita.com

PosgreSQL の WITH

共通テーブル式（Common Table Expression）は一時的なテーブルを定義するもの。 サブクエリを使って SQL がネストして深くなってしまうような場合に、便利だと思う。

PostgreSQL では WITH 句で CTE を書ける。

www.postgresql.jp

こんな感じで、名前をつけた式を別の式から呼ぶことができる。便利。

WITH company_names AS (
    SELECT company_name AS name
    FROM companies
)

SELECT name
FROM company_names;

WITH で再帰できる？

WITH RECURSIVE と書けば、自分自身を参照できるようになり、再帰的に問い合わせることができる。 公式ドキュメントでも紹介されていた、1から100までの合計を計算する式は、以下のようになる。

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

-- 5050

これを ruby で書くとこんな感じだろうか。

def t(n)
  return nil if n > 100
  
  [n, *t(n + 1)]
end

pp t(1).sum

# 5050

なるほど再帰が書けるのか、と思ったけど、どうやら再帰ではないらしい。

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

ふむ...

たしかに 再帰的問い合わせの評価 を読むとこれは for 文に近い。

とはいえ、面白いので、他にも試してみる。

添字の列を確保しておくと、ちょっと難しい数列も表現できる。

等比数列

WITH RECURSIVE t(i, n, d) AS (
    SELECT 1, 1, 3
  UNION ALL
    SELECT i + 1, n * d, d FROM t WHERE i < 10
)

SELECT i, n FROM t;

i    n
1   1
2   3
3   9
4   27
5   81
6   243
7   729
8   2187
9   6561
10  19683

フィボナッチ数列

WITH RECURSIVE fibonacci(i, x, y) AS (
    SELECT 0, 0, 1
  UNION ALL
    SELECT i + 1, y, x + y  FROM fibonacci WHERE i < 10
)

SELECT i, x FROM fibonacci;

i    x
0   0
1   1
2   1
3   2
4   3
5   5
6   8
7   13
8   21
9   34
10  55

感想

再帰じゃなくて反復なのは違和感があるし、トリッキーだと思う。なんとかならなかったのだろうか。 SQL でちょっと気の利いた計算ができるのは面白いけれど、この機能で救われる場面はあるのだろうか？と考えてしまう。

引き続き、弥生 Advent Calendar 2021 をよろしくお願いします。

qiita.com