对于每一行,请找到具有较高(较晚)LogDate的第一行。如果该行的速度小于10,则计算该行的日期与下一行的日期之间的日期差,否则输入0。
给出以这种方式计数的值的列表的查询应类似于:
SELECT ( SELECT IF( c1.speed <10, unix_timestamp( c2.LogDate ) - unix_timestamp( c1.logdate ) , 0 )
FROM car_log c2
WHERE c2.LogDate > c1.LogDate
LIMIT 1
) AS seconds_below_10
FROM car_log c1
现在,只需总结一下即可:
SELECT sum( seconds_below_10) FROM
( SELECT ( SELECT IF( c1.speed <10, unix_timestamp( c2.LogDate ) - unix_timestamp( c1.logdate ) , 0 )
FROM car_log c2
WHERE c2.LogDate > c1.LogDate
LIMIT 1
) AS seconds_below_10
FROM car_log c1 ) seconds_between_logs
当您有多于1辆汽车时,您需要在相关子查询中添加一个WHERE条件(我们想要该确切汽车的下一个日志,而不仅仅是任何下一个日志),并按CarId对整个行集进行分组,可能在选择中添加上述CarId以显示它也。
SELECT sbl.carId, sum( sbl.seconds_below_10 ) as `seconds_with_speed_less_than_10` FROM
( SELECT c1.carId,
( SELECT IF( c1.speed <10, unix_timestamp( c2.LogDate ) - unix_timestamp( c1.logdate ) , 0 )
FROM car_log c2
WHERE c2.LogDate > c1.LogDate AND c2.carId = c1.carId
LIMIT 1 ) AS seconds_below_10
FROM car_log c1 ) sbl
GROUP BY sbl.carId
参见Sqlfiddle的示例。