从一系列值中获取最新的值更改。
从一系列值中获取最新的值更改。
提问于 2017-09-26 13:56:40
我正在寻找一个SQL查询，它返回传感器数据的时间序列中的最新更改(日期)。
基本上，我需要知道什么时候带有id==i的传感器的值第一次从上一次连续变化。
该值每天是唯一的，主键是(sensor_id，date)。
请看SQLite小提琴来玩它。
数据示例#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，我更喜欢一个可以获得数据的查询。
回答 3
Database Administration用户
回答已采纳
发布于 2018-03-26 10:31:28
不确定这是否是最好的解决方案，但这是工作。唯一的要求是每个传感器至少有两个值。
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.dateDatabase Administration用户
发布于 2017-09-26 15:11:34
您正在搜索值与上一行中的值不同的行，其中“前一”表示具有相同传感器ID的行，并且其最大日期小于当前行的日期。在所有这些更改中，您需要每个传感器的最新更改：
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;Database Administration用户
发布于 2017-09-26 17:51:20
尝尝这个
; 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页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/186912
复制相关文章
相似问题
腾讯云开发者
