SQLiteのWith句で再帰を使って日付のデータを生成する

2620 ワード
SQL sqlite SQL テキストリンク

SQLiteでイベントの発生を示すデータを集計したいとします。
単純に日付でgroup byするとイベントが発生した日付のみでgroup化されたデータが集計され、日付が飛び飛びになってしまいますね？

こういうときに日付の一覧のデータとjoinすれば問題ないのですが、そのためにtabelやviewを作るのもな〜というときに今回のテクニックが使えます。

やりかた

例として2021年02月の日付一覧を生成します。


WITH RECURSIVE calendar(calendar_date) AS (
    SELECT
        -- ここが始まりの日付
        DATE('2021-02-01') AS calendar_date
    UNION ALL
    -- UNION ALL 以下が再帰処理部分
    SELECT
        DATE(calendar_date, '+1 day') AS calendar_date
    FROM calendar
    WHERE
        -- ここが終わりの日付
        calendar_date < DATE('2021-02-28')
)

-- ここが実際のSELECT文
SELECT calendar_date FROM calendar;

calendar_dateは2021-02-01から始まり、再帰的にselectが実行されるたびにcalendar_dateに+1dayされて、それが2021-02-28まで続きます。その結果が全てUNION ALLで連結されてcalendarという一つのテーブルになります。

結果

これはRedash上の表示です。BIツール上などで日付の一覧を用意するのに便利ですね。

参考