日時が登録されているテーブルから、開始時刻と終了時刻を出力させたい。

Question

実現したいこと

DB初心者です、ご教示いただけますと幸いです。
エラー発生時刻が登録されている既存のトランザクションテーブルから、
エラー開始時刻と終了時刻を抽出するselect文を作りたいと思っています。

＜使用環境＞
postgreSQL12
windows10

具体的な内容としては、

CREATE TABLE tr_machine_error (
 machine VARCHAR(2),
 error_code VARCHAR(3),
 error_date DATETIME
);

<トランザクションテーブル>

machine	error_code	error_date
A	E01	2020/11/17 12:00:00
A	E01	2020/11/17 12:10:00
A	E01	2020/11/17 12:20:00
A	E01	2020/11/17 12:30:00
A	E02	2020/11/17 14:20:00
A	E02	2020/11/17 14:30:00
A	E01	2020/11/17 16:30:00
A	E01	2020/11/17 16:40:00
A	E01	2020/11/17 16:50:00
B	E02	2020/11/17 17:00:00
B	E02	2020/11/17 17:10:00
B	E02	2020/11/17 17:20:00
A	E03	2020/11/17 19:40:00
A	E03	2020/11/17 19:50:00
A	E03	2020/11/17 20:00:00
A	E03	2020/11/17 20:10:00

といったテーブルが存在するとして、
出力したい結果は

machine	error_code	error_start	error_stop
A	E01	2020/11/17 12:00:00	2020/11/17 12:30:00
A	E02	2020/11/17 14:20:00	2020/11/17 14:30:00
A	E01	2020/11/17 16:30:00	2020/11/17 16:50:00
B	E02	2020/11/17 17:00:00	2020/11/17 17:20:00
A	E03	2020/11/17 19:40:00	2020/11/17 20:10:00

このように、マシンorエラーコードが切り替わったタイミングで、
開始時刻と停止時刻がわかる内容を出力させたいです。

試してみたこと

SELECT
 machine,
 error_code,
 max(error_date),
 min(error_date)
from
 tr_machine_error
group by
 machine,error_code

maxとminで出力させようと思ったのですが、上記の表だと『マシンAとE01』が2回登場するため

machine	error_code	error_start	error_stop
A	E01	2020/11/17 12:00:00	2020/11/17 16:50:00

となってしまいます。

わかりづらく申し訳ありませんが、ご教示いただけると幸いです。
何か疑問点などあれば、ご質問いただければと思います。
よろしくお願いいたします。

Comments

[Orlofsky]

質問にテーブルの定義はCREATE TABLE文で、試したSELECT文を https://teratail.com/help/question-tips#questionTips3-7 の [コード] で追記してください。

[kabochacha]

ご指摘ありがとうございます！
追記させていただきました、よろしくお願いいたします。

Answer 1

SQLで分析関数を使って、特定の条件を満たしたレコードの前と後で別々のグループとしてグループ化する

上記の記事の応用です。
試してみたらうまく出来たので、解決済みの案件ですが
せっかくですのでコメントしました。
返信は不要です。

https://www.db-fiddle.com/f/oPHaxqhi65azrJAUzetRGT/0

WITH SUB AS ( 
  SELECT *
  , LAG(machine)    OVER(ORDER BY error_date) AS LAG_machine
  , LAG(error_code) OVER(ORDER BY error_date) AS LAG_error_code
    FROM tr_machine_error 
), SUB2 AS (
  SELECT *
  , CASE WHEN (machine, error_code) <> (LAG_machine, LAG_error_code) 
    THEN 1 ELSE 0 END AS FLG
  , SUM(CASE WHEN (machine, error_code) <> (LAG_machine, LAG_error_code) 
        THEN 1 ELSE 0 END) OVER(ORDER BY error_date) AS グループ番号 
  FROM SUB
)

SELECT machine, error_code
, MIN(error_date) AS error_start
, MAX(error_date) AS error_stop
FROM SUB2
GROUP BY グループ番号, machine, error_code
ORDER BY グループ番号

