連続するシフトデータをカウントしたい

Question

前提・実現したいこと

mysql 5.7の環境です

staff_id	datetime	status
101	2021-01-01 10:00:00	1
101	2021-01-01 11:00:00	1
101	2021-01-01 12:00:00	0
101	2021-01-01 13:00:00	1
101	2021-01-01 14:00:00	0
101	2021-01-01 15:00:00	1
101	2021-01-01 16:00:00	1
201	2021-01-01 10:00:00	0
201	2021-01-01 11:00:00	0
201	2021-01-01 12:00:00	1
201	2021-01-01 13:00:00	1
201	2021-01-01 14:00:00	1
201	2021-01-01 15:00:00	0
201	2021-01-01 16:00:00	0

上記のようなシフトデータが存在した場合、
staff_id毎に、statusが1の『最小となる時間』と『1時間毎に連続する数』を取得するにはどのようにしたら良いでしょうか？

結果イメージ

最終的には以下のような結果を取得したいです

staff_id	min_datetime	count
101	2021-01-01 10:00:00	2
101	2021-01-01 13:00:00	1
101	2021-01-01 15:00:00	2
201	2021-01-01 12:00:00	3

row_numberやoverを使えれば実現出来そうだったのですが
mysql8の環境にすることは叶わない為、5.7系で代替となるSQLを書く事が出来ずに困っております
お力添え頂けますと幸いです

Answer 1

row_numberやoverを使えれば実現出来そうだったのですが
mysql8の環境にすることは叶わない為、
5.7系で代替となるSQLを書く事が出来ずに困っております

|row_number関数は、レコードを一意に特定できる項目の組み合わせがテーブル内に存在すれば
( 今回のケースですとstaff_id + datetime )
スカラサブクエリ や 自己結合による集計 で模倣できます。

これらの方法は、RDBMSの種別やMySQLのバージョンに依存しないテクニックになりますから
この機会に習得しておかれることをお薦めします。

ただし、どちらの方法も
演算量が多くDBへの負荷も高い処理であることも事実ですから
日付単位でデータのカウントを区切るとか
|datetimeフィールドに抽出条件を記述して範囲を限定するなどの
チューニング無しでは、実用に耐えない可能性があるでしょう。

なお、対象とするdatetimeフィールドの範囲が広く
結果セットの行数が巨大になるのでしたら、takanaweb5さんの記述を用いればいいでしょう。

SELECT staff_id
     , min( ts ) min_datetime
     , count(1) _count
FROM
(
    SELECT x.staff_id
         , x.datetime  ts
         , count(1)    i
    FROM テーブル名 x
       , テーブル名 y
    WHERE x.status = 1
      AND y.status = 1
      AND x.staff_id = y.staff_id
      AND x.datetime >= y.datetime
    GROUP BY 
       1, 2
) q
GROUP BY staff_id
       , date_sub( ts, interval i hour )
ORDER BY 1, 2
;

Answer 2

SELECT T.*
, @grp := CASE WHEN datetime = ADDTIME(@old, '01:00:00')
          THEN @grp
          ELSE @grp + 1
          END AS grp
, @old := datetime
FROM (SELECT * FROM Table1 where status = 1 ORDER BY staff_id, datetime) AS T
   , (SELECT @grp:=0, @old:=NULL) AS X  -- 変数の初期化

を実行すると

staff_id	datetime	status	grp	@old
101	2021-01-01 10:00:00	1	1	2021-01-01 10:00:00
101	2021-01-01 11:00:00	1	1	2021-01-01 11:00:00
101	2021-01-01 13:00:00	1	2	2021-01-01 13:00:00
101	2021-01-01 15:00:00	1	3	2021-01-01 15:00:00
101	2021-01-01 16:00:00	1	3	2021-01-01 16:00:00
201	2021-01-01 12:00:00	1	4	2021-01-01 12:00:00
201	2021-01-01 13:00:00	1	4	2021-01-01 13:00:00
201	2021-01-01 14:00:00	1	4	2021-01-01 14:00:00

となります

上記の結果に対して、staff_id , grp でGROUP BY して集計関数を実行すれば良いでしょう

SELECT
  staff_id 
, MIN(datetime) AS min_datetime
, COUNT(*) AS count
FROM (上記のSELECT文) AS T2
GROUP BY 
  staff_id 
, grp

http://sqlfiddle.com/#!9/2792a9/10