一人アドカレ
| 日 | ポスト |
|---|---|
| 1 | 一時テーブルが存在していたら削除する |
| 2 | 何度も使う中間テーブルに名前をつけてクエリを見やすくする |
| 3 | 共通テーブル式と一時テーブルの参照可能な範囲の違い |
| 4 | グループ毎に最大値や最小値を持つレコードを抽出する |
| 5 | |
| 6 | SQL Serverのデータインポートウィザードを使って日時データをインポートする |
| 7 | 再帰共通テーブル式で連続した値を得る |
| 8 | |
| 9 | |
| 10 | |
| 11 | |
| 12 | |
| 13 | |
| 14 | |
| 15 | |
| 16 | |
| 17 | |
| 18 | |
| 19 | |
| 20 | |
| 21 | |
| 22 | |
| 23 | |
| 24 | |
| 25 |
再帰共通テーブル式で連続した値を得る
課題
WITH句を使用した共通テーブル式を再帰的に使用し、1から100や2019/12/01から2019/12/31までなどの連続した値を出力する。
準備
実行環境
- SQL Server 2017 Standard
- SQL Server Management Studio 2017
解法
共通テーブル式については『何度も使う中間テーブルに名前をつけてクエリを見やすくする』で扱った。
このWITH句を使用した共通テーブル式には自己参照を含めることができ、これを再帰共通テーブル式という。自己参照を含める、というのはどういうことか説明するためにまず、仮のコードを次に示す。
再帰共通テーブル式の構造はざっくり説明すると
WITH [共通テーブルの名前] ( [共通テーブルの持つ列], ... )
AS ( [アンカーメンバー]
UNION
[再帰メンバー] )
SELECT文とか...
となっている。
[共通テーブルの名前] と [共通テーブルの持つ列] というのは、最終的に共通テーブル式を使って出来上がったテーブルは なんていう名前 で、 どんな名前の列を持っている か、を示してあげるものだ。
[アンカーメンバー] と [再帰メンバー] はどちらもSELECT文を使う。それぞれがもつ役割は次のとおり。
アンカーメンバー：再帰の出発点を与える。
再帰メンバー ：アンカーメンバーの出力を入力に受け取って、条件を満たすなら出力する。
出力した場合、それを新しいアンカーメンバーの出力として
再帰メンバーの処理を繰り返す。
条件を満たさなくなったら出力をやめる。
つまるところ、 アンカーメンバー が再帰処理の開始点で、 再帰メンバー が再帰処理の中身、といった感じだと思っている。
そして、上記の アンカーメンバー の出力を 再帰メンバー へ渡す方法として、 [共通テーブルの名前] を 再帰メンバー で利用できる。これが 『自己参照を含めることができる』 ということ。
次に実際に動くクエリを示す。
このクエリは1から99までの連番を出力するクエリである。
WITH [some_tbl] ( [number] )
AS ( SELECT 1 AS [number] -- このSELECT文がアンカーメンバー
UNION ALL -- アンカーメンバーと再帰メンバーはUNION句でつなぐ
SELECT [number] + 1 -- このSELECT文が再帰メンバー
FROM [some_tbl] -- ここでsome_tblというWITH句で定義したテーブルを参照している＝自己を参照している＝自己参照
WHERE [number] < 99 ) -- 再帰の終了条件を再帰メンバーの条件に含める
SELECT [number]
FROM [some_Tbl]
ORDER BY [number];
共通テーブル式を構成するどれがどれか、というのはコメントを参照するとして、再帰メンバーのFROM句で共通テーブルの名前である[some_tbl]を参照している。これが自己参照になる。
処理の流れとしては…
- アンカーメンバーの
SELECT 1が実行されて、出力の1が再帰メンバーに渡される - 再帰メンバーがアンカーメンバーの出力の
1を（[some_tbl]を介して）受け取り、クエリを実行する - 再帰メンバーのクエリ実行結果として
2が出力される - 出力された
2を再び再帰メンバーへ渡す - 再帰メンバーが出力の
2を（[some_tbl]を介して）受け取り、クエリを実行する - ...ということを、再帰メンバーが出力しなくなるまで繰り返す
- 最後に、すべての出力をまとめて[some_tbl]として最終結果とする。
出力としては次のようになる。
| number |
|---|
| 1 |
| 2 |
| 3 |
| ... |
| 97 |
| 98 |
| 99 |
なお、再帰メンバーの再帰回数が一定値を超えるとエラーとなってしまう。
その場合はクエリヒントの MAXRECURSION を使用することで制御が可能である。
感想
一時的に連番テーブルが使いたいときにDBを汚さずに使えるので良いな、と思っています。