Answer 2

error_dateは重複なく連続している前提で。

連続したerror_dateからみて、machineとerror_codeが変わるタイミングがブレークポイントです。
そのブレークポイントを個々のデータにグループの情報として割り当て、全体を集計します。
区切りの情報を生成するにはlag()やlead()のwindow関数を用います。
※文字列の結合だと組合せで同じになるかもしれないのでarray[]に変更

with step1 as (
  select *
       , lag(array[machine, error_code] ::text[], 1, array['','']) 
         over(order by error_date) lag_key
  from tr_machine_error
), step2 as (
  select case when array[machine, error_code] ::text[] != lag_key then error_date end error_start
  from step1
) , step3 as (
  select error_start 
  from step2
  where error_start is not null
) , step4 as (
  select error_start, lead(error_start) over(order by error_start) lead_error_start
  from step3
)
select machine, error_code, error_start, max(error_date) as error_end
from  tr_machine_error left join step4
      on    tr_machine_error.error_date >= step4.error_start
        and (tr_machine_error.error_date < lead_error_start or lead_error_start is null)
group by machine, error_code, error_start
order by error_start

・解説
**step1:**lag()を用い直前のmachine, error_codeを取得します。
**step2:**step1の情報を元にブレークしている場所のみerror_startを設定します。
**step3:**step4で使用するlead()用にerror_startが設定されている情報のみにします。
**step4:**lead()を用いて、error_startでグルーピングする範囲を作成します。
**最終:**step4の情報と結合し、集計によってerror_endを求めます。

ぱっと思いついて組み立てただけなので、観点を変えると簡潔な記述があるかもしれません。

DB初心者です

初心者レベルならSQLのみでは解決できません。
テーブルを日時順に読み込んで、start、endを作成するような処理になるはずです。
指針もなくこれを丸投げするプロジェクトだったりすると、前途多難ですね。

Comments

[kabochacha]

ありがとうございます。試してみたところ、無事できました！
ですが、確かにDB初心者がSQLのみで解決するような内容ではないと思いました。
最初から開始時刻と終了時刻がわかるようなデータのとり方が一番いいのですが、
テーブル構造など一旦見直しつつ、考えてみます。
ありがとうございました。

[sazi]

集計で括る為の情報が不足しているので、tr_machine_error に追加する際にそういった情報（例えばエラーID）を設定するだけで、複雑な集計ではなくなります。

[kabochacha]

ご無沙汰しております。
まだ、ご回答いただけそうであればご質問させていただきたいです！
やはり、errorの開始時間と、終了時間がわかるようなプログラムがないようで
既存のtr_machine_errorテーブルの構造を見直すという方向になり、カラムを追加しようと
思っています。アドバイスいただきました、

>集計で括る為の情報が不足しているので、tr_machine_error に追加する際にそういった情報（例えば?>エラーID）を設定するだけで、複雑な集計ではなくなります。

エラーIDとは、シーケンスのようなものでしょうか？

また、上記の質問とは別に、tr_machine_errorは必ず10分ごとにレコードが吐かれるようで、
その仕組みを使って、『直前のレコードと比較して、差が10分ではない場合、そのレコードを終了日時とする』みたいな条件を考えているのですが、こちらもSQLで可能なのでしょうか。

[sazi]

> エラーIDとは、シーケンスのようなものでしょうか？
シーケンスではありません。
グループ毎に一意となるものをルールに基づき自前で発番するものを想定しています。

>tr_machine_errorは必ず10分ごとにレコードが吐かれるようで、その仕組みを使って、『直前のレコードと比較して、差が10分ではない場合、そのレコードを終了日時とする』みたいな条件を考えているのですが、こちらもSQLで可能なのでしょうか。

10分間隔で行われる処理とは別な処理で行うという事でしょうか？
いまいち仕様が把握できませんが、SQLでは無理と言うほどでは無いと思います。

だけど、出来ない事は出来ないと言う勇気も必要ですよ。