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