MYSQL:查找连续计数的开始和结束时间戳 [英] MYSQL: Find Start and End Timestamp of a consecutive count

问题描述

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

I'm looking to output the start and end time of a series of positive numbers

例如；我有下表，其中有两列 Timestamp 和 Marker.Marker 由一系列 1 和 0 组成:

For example; I have the following Table that has two columns Timestamp and Marker.Marker consists of a series of 1's and 0's:

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

I wish to count each series/ run of 1's and produce the timestamp when the series starts and ends

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 出现之前开始和结束的时间.

An example output table would show the count for each section of 1's for each run of the value and the time where the value starts and ends before a 0 appears.

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 的总数以及总体值的开始和结束.不完全是我需要的.

I've started some code (created a table named tbl) which gives the overall count of the number of 1's and start and End for the overall values. Not exactly what i need.

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"的单个运行并输出每次运行的开始和结束时间戳.

I'm short on ideas. Any thoughts by anyone on how to adjust so I can identify individual runs of the value '1' and output the start and end timestamp for each run.

干杯

推荐答案

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

One way is to give a unique index of each run / series and then use GROUP BY to get starting and ending timestamps.

给你:

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`

工作小提琴:http://sqlfiddle.com/#!2/f5f19c/3

这篇关于MYSQL:查找连续计数的开始和结束时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！