新しいことにはウェルカム

技術 | 電子工作 | ガジェット | ゲーム のメモ書き

SQL Server でカレンダーテーブルを作る方法

SQL Server(Azure SQL Database)で、カレンダーテーブルが欲しかったので作成方法メモです。

ググったところ、「WITH」でテーブルを作成し、そのテーブル定義の中の「UNION ALL」から再帰呼び出しして作るようです。

WITH

_calendar AS
(
SELECT
    CAST(N'2019-01-01' AS DATE) AS dt
UNION ALL
SELECT
    DATEADD(DAY, 1, _tbl.dt) AS dt  /* 日付間隔はここで調整 */
FROM _calendar AS _tbl
WHERE
    _tbl.dt < N'2019-12-31'
)

SELECT
*
FROM _calendar
OPTION ( MAXRECURSION 0 )  /* 再帰無制限 */

最初のSELECTでスタートして、UNION ALLで連結していくようなイメージです。

UNION ALLの下のFROMには、前のSELECTの結果の1レコードが入り、WHEREの条件チェックが入ります。

条件を満たしていれば、SELECTの計算をして1レコードが作られ、そのレコードが、次のUNION ALLFROMへと再帰的に続いていきます。

そして、WHEREの条件に一致しなくなると終了します。

再帰回数はデフォルトで100回までのため、オプションで0にして無制限にしています。

これなら連番も作れますね。

/* 10〜1000の10飛び番号 */
WITH

_seq AS
(
SELECT
  10 AS num
UNION ALL
SELECT
  _tbl.num + 10 AS num
FROM _seq AS _tbl
WHERE _tbl.num<=1000-10
)

SELECT
*
FROM _seq
OPTION ( MAXRECURSION 0 )

実験

でも、再帰が実際にどう呼び出されているのか、なんかモヤモヤしたので、スタートに['2019-01-01', '2019-02-01', '2019-03-01']の3レコードを入れて実験

WITH

tbl AS
(
    (
        SELECT
            _a.v
        FROM (VALUES
            (CAST(N'2019-01-01' AS DATE)),
            (CAST(N'2019-02-01' AS DATE)),
            (CAST(N'2019-03-01' AS DATE))
            ) AS _a(v)
    )
    UNION ALL
    (
        SELECT
            DATEADD(DAY, 1, _b.v) AS v
        FROM tbl AS _b
        WHERE DAY(_b.v) <= 2
    )
)

SELECT * FROM tbl

結果は…

v
2019-01-01
2019-02-01
2019-03-01
2019-03-02
2019-03-03
2019-02-02
2019-02-03
2019-01-02
2019-01-03

スタートの出力の後、スタートの値が、レコード単位に分解されUNION ALL以下のループ処理が行われていました。テーブルがゴソッとUNION ALL以下に渡されるんじゃないんですね。

感想など

毎回忘れて「んっ?」となるのですが、一番最後のレコードは、「WHEREまで」ではなく、「WHEREまでによるSELECTの演算結果まで」になります。

参考記事

https://sqlite-date.com/calendar

https://sql55.com/query/date-range-to-list-of-dates.php

https://knowledge.reontosanta.com/archives/1013