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

2022.08.10
データベース

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

1. やってみたこと
2. 事前準備
3. クエリ

やってみたこと

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

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

実績：完成した製品の数

このデータを「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」までしかデータを入れていません。