製造業でデータ分析 | PostgreSQL | 時系列データを5分毎にデータをまとめる

今回はデータ解析用のクエリを検証しました。

やってみたこと

例えば、製造業で実績を取得するシステムがあり、以下のようなデータが蓄積されているとします。

時間：実績を入力した時間

実績：完成した製品の数

このデータを「5分単位」でデータを丸める場合のクエリは以下の通りです。

事前準備

今回利用した環境は heroku上の PostgreSQLです。

１：テーブルを作成します

以下のクエリを実行しました。

create table product_results (
    comp_time timestamp,
    product_num integer
)

２：事前に作成しておいたデータを挿入する

今回はA5M2を使って、クエリを書かずにデータを挿入しました。

（１）以下のようにエクセルでデータを作成しておきます。

（２）時間と実績のデータをクリップボードにコピーします

（３）レコードの貼り付けをクリックする

※画面が一時的に固まる場合もありますが、データの挿入には少し時間がかかりますので待ちましょう。

試しにデータ取得

select all してみた結果です。件数は98件しか入れていませんが、大体1.1秒ほど掛かっています。

クエリ

以下が五分毎の集計を行うクエリです。

select
    date_trunc('hour',comp_time)
     + cast(floor(extract(minutes from comp_time)/5)*5||'minutes' as Interval) as 丸め時刻
    ,sum(product_num) as 生産数
from product_results
group by 
    date_trunc('hour',comp_time)
     + cast(floor(extract(minutes from comp_time)/5)*5||'minutes' as Interval)
order by 丸め時刻

今回は「2022/07/01 00:00:00」から「2022/07/01 00:30:00」までしかデータを入れていません。

・date_trunc：timestampのcomp_timeから、時間情報を切り捨てます

例）date_trunc(‘day’, ‘2022/07/01 08:10:00’ ) ならば ‘2022/07/01 00:00:00’ になる

・cast( floor(extract(minutes from comp_time)/5) *5 || ‘minutes’ as Interval )

関数が複数内包されているので分かりにくいのですが以下のような役割です。

１：extract(minutes from comp_time) ：分の値を取り出す

２：extract(minutes from comp_time)/5：5分毎なので、5で割る

３： floor(extract(minutes from comp_time)/5) ：小数点以下を切り捨て

４：floor(extract(minutes from comp_time)/5)*5：5分毎なので、5を掛ける

５：date_trunc(‘day’, comp_time) + cast(**** || ‘minutes’ as Interval)：分を加算する

以上です。