Comparing previous/next record to calculate an average speed

Question

I have a simple table ina MySQL db recording the speed in m/s of a production machine every n seconds.

ID    SPEED    TIME
----------------------------------
1     2.5      2017-01-27 08:23:03
2     1.9      2017-01-27 08:22:02
3     2.0      2017-01-27 08:21:02
4     2.1      2017-01-27 08:20:01
5     2.3      2017-01-27 08:19:03

ID is an auto increment field.

Due to some lacks in signal transmission, the time interval between two rows is not always the same. What I would do is:

1. Calculate the difference of time between two rows (assuming that in that timeframe speed is constant);
2. Multiply the calculated timeframe for the speed (so I get a distance);
3. Sum the results (I get the total distance);
4. Calculate the difference in seconds between the first and last records selected (I get the total time);
5. Divide total distance for total time getting my average speed.

My problem is to understand how to get previous and next record and how to do that iteratively.

This is now in MySQL, but will be moved to Postgres soon.

Answer 1

This covers the first point of your question. Notice I've ordered by TIME1 desc due you need to emulate LAG() function.

Notice also I've used TIME1 instead of TIME as a field name.

select ID, SPEED, t1, t2, TIME_TO_SEC(TIMEDIFF(t2, t1)) T_DIF, 
       (TIME_TO_SEC(TIMEDIFF(t2, t1)) * SPEED) AS DIST
from (
        select ID, SPEED, @t1 as t2, @t1 := TIME1 as t1
        from
            (select @t1 := MAX(TIME1) from ss) x,
            (select ID, SPEED, TIME1 from ss order by TIME1 desc) y
     ) z
;

This sentence returns next values:

+----+-------+---------------------+---------------------+-------+-------+
| ID | SPEED |          t1         |          t2         | T_DIF | DIST  |
+----+-------+---------------------+---------------------+-------+-------+
| 1  | 2,5   | 2017-01-27 08:23:03 | 2017-01-27 08:23:03 | 0     | 0,0   |
+----+-------+---------------------+---------------------+-------+-------+
| 2  | 1,9   | 2017-01-27 08:22:02 | 2017-01-27 08:23:03 | 61    | 115,9 |
+----+-------+---------------------+---------------------+-------+-------+
| 3  | 2,0   | 2017-01-27 08:21:02 | 2017-01-27 08:22:02 | 60    | 120,0 |
+----+-------+---------------------+---------------------+-------+-------+
| 4  | 2,1   | 2017-01-27 08:20:01 | 2017-01-27 08:21:02 | 61    | 128,1 |
+----+-------+---------------------+---------------------+-------+-------+
| 5  | 2,3   | 2017-01-27 08:19:03 | 2017-01-27 08:20:01 | 58    | 133,4 |
+----+-------+---------------------+---------------------+-------+-------+

And using this values you can calculate average speed:

select TIME_TO_SEC(TIMEDIFF(MAX(t2), MIN(t1))) DIF_T, 
       SUM((TIME_TO_SEC(TIMEDIFF(t2, t1)) * SPEED)) TOTAL_DIST,
       (SUM((TIME_TO_SEC(TIMEDIFF(t2, t1)) * SPEED)) / TIME_TO_SEC(TIMEDIFF(MAX(t2), MIN(t1)))) AVG_SPEED
from (
        select ID, SPEED, @t1 as t2, @t1 := TIME1 as t1
        from
            (select @t1 := MAX(TIME1) from ss) x,
            (select ID, SPEED, TIME1 from ss order by TIME1 desc) y
     ) z
;

+-------+------------+-----------+
| DIF_T | TOTAL_DIST | AVG_SPEED |
+-------+------------+-----------+
| 240   | 497,4      | 2,07250   |
+-------+------------+-----------+

Check it here: http://rextester.com/YYYHE22690

Answer 2

Getting the previous row is very easy in Postgres:

select id, 
       speed, 
       "time", 
       extract(epoch from lag("time") over (order by "time") - "time") as diff_seconds,
from the_table
order by "time";

The whole calculation would be:

select extract(epoch from max("time") - min("time")) as total_seconds, 
       sum(diff_distance) / extract(epoch from max("time") - min("time")) as avg_speed
from (
  select id, 
         speed, 
         "time", 
         extract(epoch from lag("time") over (order by "time") - "time") as diff_seconds,
         speed * extract(epoch from lag("time") over (order by "time") - "time") as diff_distance
  from the_table
  order by "time"
) t;

Subtracting a timestamp from a timestamp yields an interval. Using extract(epoch ...) converts the interval to the total number of seconds in that interval.

The column diff_distance isn't really necessary, I just added that for debugging purposes.

This however is currently not possible in MySQL.