MYSQL:查找连续计数的开始和结束时间戳

Question

我希望输出一系列正数的开始和结束时间

例如；我有下表，其中有两列时间戳和标记。标记由一系列 1 和 0 组成:

我希望计算每个系列/1 的运行并在系列开始和结束时生成时间戳

Time_stamp                 Marker

2012-01-01 00:01:00         1

2012-01-01 00:02:10         1

2012-01-01 00:03:01         1

2012-01-01 00:04:15         0

2012-01-01 00:05:12         0

2012-01-01 00:06:00         1

2012-01-01 00:07:02         1

2012-01-01 00:08:19         1

2012-01-01 00:09:11         1

2012-01-01 00:10:10         1

示例输出表将显示值的每次运行的每个 1 部分的计数，以及值在 0 出现之前开始和结束的时间。

Output Table:

Total Count       Start Timestamp            End Timestamp

   3              2012-01-01 00:01:00       2012-01-01 00:03:01 


   5              2012-01-01 00:06:00      2012-01-01 00:10:10   

我已经启动了一些代码(创建了一个名为 tbl 的表)，它给出了 1 的总数以及总体值的开始和结束。不完全是我需要的。

Select Consec,
SUM(IF(Consec = '1',1,0)) AS Total_Count,
Min(Time_stamp) AS Min_Timestamp,
Max(Time_stamp) AS Max_Timestamp
FROM(
SELECT 
  a.*, 
 @val:=0,
 @Zero:=0,
 IF (a.Marker=1,@val:=@val+1,@Zero) AS Consec
FROM tbl a
) a

我缺乏想法。任何人关于如何调整的任何想法，以便我可以识别值“1”的各个运行并输出每次运行的开始和结束时间戳。

干杯

Answer 1

一种方法是为每个运行/系列提供唯一索引，然后使用 GROUP BY 获取开始和结束时间戳。

给你:

SELECT COUNT(*), MIN(time_stamp) AS starttime, MAX(time_stamp) AS endtime
FROM (
  SELECT IF(@prev <> marker, @s:=@s+1, @s:=@s) AS `index`, 
    time_stamp, @prev:=marker marker
  FROM
    tbl, (SELECT @s:= 0, @prev:= -1) s
) tmp
WHERE marker = 1
GROUP BY `index`

工作 fiddle :http://sqlfiddle.com/#!2/f5f19c/3