読者です 読者をやめる 読者になる 読者になる

再帰 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 もそんなに難しいものじゃないでしょ?