需要有关SQL查询的帮助(MySQL)
假设我有一个数据为..的表。

该表记录了某个人在某些时间间隔内的纬度和经度位置(TIME列),DISTANCE_TRAVELLED列记录了从上一条记录到的距离。
如果我想知道一个人有多少分钟没有移动(即DISTANCE_TRAVEKLLED <= 0.001),我应该使用什么查询?
我们还可以按日期对数据进行分组吗?基本上,我想知道一个人在特定的一天中有多少分钟是空闲的。
发布于 2014-06-13 19:38:44
您需要获取每条记录的上一次时间。我喜欢使用相关子查询:
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)编辑:
为了提高性能,请在table(device, time)上创建索引。
https://stackoverflow.com/questions/24204293
复制相似问题