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ツール上などで日付の一覧を用意するのに便利ですね。