レコードがない日付のデータも出力したい

やりたいこと

PostgreSQLでランダムな日時で登録されるテーブルに対して集計を行う際に歯抜けにならないようにレコードを取得したい。
webアプリであればphpやrubyなどのプログラミング言語で組み立てればいいが今回はsqlだけでどうにかしたかった。
ちなみにjoinする用の仮テーブルを作ることも考えたがデータ登録しなきゃいけないし汎用性がなくなるのでやりたくない。

結論

結果を先に書くと generate_series() と union all を使うことでやりたいことを実現できました。

環境

PostgresSQL 10.5

postgres=# select version();
-----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.5 (Ubuntu 10.5-0ubuntu0.18.04) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-16ubuntu3) 7.3.0, 64-bit
(1 row)

テーブル

postgres@vagrant:~$ psql -d sample
psql (10.5 (Ubuntu 10.5-0ubuntu0.18.04))
Type "help" for help.

sample=# \d logs
                                       Table "public.logs"
  Column  |            Type             | Collation | Nullable |             Default
----------+-----------------------------+-----------+----------+----------------------------------
 id       | integer                     |           | not null | nextval('logs_id_seq'::regclass)
 datetime | timestamp without time zone |           |          |
 amount   | integer                     |           |          |
Indexes:
    "logs_pkey" PRIMARY KEY, btree (id)

テストデータ

sample=# select * from logs;
 id |      datetime       | amount
----+---------------------+--------
  1 | 2018-12-10 00:00:00 |      1
  2 | 2018-12-10 00:30:00 |     18
  3 | 2018-12-10 02:30:00 |     17
  4 | 2018-12-10 03:00:00 |      1
  5 | 2018-12-10 03:30:00 |     50
  6 | 2018-12-10 04:00:00 |      3
  7 | 2018-12-10 08:00:00 |     30
  8 | 2018-12-10 08:30:00 |     27
  9 | 2018-12-10 09:00:00 |     15
 10 | 2018-12-10 09:30:00 |      8
 11 | 2018-12-10 10:00:00 |     23
 12 | 2018-12-10 10:30:00 |     83
 13 | 2018-12-10 11:00:00 |     46
 14 | 2018-12-10 11:30:00 |     46
 15 | 2018-12-10 12:00:00 |     14
 16 | 2018-12-10 15:00:00 |     56
 17 | 2018-12-10 15:30:00 |     65
 18 | 2018-12-10 18:30:00 |     39
 19 | 2018-12-10 19:00:00 |     32
 20 | 2018-12-10 21:00:00 |      5
 21 | 2018-12-10 21:30:00 |      1
 22 | 2018-12-10 23:00:00 |     14
 23 | 2018-12-10 23:30:00 |     10
(23 rows)

手順

1時間ごとのレコードにgroup byして対象の時間のamountを集計する。
そのまま集計しただけでは下記のように時間が歯抜けになってしまう。
ちなみに1時間毎のレコードにするために date_trunc を使って時間をまるめています。

sample=# select
sample-#   date_trunc('hour', datetime) as datetime,
sample-#   sum(amount) as total
sample-# from
sample-#   logs
sample-# group by
sample-#   date_trunc('hour', datetime)
sample-# order by
sample-#   date_trunc('hour', datetime)
sample-# ;
      datetime       | total
---------------------+-------
 2018-12-10 00:00:00 |    19
 2018-12-10 02:00:00 |    17
 2018-12-10 03:00:00 |    51
 2018-12-10 04:00:00 |     3
 2018-12-10 08:00:00 |    57
 2018-12-10 09:00:00 |    23
 2018-12-10 10:00:00 |   106
 2018-12-10 11:00:00 |    92
 2018-12-10 12:00:00 |    14
 2018-12-10 15:00:00 |   121
 2018-12-10 18:00:00 |    39
 2018-12-10 19:00:00 |    32
 2018-12-10 21:00:00 |     6
 2018-12-10 23:00:00 |    24
(14 rows)

理想は 2018-12-10 01:00:00 のレコードもtotal 0 で出力されてほしいのです。
そこで登場するのが generate_series() 関数です。ちょっと特殊な関数で連続する値を返してくれます。
連続した日付や時間、もちろん数値も指定することが可能です。

-- 日付
sample=# select generate_series('2018-12-01'::date, '2018-12-10'::date, '1 day') as day;
          day
------------------------
 2018-12-01 00:00:00+00
 2018-12-02 00:00:00+00
 2018-12-03 00:00:00+00
 2018-12-04 00:00:00+00
 2018-12-05 00:00:00+00
 2018-12-06 00:00:00+00
 2018-12-07 00:00:00+00
 2018-12-08 00:00:00+00
 2018-12-09 00:00:00+00
 2018-12-10 00:00:00+00
(10 rows)

-- 時間
sample=# select generate_series('2018-12-10 00:00:00'::timestamp, '2018-12-10 12:00:00'::timestamp, '1 hour') as time;
        time
---------------------
 2018-12-10 00:00:00
 2018-12-10 01:00:00
 2018-12-10 02:00:00
 2018-12-10 03:00:00
 2018-12-10 04:00:00
 2018-12-10 05:00:00
 2018-12-10 06:00:00
 2018-12-10 07:00:00
 2018-12-10 08:00:00
 2018-12-10 09:00:00
 2018-12-10 10:00:00
 2018-12-10 11:00:00
 2018-12-10 12:00:00
(13 rows)

-- 数値
sample=# select generate_series(1, 10, 1) as num;
 num
-----
   1
   2
   3
   4
   5
   6
   7
   8
   9
  10
(10 rows)

generate_series() で生成した時間ごとのレコードと集計対象のテーブルを union all で合体させることで歯抜けのレコード部分を埋めてしまおうという作戦です。
下記のsqlで歯抜けにならないレコード取得に成功しました。

select
  union_table.datetime as datetime,
  sum(union_table.amount) as total
from (
  select generate_series('2018-12-10 00:00:00'::timestamp, '2018-12-10 23:00:00'::timestamp, '1 hour') as datetime, 0 as amount
  union all
  select date_trunc('hour', datetime) as datetime, amount from logs
) as union_table
group by
  union_table.datetime
order by 
  union_table.datetime
;

結果

      datetime       | total
---------------------+-------
 2018-12-10 00:00:00 |    19
 2018-12-10 01:00:00 |     0
 2018-12-10 02:00:00 |    17
 2018-12-10 03:00:00 |    51
 2018-12-10 04:00:00 |     3
 2018-12-10 05:00:00 |     0
 2018-12-10 06:00:00 |     0
 2018-12-10 07:00:00 |     0
 2018-12-10 08:00:00 |    57
 2018-12-10 09:00:00 |    23
 2018-12-10 10:00:00 |   106
 2018-12-10 11:00:00 |    92
 2018-12-10 12:00:00 |    14
 2018-12-10 13:00:00 |     0
 2018-12-10 14:00:00 |     0
 2018-12-10 15:00:00 |   121
 2018-12-10 16:00:00 |     0
 2018-12-10 17:00:00 |     0
 2018-12-10 18:00:00 |    39
 2018-12-10 19:00:00 |    32
 2018-12-10 20:00:00 |     0
 2018-12-10 21:00:00 |     6
 2018-12-10 22:00:00 |     0
 2018-12-10 23:00:00 |    24
(24 rows)

出来ました！

所感

いつもはMySQLばかり使っているので久々のPostgreSQLでした。
MySQLでどうやるかは別の機会に調べたいと思います。
ここまで見ていただきたありがとうございました！