mysql - 基于 session 计数器配对事件并在 mySQL 中查找时间增量

Question

我读过许多涉及在 SQL 中配对记录的 SO 问题，但没有一个使用像这样的 session 计数器。我已经简化了应该非常适用于许多问题的通用实例。

基本上我有一个 mySQL 表记录带有时间戳的单个事件和一个递增的“ session 计数器”，每个新 session 都会重置为 0，如下所示:

time  counter
t0    0
t1    1
t2    2
t3    0
t4    1
t5    0
t6    1
t7    2
t8    3
t9    4

我想获取该数据并将每个 session 的第一个和最后一个时间戳(并最终计算时间增量)配对到每个 session 的一行中。所以所需的输出将是这样的:

start  counter  end  counter
t0     0        t2   2
t3     0        t4   1
t5     0        t9   4

我读过一些使用 LEAD 和 LAG 函数的解决方案，但这些在 MySQL 中不可用，而且替代实现超出了我的理解水平。我知道我可以在 Excel/Python 中进行暴力破解，但是是否有相对简单的 mySQL 查询可以完成此操作？

Answer 1

您可以使用 User-Defined Variables 在 MySQL 中模拟 LEAD 函数

举个例子

select min(time),max(time),count(*)-1
from
(
select time,counter,
if(@prev>counter,@grp:=@grp+1,@grp:=@grp),
@grp as GRPvalue,
@prev:=counter 
from t,(select @prev:=0,@grp:=0) as t1 
order by time
) t2
GROUP BY GRPValue
order by min(time)

SQLFiddle

或者这里是另一个 SQL 查询。这里我们将组定义为当前记录之前的 counter=0 的计数(如果所有间隔都从 0 开始):

select min(time),max(time),count(*)-1
from t t1
group by 
(select count(*) from t where t.time<=t1.time and counter = 0)

SQLFiddle demo