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

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

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

関連カテゴリー記事

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com