提问人：Mirs405 提问时间：6/20/2021 最后编辑：StrawberryMirs405 更新时间：6/21/2021 访问量：219
MySQL查询，根据日期查找最长的值序列
MySQL query to find the longest sequence of value based on date
问：
我想根据日期列找到给定值的最长序列，例如给定的表格：
+-------+-------------------+
|value |timestamp |
+-------+-------------------+
|1 |2021-02-20 13:31:21|
|0 |2021-02-20 13:31:58|
|1 |2021-02-20 13:32:00|
|1 |2021-02-20 13:33:24|
|1 |2021-02-20 13:34:12|
|0 |2021-02-20 13:36:51|
对于值“1”，最长的序列长度为 2 分 12 秒，这如何实现？
希望有人能帮忙！谢谢！
答：
1赞
Gordon Linoff
6/21/2021
#1
您可以通过累计计算值的数量来分配组。然后，只需聚合即可查看所有组：0
select min(timestamp), max(timestamp)
from (select t.*,
sum(value = 0) over (order by timestamp) as grp
from t
) t
where value = 1
group by grp;
要计算差值并取最长的周期：
select min(timestamp), max(timestamp),
second_to_time(to_seconds(max(timestamp)) - to_seconds(min(timetamp)))
from (select t.*,
sum(value = 0) over (order by timestamp) as grp
from t
) t
where value = 1
group by grp
order by to_seconds(max(timestamp)) - to_seconds(min(timetamp)) desc
limit 1;
评论