求和多行日期差异Mysql [英] Sum Multiple Row Date Difference Mysql

问题描述

表car_log

 Speed  LogDate
 5      2013-04-30 10:10:09 ->row1
 6      2013-04-30 10:12:15 ->row2
 4      2013-04-30 10:13:44 ->row3
 17     2013-04-30 10:15:32 ->row4
 22     2013-04-30 10:18:19 ->row5
 3      2013-04-30 10:22:33 ->row6
 4      2013-04-30 10:24:14 ->row7
 15     2013-04-30 10:26:59 ->row8
 2      2013-04-30 10:29:19 ->row9

我想知道汽车在10秒钟内达到多快的速度. 在我看来，我将计算第1行-第4行之间的LogDate差异(因为第4行与第3行之间的10:14:44 =>，速度为4)+(总和)第6行-第8行之间的LogDate差异.我怀疑是对还是不对. 我如何在mysql查询中计算它.谢谢.

I want to know how long the car get speed under 10. In my mind, i will count the LogDate difference between row 1 - row4 (because in 10:14:44 => between row4 and row3, the speed is 4) + (sum) LogDate difference between row6 - row8. I am doubt if it right or no. How can i count it in mysql queries. Thank you.

推荐答案

对于每一行，请找到具有较高(较晚)LogDate的第一行.如果该行的速度小于10，则计算该行的日期与下一行的日期之间的日期差，否则输入0.

For every row, find a first row with higher (later) LogDate. If the speed in this row is less than 10, count date difference between this row's date and next row's date, else put 0.

将给出以这种方式计数的值的列表的查询应如下所示:

A query that would give a list of the values counted this way should look like:

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

现在只需要总结一下即可:

Now its just a matter of summing it up:

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

评论添加CarId后更新:

Update after comment about adding CarId:

当您有多于1辆汽车时，您需要在相关子查询中添加一个WHERE条件(我们希望该确切汽车的下一个日志，而不仅仅是任何下一个日志)，并按CarId将整个行集分组，可能将上述CarId添加到选择也显示它.

When you have more than 1 car you need to add one more WHERE condition inside dependent subquery (we want next log for that exact car, not just any next log) and group whole rowset by CarId, possibly adding said CarId to the select to show it too.

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 中查看示例.

这篇关于求和多行日期差异Mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！