【Postgresql】generate_series関数を集計に使用する

前回、generate_series関数でダミーデータを作る方法を書きました。

uranaga512.hatenablog.com

generate_series関数はちょっとしたデータの集計にも使えるので、今回はそれについて書きます。

日毎のレコード数の集計

例えば以下のようなテーブルがあったとします。
(何かしらのログが登録されるテーブルをイメージしています)

CREATE TABLE hoge_log (
  created_at timestamp NOT NULL
);

１日毎に何件のレコードがあるか調べたい場合、単にGROUP BYすればよいです。

SELECT
  date_trunc('DAY', created_at)::date AS log_date,
  count(*)
FROM
  hoge_log
GROUP BY
  log_date
ORDER BY
  log_date
;
/*
  log_date  | count 
------------+-------
 2019-01-01 |     2
 2019-01-02 |     1
 2019-01-04 |     3
 2019-01-05 |     1
(4 rows)
*/

ここでちょっと気になるのが
「件数が0件の場合、レコードが出力されない」
ということです。

上の例では、2019-01-03のレコードは出力されていません。

件数が0件の場合もレコードが出力されるようにしたい場合、generate_series関数が役に立ちます。

generate_seriesを使って、0件の日付も出力

SQLは以下のようになります。

SELECT
  series.log_date,
  count(hoge_log.*)
FROM
  (
    SELECT
      date_trunc(
        'DAY', 
        generate_series(
          '2019-01-01 00:00'::timestamp,
          '2019-01-05 00:00'::timestamp,
          '1 day')
      )::date AS log_date
  ) series
  LEFT JOIN hoge_log
    ON (series.log_date = date_trunc('DAY', hoge_log.created_at))
GROUP BY
  series.log_date
ORDER BY
  series.log_date
;
/*
  log_date  | count 
------------+-------
 2019-01-01 |     2
 2019-01-02 |     1
 2019-01-03 |     0
 2019-01-04 |     3
 2019-01-05 |     1
(5 rows)
*/

generate_series関数で日付の雛形を作ってあげて、それに対して実テーブルをLEFT JOINする形になっています。

count(hoge_log.*) のように、実テーブルのレコードだけをカウントしているのがポイントです。