MYSQL グループごとにflg=1が連続しているレコードを抽出

Question

バージョン：mysql 5.1.73

グループ毎にflg=1が連続した場合のみ、該当のレコードを抽出したいのですが、
SQLがうまく思い浮かばず、ご教授いただけないでしょうか。

イメージとして、テーブルを使って説明します。

・テーブルA

ID	部署CD	日付	flg
1	A001	20210901	1
2	A001	20210903	0
3	A001	20210905	1
4	A001	20210909	1
5	A002	20210902	0
6	A002	20210905	0
7	A002	20210908	1
8	A002	20210909	1
9	A003	20210902	0

上記テーブルの場合だと、下記のような結果を出力できればと思います。

ID	部署CD	日付	flg
3	A001	20210905	1
4	A001	20210909	1
7	A002	20210908	1
8	A002	20210909	1

mysqlでなければ、row_number()を利用して出力できるようなのですが、
row_number()が利用することができず、詰まっています。
SQLだけでもありがたいのですが、SQLについてあまり詳しくなく、
後学のため作成いただいたSQLの記述内容や参考になるサイトを教えていただけると
非常にありがたいです。

Comments

[takanaweb5]

MYSQLのバージョンを提示願います。

[yambejp]

IDは順番が担保されていれば連続していなくてもいいのですよね？

[antaka]

>takanaweb5さん
ありがとうございます。バージョン記載しました。

>yambejpさん
はい、IDはとくに連続している必要はありません。
IDをつけてしまいましたが、今回はグループごとにflg1が連続で出ているものを抽出できれば問題ないです。

Answer 1

考え方としてはこんな感じ

create table tbl(ID int primary key,cd varchar(10),d date,flg tinyint);
insert into tbl values
(1,'A001','2021-09-01',1),
(2,'A001','2021-09-03',0),
(3,'A001','2021-09-05',1),
(4,'A001','2021-09-09',1),
(5,'A002','2021-09-02',0),
(6,'A002','2021-09-05',0),
(7,'A002','2021-09-08',1),
(8,'A002','2021-09-09',1),
(9,'A003','2021-09-02',0);


select *
,(select flg from tbl where id=(select max(id) from tbl where id<t1.id and cd=t1.cd)) as prev
,(select flg from tbl where id=(select min(id) from tbl where id>t1.id and cd=t1.cd)) as next
from tbl as t1

したがって絞り込むと

select *
from tbl as t1
where flg=1 and (
(select flg from tbl where id=(select max(id) from tbl where id<t1.id and cd=t1.cd)) =1
or (select flg from tbl where id=(select min(id) from tbl where id>t1.id and cd=t1.cd))=1
)

Comments

[antaka]

回答ありがとうございます。やりたいことが実現できました。
mysqlの処理の流れがどうなっているか初めて知りました。
1レコードずつ取得されていて、t1のレコード取得毎に比較が走ってるんですね。

また、処理内容、下記認識であっていますでしょうか。
処理概要
・mysqlの処理は、1行づつ取得されている
・処理中にt1 が取得したレコードと同じCD内のidでt1の次に大きいidのflgを取得 ＝ prev ＝1つ前のflgを取得
・処理中にt1 が取得したレコードと同じCD内のidでt1の次に小さいidのflgを取得 ＝ next ＝1つ後のflgを取得
・t1のflgが1の状態で、prev、nextどちらかがflg1であれば、flg=1が連続していることになるため、対象カラムとなる