SQLで抽出した行のひとつ前の行を参照する方法

Question

前提・実現したいこと

以下のようなテーブルがあります。
ptimeカラムはtimestamp型でソートされてます。
（実際は日付時刻のTimestampでユニークです）

ptime	col_a	col_b
12:30	-1	0
12:42	1	0
12:44	-1	1
12:50	0	1
12:52	0	1
12:55	1	0
13:00	-1	1

このテーブルからcol_aが-1、かつcol_bが1である行を抽出し、その上の行のptimeカラムを参照して、以下のようなテーブルを作成するSQLを作りたいです。

start_tm	end_tm
12:42	12:44
12:55	13:00

「col_aが-1、かつcol_bが1である行を抽出」は単純なSELECT文で可能ですが、本件の場合はまったく、方法が分かりません。

環境：PostgreSQL 10

ご教授頂きたく。よろしくお願いします。

Comments

[yambejp]

Postgreのタグをつけた方がよいですよ
ptimeはユニークが保証されているのでしょうか？

[hideki.]

アドバイスありがとうございます。
タグ付けしてptimeカラムがユニークであることを追記しました。

Answer 1

普通にかくと

select (select max(ptime) from tbl
where ptime<t1.ptime) as start_time,
ptime as end_time,col_a,col_b
 from tbl as t1;

しぼりこんでこう？

select (select max(ptime) from tbl
where ptime<t1.ptime) as start_time,
ptime as end_time
 from tbl as t1 
where (col_a,col_b) in (select -1,1)

Comments

[hideki.]

超高速の回答ありがとうございます。
ご提示頂いたSQLで、欲しい結果が得られました。

但し、内容は理解できていません。
上のコードはstart_tmとend_tmのテーブル作成、下のコードはそこからcol_a=-1、col_b=1のレコードを抽出ですね。
上のコードのmax()、as t1や下のコードのin(select )のあたりの使い方がキーポイントと推測しますが、参考となるサイト紹介か、解説を頂けると幸いです（うまい検索ワードが浮かびません）。
よろしくお願いします。

[yambejp]

基本は
select * from tbl as t1 where col_a=-1 and col_b=1
inは見た目を変えただけでやってることは同じです
a=1 and b=2 and c=3→(a,b,c) in (select 1,2,3) みたいな感じ

select * from tbl as t1 に対して
t1のptimeより小さくて、最大のptimeが１個前のデータですから
maxをつかってstart_timeを抽出しています

[hideki.]

解説ありがとうございます。
in (select 1,2,3) は簡潔に書けて便利ですね。
2番目のやり方も勉強になりました。

Answer 2

やりかたはいろいろありそうですが、個人的にはウィンドウ関数のLAGを使うとシンプルになるかと思います。

SELECT start_tm, end_tm
FROM (
  SELECT LAG(ptime) OVER (ORDER BY ptime) AS start_tm, ptime AS end_tm, col_a, col_b
  FROM T
) U
WHERE col_a = -1 AND col_b = 1

Comments

[hideki.]

回答ありがとうございます。
neko_the_shaadow様のSQLでも欲しい結果が得られました。

window関数を使って何とかできないか、考えましたが、自分の力ではできずにあきらめていました。参考にさせて頂きます。