帯グラフにできるようなSQLの抽出

Question

前提

MySQLデータベースからデータを抽出し、Excelでグラフ化しようとしています。
Window関数を使ったりして悪戦苦闘しているのですが、なかなか思ったような出力結果になりません。

実現したいこと

table01
・epoch_sec(YYYYMMDD HH:MM:SS)
・sensor_id(sensor_mstとリンク)
・stay_time(秒)

データサンプル
epoch_sec sensor_id stay_time
20230113 08:30:00 A01 4
20230113 08:30:05 A01 2
20230113 08:30:13 B01 4
20230113 08:30:18 B01 1
20230113 08:35:00 A01 3
20230113 08:39:05 A01 4
20230113 08:39:25 C01 1
20230113 08:40:00 A01 5
20230113 08:41:00 A01 4
20230113 08:41:05 A01 5
20230113 08:41:10 A01 4
20230113 08:48:40 B01 5
20230113 08:48:50 A01 3
20230113 08:48:55 A01 4
20230113 08:59:25 C01 1
20230113 08:59:40 B01 5

１．動作要件
・エリアごとにセンサーがある。
・センサーが検知したら、5秒間ポーリングし、検知したものが存在したstay_timeと、そのsensor_idを出力。

２．集計したい内容
・8:00-18:00　の間で、10分刻みで最も多い（滞在時間が長い）sensor_idを出力し、
sensor_idごとに色分けして帯グラフで表示する。
⇒1日の滞在時間の割合を表現したい。
・データがない時間帯は空白or ゼロで出力。（可能であれば付けたい処理）

発生している問題・エラーメッセージ

10分ごとにMAX値を集計する部分も含めて、どのようなSQLで書けばよいか悩んでいます。
私のSQLはcountさせていますが、試行錯誤中によるものです。

該当のソースコード

SELECT 
min(DATE_FORMAT(FROM_UNIXTIME(p2.epoch_sec),'%Y-%m-%d %H:%i:%s')) as epoch_sec, 
sensor_id,  
count(*) as cnt, 
buff
FROM (
SELECT p1.epoch_sec, p1.sensor_id
      ,Row_Number() over(partition by p1.sensor_id order by p1.epoch_sec) rn
      ,p1.epoch_sec-Row_Number() over(partition by p1.sensor_id order by p1.epoch_sec)+1 as buff
FROM sample_db.table01 p1
INNER JOIN sample_db.sensor_mst s ON p1.sensor_id = s.sensor_id
WHERE  DATE_FORMAT(FROM_UNIXTIME(p1.epoch_sec),'%Y%m%d')  BETWEEN '20230110'AND '20230127'
) p2
GROUP BY sensor_id,buff
ORDER BY buff,sensor_id

試したこと

このようなデータが出力されれば、グラフが実現できそうです・・・

Answer 1

table01のepoch_secがTIMESTAMP型だとすると、以下のようなSQLで実現できそうです。

WITH RECURSIVE R (epoch_sec_from) AS (
    SELECT TIMESTAMP '2023-01-13 08:00:00'
  UNION ALL
    SELECT TIMESTAMPADD(MINUTE, 10, epoch_sec_from) 
    FROM R 
    WHERE epoch_sec_from < TIMESTAMP '2023-01-13 18:00:00'
)
SELECT S.epoch_sec_from, COALESCE(S.sensor_id, 'なし') AS sensor_id, COALESCE(S.sm, 0) AS sm
FROM (
  SELECT 
    T.sensor_id, 
    R.epoch_sec_from, 
    SUM(stay_time) AS sm, 
    ROW_NUMBER() OVER (PARTITION BY R.epoch_sec_from ORDER BY SUM(stay_time) DESC) AS rn
  FROM R
  LEFT OUTER JOIN table01 T ON R.epoch_sec_from <= T.epoch_sec AND T.epoch_sec < TIMESTAMPADD(MINUTE, 10, epoch_sec_from)
  GROUP BY T.sensor_id, R.epoch_sec_from
) S
WHERE S.rn = 1

共通表式Rは再帰SQLで、10分おきの目盛を生成しています。ここはプログラムなどで生成してもよいです。また、10分おきの目盛に該当するデータがない場合、sensor_idはなし、stay_timeは0としています。

Comments

[blue0524]

ご回答ありがとうございます。
再帰SQLはトライしたことがなかったので勉強になりました。
epoch_sec ですが、データ型はDOUBLEで、UNIXエポック秒を入れています。

ご教示いただいたSQLを参考にSQL文を調整して実行したところ、希望していた結果が出力されました。
ありがとうございます！
最終的にはストアドプロシージャにして実行する予定なのですが、その際は、
引数として２つの日付を渡すつもりでおります。（いつからいつまで）
あともう少しアレンジを加えて実務にすり合わせていきたいと思います。
それにしても、SQL1本でここまで実現できるのですね。もっと精進したいと思います。
本当にありがとうございました。

[blue0524]

