SQLでflgが切り替わるタイミングで期間の集計方法について

Question

flgが切り替わるタイミングで期間を集計するselect文を作成したいと思っております。

＜使用環境＞
postgreSQL12
windows10

CREATE TABLE tr_machine_error (
 id integer,
 code VARCHAR(3),
 flg integer,
 start_dt DATETIME,
 stop_dt DATETIME
);

id	code	flg	start_dt	stop_dt
1	S01	1	2020/11/17 12:00:00	2020/11/17 13:00:00
2	S01	0	2020/11/17 12:10:00	2020/11/17 13:10:00
3	S01	0	2020/11/17 12:20:00	2020/11/17 13:20:00
4	S01	1	2020/11/17 13:10:00	2020/11/17 13:30:00
5	S01	0	2020/11/17 13:20:00	2020/11/17 13:40:00
6	S01	0	2020/11/17 13:30:00	2020/11/17 13:50:00
7	S01	1	2020/11/17 14:30:00	2020/11/17 15:10:00
8	S01	0	2020/11/17 14:40:00	2020/11/17 15:20:00

というようなデータがあるとして
flgが1になったタイミングのstart_dtと、1に切り替わる直前のレコードの
stop_dtを抽出し、集計させるselect文を作成したいと思っております。
実際抽出したいdatetimeについては太字にしてあります。

出力したい結果

id	code	flg	start_dt	stop_dt
1	S01	1	2020/11/17 12:00:00	2020/11/17 13:20:00
4	S01	1	2020/11/17 13:10:00	2020/11/17 13:50:00
7	S01	1	2020/11/17 14:30:00	2020/11/17 15:20:00

上記のような集計をすることは可能なのでしょうか。
分析関数？lagなどを使って行おうと思ったのですが、SQLが捻出できず
お力添えいただければ幸いです。

※最後のレコードが0で終わる場合には、そのレコードのstop_dtの値を
抽出させたいです。

Answer 1

以下の記事が参考になります

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

WITH SUB AS (
  SELECT *
  , SUM(flg) OVER (ORDER BY id) AS グループ番号
  FROM tr_machine_error
), SUB2 AS (
  SELECT *
  , ROW_NUMBER() OVER w as Num
  , LAST_VALUE(stop_dt) OVER w as stop_dt2
  FROM SUB
  WINDOW w as (PARTITION BY グループ番号 ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
)

SELECT id, code, flg, start_dt, stop_dt2
FROM SUB2
WHERE Num=1

参考までに

SELECT *
FROM SUB2

の実行結果は以下となります。

id	code	flg	start_dt	stop_dt	グループ番号	Num	stop_dt2
1	S01	1	2020/11/17 12:00:00	2020/11/17 13:00:00	1	1	2020-11-17 13:20:00
2	S01	0	2020/11/17 12:10:00	2020/11/17 13:10:00	1	2	2020-11-17 13:20:00
3	S01	0	2020/11/17 12:20:00	2020/11/17 13:20:00	1	3	2020-11-17 13:20:00
4	S01	1	2020/11/17 13:10:00	2020/11/17 13:30:00	2	1	2020-11-17 13:50:00
5	S01	0	2020/11/17 13:20:00	2020/11/17 13:40:00	2	2	2020-11-17 13:50:00
6	S01	0	2020/11/17 13:30:00	2020/11/17 13:50:00	2	3	2020-11-17 13:50:00
7	S01	1	2020/11/17 14:30:00	2020/11/17 15:10:00	3	1	2020-11-17 15:20:00
8	S01	0	2020/11/17 14:40:00	2020/11/17 15:20:00	3	2	2020-11-17 15:20:00

Comments

[kabochacha]

記事拝見しました。また、具体的なクエリいただきありがとうございました。たしかにnumを追加して、集計する方法もありますね。select文参考にさせていただきます！

[sazi]

@takanaweb5 さん
これSqlServerの文法ですよね？

[takanaweb5]

postgreSQL12で動作すると思いますよ。

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

スキーマ定義はDATETIME型がエラーになったので
timestamp型に変えましたが

[sazi]

@takanaweb5 さん
失礼しました。

Answer 2

同じテーブルを相手にしてstart_dt基準で並べ替えしたものにrow_number()で行番号をつけて、
行番号＝行番号-1の条件で連結すれば、flg=1になる直前の行が求まりますよね。

Comments

[kabochacha]

ご回答ありがとうございます。なるほど、行番号＝行番号-1の条件で連結する方法もクエリに落とし込んでみます。

Answer 3

flgでブロック化された、start_dtの範囲を**lead()**を用いて求めます。
その範囲の最大値のstop_dtを相関副問合せで求めます。

select id, code, flg, start_dt
     ,(select max(stop_dt) 
       from tr_machine_error 
       where start_dt >= t.start_dt and start_dt < t.lead_start_dt
      ) as stop_dt
from (
    select *
         , lead(start_dt, 1,'Infinity') 
           over(partition by code order by start_dt) 
           as lead_start_dt
    from tr_machine_error
    where flg=1
) t

Comments

[kabochacha]

saziさんのSQLが一番簡単に集計できました、lead関数まだよくわかっていないので、調べて勉強させていただきます。ご回答ありがとうございました。またよろしくお願いいたします。

[sazi]

「簡単に集計出来た」というのは、どういう意味でしょうか？
・記述が少なかった
・結果が出るのが早かった
・理解しやすかった