【SQL】月別で集計するときに値がない月は0と表示したい

SQLに関するメモ。前提としてPostgreSQLを使っています。

以下のようなTimestamp型で日時の情報を持つテーブルがあるとする。

id: integer	timestamp: timestamp with time zone
1	2022-01-01 00:00:00+00
2	2022-02-01 00:00:00+00
3	2022-10-01 00:00:00+00
4	2022-10-01 00:00:00+00
5	2022-12-01 00:00:00+00

で、月ごとの件数を集計をしたい場合、何も考えずにこんなSQLを書く。

SELECT to_char(timestamp, 'YYYYMM') AS MONTH,
       count(id)
FROM table1
GROUP BY MONTH
ORDER BY MONTH;

そうすると得られるのはこんな結果。

month	count
202201	1
202202	1
202210	2
202212	1

これはこれで正しい。正しいが0件の月も表示したい。つまりこんな結果がほしい。

month	count
202201	1
202202	1
202203	0
202204	0
202205	0
202206	0
202207	0
202208	0
202209	0
202210	2
202201	0
202212	1

そんなときはgenerate_series()を使う。generate_series()ってのは文字通り連続値を生成できる関数。実はこれ日付型でも使える。

こんな感じ。

SELECT *
FROM generate_series('2022-01-01'::timestamp, '2022-12-01', '1 month');

実行するとこういう結果が得られる。

| generate_series     | 
| ------------------- | 
| 2022-01-01 00:00:00 | 
| 2022-02-01 00:00:00 | 
| 2022-03-01 00:00:00 | 
| 2022-04-01 00:00:00 | 
| 2022-05-01 00:00:00 | 
| 2022-06-01 00:00:00 | 
| 2022-07-01 00:00:00 | 
| 2022-08-01 00:00:00 | 
| 2022-09-01 00:00:00 | 
| 2022-10-01 00:00:00 | 
| 2022-11-01 00:00:00 | 
| 2022-12-01 00:00:00 | 

というわけでこの結果と元のテーブルをジョインする。そうすると期待したものが得られる。次のようなSQLになる。

SELECT
       t1.month,
       count(id)
FROM
  (SELECT to_char(g, 'YYYYMM') AS MONTH
   FROM generate_series('2022-01-01'::timestamp, '2022-12-01', '1 month') AS g) AS t1
LEFT JOIN
  (SELECT id,
          to_char(timestamp, 'YYYYMM') AS MONTH
   FROM table1) AS t2 ON t1.month = t2.month
GROUP BY t1.month;

年月だけで集計しているのでto_charがあってちょっと見づらいけど。やっているのは先のようにgenerate_seriesで年月のデータを作ったもの（t1）と集計したいテーブルの日時を年月にしたもの（t2）をJOINして、年月でGROUP BYしている。

| month  | count | 
| ------ | ----- | 
| 202201 | 1     | 
| 202202 | 1     | 
| 202203 | 0     | 
| 202204 | 0     | 
| 202205 | 0     | 
| 202206 | 0     | 
| 202207 | 0     | 
| 202208 | 0     | 
| 202209 | 0     | 
| 202210 | 2     | 
| 202211 | 0     | 
| 202212 | 1     | 

というわけでできた。whereで条件指定をする場合は絞り込むのを集計後の結果に対してなのか、集計する対象に対してなのかだけ気をつけること。