mysql - SQL 查询对连续行进行分组并添加时间

Question

需要 SQL 查询 (MySQL) 方面的帮助

假设我有一个表格，其中的数据为..

该表记录了某个人在某些时间间隔(TIME 列)的纬度和经度位置，DISTANCE_TRAVELLED 列记录了从其先前记录以来走过的距离。

如果我想知道一个人有多少分钟没有移动(即 DISTANCE_TRAVEKLLED <= 0.001)我应该使用什么查询？

我们还可以按日期对数据进行分组吗？基本上我想知道这个人在某一天空闲了多少分钟。

Answer 1

您需要获取每条记录的前一个时间。我喜欢使用相关子查询来做到这一点:

select t.*,
       (select t2.time
        from table t2
        where t2.device = t.device and t2.time < t.time
        order by time desc
        limit 1
       ) as prevtime
from table t;

现在您可以获得未移动的分钟数，如下所示:

select t.*, TIMESTAMPDIFF(MINUTE, prevftime, time) as minutes
from (select t.*,
             (select t2.time
              from table t2
              where t2.device = t.device and t2.time < t.time
              order by time desc
              limit 1
             ) as prevtime
      from table t
     ) t

您请求的其余部分只需添加适当的 where 子句或 group by 子句。例如:

select device, date(time), sum(TIMESTAMPDIFF(MINUTE, prevftime, time)) as minutes
from (select t.*,
             (select t2.time
              from table t2
              where t2.device = t.device and t2.time < t.time
              order by time desc
              limit 1
             ) as prevtime
      from table t
     ) t
 where distance_travelled <= 0.001
group by device, date(time)

编辑:

为了提高性能，请在表(设备、时间)上创建索引。