2

I'm looking for a SQL query which returns the most recent change (date) in a time series of sensor data.

Basically I need to know when the sensor with id==i had a value change for the first time from the last streak.

The value is unique per day, the primary key is (sensor_id, date).

See SQLite fiddle to play around with it.

Data example #1, expected return [(1,'2017-01-05',0.10), (2,'2017-01-05',0.70)]:

sendor_id  date        val
---------  ----------  ----
    1      2017-01-07  0.10
    1      2017-01-06  0.10
    1      2017-01-05  0.10 <==
    1      2017-01-03  0.20
    1      2017-01-02  0.20
    1      2017-01-01  0.10
    2      2017-01-07  0.70
    2      2017-01-06  0.70
    2      2017-01-05  0.70 <==
    2      2017-01-02  0.20
    2      2017-01-01  0.20
    2      2016-12-31  0.70

Data example #2, expected return [(1,'2017-01-08',0.20), (2,'2017-01-05',0.70)]:

sendor_id  date        val
---------  ----------  ----
    1      2017-01-08  0.20 <==
    1      2017-01-07  0.10
    1      2017-01-06  0.10
    1      2017-01-05  0.10
    1      2017-01-03  0.20
    1      2017-01-02  0.20
    1      2017-01-01  0.20
    2      2017-01-08  0.70
    2      2017-01-07  0.70
    2      2017-01-06  0.70
    2      2017-01-05  0.70 <==
    2      2017-01-02  0.20
    2      2017-01-01  0.20
    2      2016-12-31  0.70

I want to use SQLite for this and I prefer just one query which can get the data.

0

Not sure if this is the best solution, but this does the job. The only requirement is that each sensor has a least 2 values.

select *
from sensor_data as t8
inner join (
  select t6.sensor_id, min(t6.date) as date
  from sensor_data t6
  inner join (
     select t4.sensor_id, max(t4.date) as before_last_date
      from sensor_data t4
      left join (
          select t2.sensor_id, t2.val as last_val, t2.date as last_date
          from sensor_data t2
          inner join (
              select sensor_id, max(date) as last_change
              from sensor_data
              where 1
              group by sensor_id
          ) as t1 
          ON t2.sensor_id = t1.sensor_id AND t2.date = t1.last_change
      ) as t3
      ON t3.sensor_id = t4.sensor_id
      WHERE t4.val != last_val
      GROUP BY t4.sensor_id
  ) as t5
  ON t5.sensor_id = t6.sensor_id
  WHERE t5.before_last_date < t6.date
  group by t6.sensor_id
) as t7
on t7.sensor_id = t8.sensor_id and t7.date = t8.date
-1

You are searching rows whose value is different from the value in the previous row, where "previous" means the row with the same sensor ID, and with the largest date that is smaller than the current row's date. Of all these changes, you want the latest one for each sensor:

SELECT sensor_id,
       MAX(date) AS date,
       value
FROM MyTable
WHERE val <> (SELECT val
              FROM MyTable AS T2
              WHERE sensor_id = MyTable.sensor_id
                AND date      < MyTable.date
              ORDER BY date DESC
              LIMIT 1)
GROUP BY sensor_id;
  • 1
    Tested on local machine but the result is incorrect. – Artashes Khachatryan Sep 26 '17 at 17:51
  • This one returns multiple rows if you add INSERT INTO sensor_data (sensor_id, date, val) VALUES (1, '2017-01-08',0.20). – ddofborg Sep 26 '17 at 20:24
  • Sorry; I overlooked "the most recent". – CL. Sep 27 '17 at 6:58
-2

Try this

; WITH cte AS(
SELECT  ROW_NUMBER() OVER (PARTITION BY sensorid ORDER BY date) AS rnm, *
FROM dbo._TestRanking
)

SELECT A.sensorid, A.Date, A.val
FROM (
SELECT c1.*, ROW_NUMBER() OVER (PARTITION BY c1.sensorid ORDER BY c1.rnm DESC) rn
FROM cte c1
LEFT JOIN cte c2 ON c2.rnm = c1.rnm - 1
AND c1.sensorid = c2.sensorid
WHERE  c1.val <> c2.val
) A
WHERE A.rn = 1
  • I don't think this works on SQLite. – ddofborg Sep 26 '17 at 20:12
  • You think, or you have tested and it doesn't work? Why it should not work? – Artashes Khachatryan Sep 27 '17 at 7:49
  • 1
    I tried, SQLite has no ROW_NUMBER(), OVER or PARTITION BY support. – ddofborg Sep 27 '17 at 11:58
  • 1
    SQLite does not have window functions. – ypercubeᵀᴹ Oct 5 '17 at 9:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.