Find the column with unusual difference between succeeding or preceding column in mysql

Question

I have following table

id  vehicle_id  timestamp distance_meters
1   1           12:00:01  1000 
2   1           12:00:04  1000.75
3   1           15:00:06  1345.0(unusual as time and distance jumped)
4   1           15:00:09  1347

The table above is the log of the vehicle.Normally , vehicle sends the data at 3 seconds interval , but sometimes they can get offline and send the data only they are online. Only, way to find out that is find out unusual jump in distance . We can assume some normal jump as (500 meters)

What is the best way to do that?

Wouldn't jump in time be a better indication? – jarlh Jan 20 '17 at 11:14 — jarlh, Jan 20 '17 at 11:14
yea , normally it won't jump in usual time – sumit Jan 20 '17 at 11:15 — sumit, Jan 20 '17 at 11:15

score 1 · Answer 1 · edited May 23 '17 at 12:33

Usually you can use windowing function for such task - LEAD and LAG are perfect for this. However since there are no windowing functions in mysql you would have to emulate them.

You need to get your data set with row number and then join it to itself by row number with offset by 1.

It would look something like this:

SELECT
  *
FROM (SELECT
  rownr,
  vehicle_id,
  timestamp,
  distance_meters
FROM t) tcurrent
LEFT JOIN (SELECT
  rownr,
  vehicle_id,
  timestamp,
  distance_meters
FROM t) tprev
  ON tcurrent.vehicle_id = tprev.vehicle_id
  AND tprev.rownr = tcurrent.rownr - 1

If you can assume id is sequential (without gaps) per vehicle_id, then you could use it instead of rownr. Otherwise you would have to make you own rank/row number.

So you would have to combine ranking solution from this question: MySQL - Get row number on select

score 1 · Accepted Answer · answered Jan 20 '17 at 11:45

If you cannot ensure that the ids increment with no gaps, then you need another method. One method uses variables and one uses correlated subqueries.

The variables is messy, but probably the fastest method:

select t.*,
       (case when @tmp_prev_ts := @prev_ts and false then NULL     -- never happens
             when @prev_ts := timestamp and false then NULL        -- never happens
             else @tmp_prev_ts
        end) as prev_timestamp,
       (case when @tmp_prev_d := @prev_d and false then NULL       -- never happens
             when @prev_d := distance_meters and false then NULL   -- never happens
             else @tmp_prev_d
        end) as prev_distance_meters
from t cross join
     (select @prev_ts := '', @prev_d := 0) params
order by timestamp;  -- assume this is the ordering

You can then use a subquery or other logic to get the large jumps.

Find the column with unusual difference between succeeding or preceding column in mysql

2 Answers2