Timestampと信号0/1のカラムのテーブルから信号が1状態の開始/終了時刻データに変換したい

Question

前提・実現したいこと

Timestampと3種類の信号の状態が0/1で格納されたテーブルtbl_dummyがあります。

ts col_a col_b col_c
2020/12/23 14:12 0 1 0
2020/12/23 14:13 0 0 1
2020/12/23 14:14 0 1 0
2020/12/23 14:15 0 1 0
2020/12/23 14:16 0 0 1
2020/12/23 14:17 0 0 1
2020/12/23 14:18 0 0 1
2020/12/23 14:19 0 0 1
2020/12/23 14:20 0 0 1
2020/12/23 14:21 1 0 1
2020/12/23 14:22 1 1 1
2020/12/23 14:23 1 1 0
2020/12/23 14:24 0 0 1
2020/12/23 14:25 0 0 1
2020/12/23 14:26 1 0 0

得たい結果は各信号がON（1）の区間の開始時刻（start_tm）、終了時刻（end_tm）、信号名（A/B/C）です。

start_tm end_tm on_signal
null 2020/12/23 14:13 B
2020/12/23 14:13 2020/12/23 14:14 C
2020/12/23 14:14 2020/12/23 14:16 B
2020/12/23 14:16 2020/12/23 14:23 C
2020/12/23 14:21 2020/12/23 14:24 A
2020/12/23 14:22 2020/12/23 14:23 B
2020/12/23 14:24 2020/12/23 14:26 C
2020/12/23 14:26 null A

以下のSQLを作成しましたが、望む結果が得られません。
どのようにすればよいか、アドバイスお願いします。

該当のソースコード

SELECT 
  CASE WHEN col_a_dif = 1 THEN ts
       WHEN col_b_dif = 1 THEN ts
	   WHEN col_c_dif = 1 THEN ts
	   END AS start_tm,
  CASE WHEN col_a_dif = -1 THEN ts
       WHEN col_b_dif = 1 THEN ts
	   WHEN col_c_dif = 1 THEN ts
	   END AS end_tm,		
  CASE WHEN col_a_dif = -1 THEN 'A'
       WHEN col_b_dif = 1 THEN 'B'
	   WHEN col_c_dif = 1 THEN 'C'
	   END AS on_signal		
FROM(
SELECT ts, 
  col_a - lag(col_a, 1) over(ORDER BY ts) as col_a_dif,
  col_b - lag(col_b, 1) over(ORDER BY ts) as col_b_dif,
  col_c - lag(col_c, 1) over(ORDER BY ts) as col_c_dif
FROM tbl_dummy) as tbl_dif
WHERE (col_a_dif | col_b_dif | col_c_dif) <> 0
;

上記SQLの結果

補足情報（FW/ツールのバージョンなど）

PostgreSQL　バージョン10

Answer 1

解決済みですが、参考までに

WITH SUB(TS, SIGNAL, COL) AS (
-- 横もちを縦もちへ
SELECT TS, UNNEST(ARRAY['A','B','C']), UNNEST(ARRAY[col_a,col_B,col_C]) FROM TBL_DUMMY
UNION ALL -- 先頭にLowValueの日付を追加
SELECT '-INFINITY', UNNEST(ARRAY['A','B','C']), -1
UNION ALL -- 末尾にHighValueの日付を追加
SELECT  'INFINITY', UNNEST(ARRAY['A','B','C']), -1

), SUB2 AS (
-- １行前の値の情報を追加
SELECT *
, LAG(COL) OVER (PARTITION BY SIGNAL ORDER BY TS) AS LAG_COL
FROM SUB

), ST AS ( 
-- 開始時間の一覧
SELECT *
, ROW_NUMBER() OVER (PARTITION BY SIGNAL ORDER BY TS) AS NUM
FROM SUB2
WHERE (LAG_COL, COL) IN((0,1),(-1,1))

), ET AS (
-- 終了時間の一覧
SELECT *
, ROW_NUMBER() OVER (PARTITION BY SIGNAL ORDER BY TS) AS NUM
FROM SUB2
WHERE (LAG_COL, COL) IN((1,0),(1,-1))

)