一度解決したと判断したのですが、新たに気づいた点がありまして、
大変申し訳ないのですが質問に回答いただければありたがいです。
仮想の時間テーブルですが、LEFT OUTER JOIN　しているにも関わらず、
すべての時間が表示されないことは判明しました。
理想ですと、R.epoch_sec_fromの表は、table01 にデータ有無に関係なく、
すべての時間が表示されると思ったのですが、両方のテーブルにあるデータだけが
抽出されているようなので、JOINする方法で注意すべき点はありますでしょうか。
以上、よろしくお願いいたします。

[neko_the_shadow]

・具体的にどのようなデータセットで発生しますか?
・回答のSQLと変更しているなどあれば教えてください
・回答のSQLはMYSQL8で検証しています。違いがあればその点も教えてください。

[blue0524]

コメントありがとうございます。
SQL文は以下の通りです。

WITH RECURSIVE R (epoch_sec_from) AS (
SELECT TIMESTAMP '2023-01-16 08:30'
UNION ALL
SELECT TIMESTAMPADD(MINUTE, 10, epoch_sec_from)
FROM R
WHERE epoch_sec_from < TIMESTAMP '2023-01-17 17:30'
)
SELECT SS.epoch_sec_from,
COALESCE(SS.sensor_id, 'なし') AS sensor_id,
CASE WHEN SS.sm > 10 THEN 10 ELSE COALESCE( SS.sm, 0) END AS sm
FROM (
SELECT
T.sensor_id,
R.epoch_sec_from,
SUM( T.stay_time )/60 AS sm,
ROW_NUMBER() OVER (PARTITION BY R.epoch_sec_from ORDER BY SUM(T.stay_time) DESC) AS rn
FROM R
LEFT OUTER JOIN table01 T ON (R.epoch_sec_from <= FROM_UNIXTIME(T.epoch_sec) AND FROM_UNIXTIME(T.epoch_sec) < TIMESTAMPADD(MINUTE, 10, epoch_sec_from))
WHERE
DATE_FORMAT(FROM_UNIXTIME(T.epoch_sec),'%Y%m%d') between '20230116' and '20230117'

GROUP BY T.sensor_id, R.epoch_sec_from
) SS
WHERE SS.rn = 1

これを実行すると、歯抜けが生じます。
09:10が表示されません。
＜実行結果＞
epoch_sec_from sensr_id sm
2023-01-16 08:30 3 6.23
2023-01-16 08:40 3 3.31
2023-01-16 08:50 3 10
2023-01-16 09:00 3 10
2023-01-16 09:20 3 9.1
2023-01-16 09:30 3 10
・・・

この時のtable01は、
8時30分から秒刻みで連続でデータがありますが、
2023-01-16 09:09:41 の次が、
2023-01-16 09:21:59 です。

尚、MySQLは8.0です。
以上、よろしくお願いいたします。

[neko_the_shadow]

WHERE DATE_FORMAT(FROM_UNIXTIME(T.epoch_sec),'%Y%m%d') between '20230116' and '20230117'
このWHERE節が原因ですね。おそらくこのWHERE節は不要なので削除するか、もしくはLEFT OUTER JOIN句を以下のようにしてください。
LEFT OUTER JOIN (SELECT * FROM table01 WHERE DATE_FORMAT(FROM_UNIXTIME(T.epoch_sec),'%Y%m%d') between '20230116' and '20230117') AS T ON ...

[blue0524]

ありがとうございます。
１．WHERE節の削除
２．LEFT OUTER JOIN句の修正
どちらも試してみたのですが、
両方とも前回と同じ結果となってしまいました。09:10が表示されず、です。
ちなみに１と２の両方を対応して実行しても同じでした。
以上になります。JOIN句に原因があるのでしょうか。

[neko_the_shadow]

WHERE節が原因でめもりの抜けが発生しているので、WHERE節の削除でうまくいかないとなると、ちょっと原因がわからないですね…。わたしの環境ですと、めもりの抜けは発生しないので。環境起因のほかに、単なる見落としやストアドプロシージャの設計ミス、スプレッドシートの誤りなど、何かしらの問題がある可能性がありますが、ここではデバッグしきれないです。ご自身の環境でデバッグして問題を発見していただくか、Oracle社の有料サポートへ問い合わせていただくかなど、対応をお願いします。

[blue0524]

わかりました。何度も丁寧に対応いただきまして、ありがとうございました。
もう少し悩んでみます。
ここはいったん解決といたします。お世話になりました。

Answer 2

こういった類は、データに依らないスケール（目盛）を先ずは準備する事です。
質問の内容であれば、１０分刻みのスケールですね。

以下参考
日付の仮想表

Comments

[blue0524]

ご回答ありがとうございます。ご紹介いただいたサイトを確認しました。
新たに時間のみのテーブルを作成することも検討していたところでしたので、
仮想の表をつくるという新たな手法を知ることができました。
これを取り入れながらもう少し悩んでみたいと思います。ありがとうございました。