再帰 CTE で総積を求める (再帰 CTE の入門)

なんか「SQL かけ算」とかを検索してここに来ている人達がいるようなんだけど、総積でも求めたいのかな？
確かに、SUM はあるのに PRODUCT なんてないしなー。
てことで勝手にそういうことにして、再帰 CTE で総積を書いてみる。
RDBMS はもちろん (?) SQL Server 前提です。

あ、CTE ってのは Common Table Expression の略で、共通表式のことらしいですよ、っと。

WITH
  Input(n) AS (
    SELECT 1
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
  )
, Input_(i, n) AS (
    -- 連番を振る。面倒なのでROW_NUMBER使った。
    SELECT ROW_NUMBER() OVER(ORDER BY n), n FROM Input
  )
, Product(n, i) AS (
    SELECT
        n
      , i
    FROM
        Input_
    WHERE
        -- 1からスタート
        i = 1
    UNION ALL
    SELECT
        -- P.nは今までの総積が、I.nには次にかけるべき値が入っている
        P.n * I.n
        -- iを進める。I.iでもいいけど、+ 1があった方が分かりやすい気がする
      , P.i + 1
    FROM
        -- 今かけるべき値をJOINで見つける
        Product P
          INNER JOIN Input_ I ON P.i + 1 = I.i
    -- WHEREなくても対象が空だと止まるっぽい
  )
, Result(n) AS (
    SELECT
        n
    FROM
        Product
    WHERE
        i = (SELECT MAX(i) FROM Input_)
  )
SELECT n FROM Result

これで、Input の n の総積が求まる。
慣れればどうと言うことはない SQL だけど、イメージが掴みにくいかもしれないので、ちょっと Product の処理を追ってみる。

まず最初に、UNION ALL より上の SELECT が実行され、

n	i
1	1

こんな行が返される。
次に再帰部分、つまり UNION ALL よりも下の SELECT が何度も実行されていくことになる。

再帰の 1 回目では、P.i は上で見ると 1 なので、FROM 句で JOIN された結果は、

P.n	P.i	I.i	I.n
1	1	2	2

こうなる。
これを元に、P.n * I.n と P.i + 1 が計算され、結果、

n	i
2	2

が返される。

次の再帰で、P.i は上で見ると 2 なので、FROM 句で JOIN された結果は、

P.n	P.i	I.i	I.n
2	2	3	3

となる。
これを元に P.n * I.n と P.i + 1 が計算され、結果、

n	i
6	3

が返される。

次の再帰で、P.i は上で見ると 3 なので、FROM 句で JOIN された結果は、

P.n	P.i	I.i	I.n
6	3	4	4

となる。
これを元に計算した結果、

n	i
24	4

が返される。

次の再帰で、

P.n	P.i	I.i	I.n
24	4	5	5

を元に計算した結果、

n	i
120	5

が返される。

次の再帰で、P.i が 5 なのだが、P.i + 1 = 6 と一致するような I.i は存在しないため、再帰が終了する。

最後に、全ての結果が UNION ALL され(処理中に UNION ALL されていっているのかもしれない)、

n	i
1	1
2	2
6	3
24	4
120	5

が Product の結果となる。
欲しいのは最後の行だけなので、Input_ から i の最大値に一致する行のみ抽出したものを Result の結果とし、SELECT * FROM Result とすることで、

n
120

を得ることが出来る。

・・・こんな感じで順を追ってみてみると、再帰 CTE もそんなに難しいものじゃないでしょ？