PostgreSQLで5分単位にレコード集計
やりたいこと
timestamp型のカラムを持つテーブルがあって、5分単位、10分単位にレコードを集計したい。みたいなことが2、3年に一度ある。その度に試行錯誤しているので、未来の俺に向けてここにメモを残す。
前提バージョン
9.6.10
使用するテーブル
2020年大阪国際女子マラソンのリザルトを管理するテーブルを用意する。面倒だから準備するレコードは上位20名までに限定する。finished_at
postgres=# \d osaka_marathon_2020_result
Table "public.osaka_marathon_2020_result"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+---------
no | integer | | |
name | character varying(32) | | |
finished_at | timestamp without time zone | | |
postgres=# select * from osaka_marathon_2020_result;
no | name | finished_at
----+------------------------------+---------------------
6 | 松田 瑞生 | 2020-01-26 14:31:47
3 | ミミ・ベレテ | 2020-01-26 14:32:40
7 | シンタエフ・レウェテン | 2020-01-26 14:33:03
2 | メスケレム・アセファ | 2020-01-26 14:33:31
10 | リサ・ウェイトマン | 2020-01-26 14:36:02
4 | ボルネス・ジェプキルイ | 2020-01-26 14:36:24
16 | 山口 遥 | 2020-01-26 14:36:35
9 | ファツマ・サド | 2020-01-26 14:37:18
1 | ハフタムネッシュ・テスファイ | 2020-01-26 14:37:50
13 | 田中 華絵 | 2020-01-26 14:37:51
41 | 井上 彩花 | 2020-01-26 14:37:54
19 | ムンフザヤ・バヤルツォグト | 2020-01-26 14:38:03
8 | 小原 怜 | 2020-01-26 14:38:12
14 | カタリナ・スタインラック | 2020-01-26 14:38:48
11 | 谷本 観月 | 2020-01-26 14:38:48
42 | 兼重 志帆 | 2020-01-26 14:38:51
43 | 西田 美咲 | 2020-01-26 14:38:51
44 | 水口 瞳 | 2020-01-26 14:42:33
17 | 下門 美春 | 2020-01-26 14:42:48
12 | 竹地 志帆 | 2020-01-26 14:44:09
(20 rows)
分単位に集計する場合
単純に日単位、時間単位、分単位に集計するのであれば、date_trunc関数が使える。
postgres=# select date_trunc('minute', finished_at), count(*) from osaka_marathon_2020_result group by 1 order by 1;
date_trunc | count
---------------------+-------
2020-01-26 14:31:00 | 1
2020-01-26 14:32:00 | 1
2020-01-26 14:33:00 | 2
2020-01-26 14:36:00 | 3
2020-01-26 14:37:00 | 4
2020-01-26 14:38:00 | 6
2020-01-26 14:42:00 | 2
2020-01-26 14:44:00 | 1
(8 rows)
5分単位に集計する場合
こんな感じになる。
postgres=# select to_timestamp(trunc(extract('epoch' from finished_at) / 300) * 300), count(*) from osaka_marathon_2020_result group by 1 order by 1;
to_timestamp | count
------------------------+-------
2020-01-26 14:30:00+00 | 4
2020-01-26 14:35:00+00 | 13
2020-01-26 14:40:00+00 | 3
(3 rows)
やっていることは以下の通り。
-
extract関数でタイムスタンプ型からUNIXエポック時間を取得。 -
UNIXエポック時間を集計単位(例では300秒)で割る。
-
trunc関数で小数点以下を切り捨てる。 -
集計単位(例では300秒)を掛けて、丸めたUNIXエポック時間を得る。
-
to_timestamp関数でタイムスタンプ型に変換する。
ちなみにto_timestampの戻り値はtimestamp with time zone型なので、元のカラムのtimestamp without time zone型と合わせるなら最後にtimezone関数を使ってあげれば良い。
跋文
