やりたいこと
- 時間ごとのレコード数の集計を取りたい
- レコードが生成されない時間もある。その時はゼロ扱いとしたい。
サンプルデータ
テーブル定義
| カラム名 | データ型 | 備考 |
|---|---|---|
| seq | serial | 通番 |
| freetext | text | 何らかのデータ |
| created_at | timestamp | 作成日時(default current_datetime) |
実際はfreetext以外にも入力値等を記録するカラムが存在するが割愛。
期待する出力結果
| datetime | count |
|---|---|
| 2017/12/27 12 | 17 |
| 2017/12/27 11 | 5 |
| 2017/12/27 10 | 0 |
| 2017/12/17 09 | 0 |
1時間ごとのレコード数の集計を行いたい。
該当期間にレコードがなければ、ゼロとしたい。
実例
3日前から1時間ごとのレコード数集計を行いたい
SELECT
datetable.datetime,
COALESCE(total.cnt, 0) as count --(5) NULLデータをゼロに変換する
FROM
(
-- (3)日付時刻の連続データを自動生成する
SELECT TO_CHAR(generate_series, 'YYYY/MM/DD HH24') as datetime
FROM
generate_series(
CAST(TO_CHAR(NOW() + INTERVAL '-3 day', 'YYYY/MM/DD HH24:00:00') AS timestamp),
CAST(TO_CHAR(NOW(), 'YYYY/MM/DD HH24:00:00') AS timestamp),
'1 hour'
)
) AS datetable
-- (4)日付時刻の連続データと、集計データを外部結合する
LEFT JOIN
(
-- (2)データテーブルのデータを日付時刻(時間)で集計する
SELECT
TO_CHAR(created_at, 'YYYY/MM/DD HH24') as datetime,
count(*) AS cnt
FROM
(
-- (1) データテーブルのデータを、予めフィルタリングしておく副問い合わせ
SELECT * FROM datatable
WHERE created_at >= CAST(TO_CHAR(NOW() + INTERVAL '-3 day', 'YYYY/MM/DD HH24:00:00') AS timestamp)
) as source
GROUP BY datetime
) AS total
ON datetable.datetime = total.datetime
ORDER BY datetable.datetime DESC
解説
データテーブルのデータを予めフィルタリングする
必要なデータだけカウントするよう、あらかじめフィルタリングしておく
この例では日付のみ行っているが、ほかに絞り込み条件がある場合はここでやっておくデータテーブルのデータを日付時刻(時間)で集計する
created_atの分・秒を切り捨てたものでグループ化し、件数をカウントする日付時刻の連続データを自動生成する
PostgreSQLの固有関数generate_seriesを用いて、1時間ごとの連続データを生成する日付時刻の連続データと、集計データを外部結合する
集計データ側に存在しないものはNULLとなるNULLデータをゼロに変換する
NULLだった場合、任意の値に変換してくれるCOALESCE関数を使用し、NULLをゼロに変換する
注意点
データテーブル側のボリュームが大きい場合は、(1)で絞り込みをしておかないと速度に影響する
(2)と(3)で使用するデータの書式を合わせておかないと結合が出来ない
今現在を起点に特定期間(3日前から等)の集計を行う場合、日付時刻の扱いに注意する
単純にNOW() - INTERVAL('-3 day')などを用いると、分・秒まで絞り込みに使われてしまい、データの取りこぼしが発生する
上記例では、TO_CHAR(NOW() + INTERVAL '-3 day', 'YYYY/MM/DD HH24:00:00')で、分・秒を切り捨て、timestamp型にキャストすることで回避している
あとがき
- 自分用にメモした内容です。必要に応じて読み替えてください。
- こうしたほうがいいよ的なアドバイスを頂けると大変助かります
以上