-- 開始時間と終了時間をJOINさせる
SELECT
  CASE WHEN ST.LAG_COL = -1 THEN NULL ELSE ST.TS END AS START_TM -- 先頭行はNULL
, CASE WHEN ET.COL     = -1 THEN NULL ELSE ET.TS END AS END_TM   -- 末尾行はNULL
, ST.SIGNAL
FROM ST INNER JOIN ET USING(SIGNAL, NUM)
ORDER BY ST.TS, ET.TS

Comments

[hideki.]

回答ありがとうございます。
コメントも入っているので、コードの考え方を理解するのに助かります。
neko_the_shadow様のコードでもそうですが、今まで全く知らなかったキーワードが出てきます。自分のカバーするSQLスキル範囲がまだほんの少しなのだと、実感しております。これから勉強していきます。

Answer 2

たとえば以下のようなクエリになると思います (もうすこし単純に書けるような気もしますが)

WITH R AS (
  SELECT ts, signal, col, last_col, ROW_NUMBER() OVER (PARTITION BY signal ORDER BY ts) AS rownum
  FROM (
    SELECT ts, signal, col, LAG(col) OVER (PARTITION BY signal ORDER BY ts) AS last_col
    FROM (
                SELECT ts, 'A', col_a FROM tbl_dummy
      UNION ALL SELECT ts, 'B', col_b FROM tbl_dummy
      UNION ALL SELECT ts, 'C', col_c FROM tbl_dummy
    ) AS P (ts, signal, col)
  ) AS Q
  WHERE (last_col = 1 AND col = 0) OR (last_col = 0 AND col = 1)
)
  SELECT 
    ts,
    (SELECT MIN(R2.ts) FROM R AS R2 WHERE R1.signal = R2.signal AND R1.ts < R2.ts AND R2.last_col = 1 AND R2.col = 0), 
    signal
  FROM R AS R1
  WHERE R1.last_col = 0 AND R1.col = 1
UNION ALL 
  SELECT NULL, ts, signal FROM R WHERE rownum = 1 AND last_col = 1 AND col = 0

Comments

[hideki.]

回答ありがとうございます。

取り急ぎ、実行してみたところ、「得たい結果」の最初と最後の行以外は正しく処理されました。最初と最後の行は開始/終了時刻がセットにならないケースなので、その後の処理でどう扱うか検討しようと思っていたところなので、今回の提示SQLでも大変参考になります。コードの意味するところの理解はこれからとなりますが、SQLで無理ならPythonでDataFrameに読み込んで処理しようかと考えていたので、大変助かりました。実際は信号カラムの個数はもっと多いので面倒ですが、今回のコードを参考にアレンジしてみます。

どうも、ありがとうございました。

[neko_the_shadow]

一応、サンプルデータを使って、「得たい結果」になっていることは確認したのですが、実データには何かしらのエッジケースがあるのかもしれません。メンテナンス性やテストのしやすさなどを考えると、複雑なSQLで処理するよりも、何らかのプログラミング言語で処理されたほうがよいかもしれません。

[hideki.]

コメントありがとうございます。
SQLに不慣れなため、SQLでやるか、一般的なプログラミング言語でやるかの見極めが自分でできません。今回、経験豊富な方の意見を頂けたので、Pythonで組む方法と両方を検討してみます。

[hideki.]

最初のコメントで「最初と最後の行以外は正しく処理されました」と書きましたが、私の勘違いで、元テーブルの最初のレコードの信号が１のケース、及び最終レコードの信号が１のケースを含め、正しい結果が得られました。お詫びして訂正いたします。また、信号カラム数を増やす場合の修正箇所も比較的分かりやすいので、Pythonで組まず、SQLで進めたいと思います（SQLコードの理解はまだ完全ではありませんが）。