從一系列值中獲取最新的值變化

我正在尋找一個 SQL 查詢，它返回感測器數據時間序列中的最新更改（日期）。

基本上我需要知道感測器id==i從上一次連續第一次發生值變化的時間。

該值每天唯一，主鍵為 (sensor_id, date)。

請參閱SQLite fiddle來玩弄它。

數據範例 #1，預期回報[(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

數據範例 #2，預期回報[(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

我想為此使用 SQLite，並且我只喜歡一個可以獲取數據的查詢。

不確定這是否是最好的解決方案，但這確實有效。唯一的要求是每個感測器至少有 2 個值。

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

引用自：https://dba.stackexchange.com/questions/186912