SQLのWITH句で任意の範囲の日付を取得する

トランザクションデータを、ある区間の日付ごとに集計したいということがまれに良くあります。
「2016年の一ヶ月ごとの売上平均を出したい」「週ごとのアクセス合計を出したい」などです。

SQLのWITH句を使ってやると上記のような集計を簡単にできます。

以下、PostgreSQLで利用する場合（MySQLでは使えませんが、MySQL8.0からWITH句がサポートされるという話です）

WITH句を使った期間一時テーブル

WITH句による再帰SQLを使うことで、任意の期間を任意の幅で簡単に得ることができます

WITH RECURSIVE date_range(i, start_date, end_date) AS (
  SELECT 1, date '2016-01-01', date '2016-01-31'
  UNION ALL 
  SELECT i + 1
    , date (start_date + interval '1 month')
    , date (start_date + interval '2 month' - interval '1 day')
  FROM date_range_
  WHERE start_date < '2017-01-01'
)
SELECT date_range.*
FROM date_range

出力

 i  | start_date |  end_date  
----+------------+------------
  1 | 2016-01-01 | 2016-01-31
  2 | 2016-02-01 | 2016-02-29
  3 | 2016-03-01 | 2016-03-31
  4 | 2016-04-01 | 2016-04-30
  5 | 2016-05-01 | 2016-05-31
  6 | 2016-06-01 | 2016-06-30
  7 | 2016-07-01 | 2016-07-31
  8 | 2016-08-01 | 2016-08-31
  9 | 2016-09-01 | 2016-09-30
 10 | 2016-10-01 | 2016-10-31
 11 | 2016-11-01 | 2016-11-30
 12 | 2016-12-01 | 2016-12-31
 13 | 2017-01-01 | 2017-01-31

これを使って、例えば「一ヶ月ごとのuser_access_logsを集計する」ならば

WITH RECURSIVE date_range(i, start_date, end_date) AS (
  SELECT 1, date '2016-01-01', date '2016-01-31'
  UNION ALL 
  SELECT i + 1
    , date (start_date + interval '1 month')
    , date (start_date + interval '2 month' - interval '1 day')
  FROM date_range
  WHERE start_date < '2017-01-01'
)
SELECT to_char(start_date, 'yyyy年mm月') date_range
  , count(user_access_logs.*) as access
FROM date_range
LEFT JOIN user_access_logs
  ON user_access_logs.access_date
     BETWEEN date_range.start_date AND date_range.end_date
WHERE date_range.start_date IS NOT NULL
GROUP BY date_range.start_date
ORDER BY date_range.start_date

出力

 date_range | access
------------+--------
 2016年01月 |    20
 2016年02月 |   110
 2016年03月 |   204
 2016年04月 |   123
 2016年05月 |   312
 2016年06月 |   400
 2016年07月 |    12
 2016年08月 |    32
 2016年09月 |    10
 2016年10月 |    11
 2016年11月 |     2
 2016年12月 |     0
 2017年01月 |     0

一週間ごとの集計にしたい場合は

WITH RECURSIVE date_range(i, start_date, end_date) AS (
  SELECT 1, date '2016-01-01', date '2016-01-31'
  UNION ALL 
  SELECT i + 1
-    , date (start_date + interval '1 month')
-    , date (start_date + interval '2 month' - interval '1 day')
+    , date (start_date + interval '1 week')
+    , date (start_date + interval '2 week' - interval '1 day')
  FROM date_range
  WHERE start_date < '2017-01-01'
)

などとしてよしなにすれば良い感じ