Posted at

時間毎の件数を0も含めて取得するSQL

時間毎の件数を取りたいが、

そもそも0件の時間帯についてはデータが取得できないので

取れるようにする

(例)

テーブル名は「users」

カラムは「created_at」(Timestamp)

1時間毎のデータ件数を表示する

generate_seriesで1時間毎、0件のデータを作成し、

それに元のデータをUnion Allして、SUMをとるだけ。

generate_seriesの存在を忘れがちなので忘備録。

select hour, sum(c) from 

(
select hour, 1 as c from users, date_trunc('hour', created_at) as hour where DATE(created_at) = '2018-08-31'
union all
SELECT generate_series as hour, 0 as c FROM generate_series('2018-08-31 00:00'::timestamp,'2018-08-31 23:00', '1 hours')
) as a
group by hour
order by hour