在SQL选择查询中，删除前0个值和后0个值，但不删除中间的值

debugcn 发表于 Dev

织物

这是我的问题陈述。我从传感器以这种格式获取了一些数据：

ts                 |i|p  |idx
2019-10-28 06:00:01|0|  0|75522
2019-10-28 06:10:00|0|  0|75522
2019-10-28 06:20:00|0|  0|75522
2019-10-28 06:30:00|0|  0|75522
2019-10-28 06:40:00|0|  0|75522
2019-10-28 06:50:00|0|  0|75522
2019-10-28 07:00:00|0|  0|75522
2019-10-28 07:10:00|0|103|75526
2019-10-28 07:20:00|0|114|75535
2019-10-28 07:30:00|1|141|75550
2019-10-28 07:40:00|1|203|75575
2019-10-28 07:50:00|1|203|75575
2019-10-28 08:00:00|1|203|75575
...
2019-10-28 15:30:00|1|144|79397
2019-10-28 15:40:00|1|127|79414
2019-10-28 15:50:00|0|113|79427
2019-10-28 16:00:00|0|106|79437
2019-10-28 16:10:00|0| 99|79443
2019-10-28 16:20:00|0| 96|79445
2019-10-28 16:30:01|0| 96|79446
2019-10-28 16:40:00|0|  0|79446
2019-10-28 16:50:00|0|  0|79446
2019-10-28 17:00:00|0|  0|79446
2019-10-28 17:10:00|0|  0|79446

对于给定的一天，我想提取这样的值，删除仅在一天的开始和结束时idx并未更改BUT的数据：

2019-10-28 07:00:00|0|0|75522   -- Remove all unchanged values before
2019-10-28 07:10:00|0|103|75526
2019-10-28 07:20:00|0|114|75535
2019-10-28 07:30:00|1|141|75550
2019-10-28 07:40:00|1|203|75575 
2019-10-28 07:50:00|1|203|75575 -- Keep this
2019-10-28 08:00:00|1|203|75575 -- Keep this
...
2019-10-28 15:30:00|1|144|79397
2019-10-28 15:40:00|1|127|79414
2019-10-28 15:50:00|0|113|79427
2019-10-28 16:00:00|0|106|79437
2019-10-28 16:10:00|0|99|79443
2019-10-28 16:20:00|0|96|79445
2019-10-28 16:30:01|0|96|79446  -- Remove all unchanged values after

我尝试使用LAG来计算第1行和第1行之间的idx增量。

SELECT ts, i, p, idx, idx - LAG (idx, 1, idx) OVER (ORDER BY ts) 
FROM my_table 
WHERE DATE(ts) = '2019-10-28'
ORDER BY ts ASC

结果令人鼓舞：

2019-10-28 06:00:01|0|0|75522|0   -- To be removed 
2019-10-28 06:10:00|0|0|75522|0   -- To be removed 
2019-10-28 06:20:00|0|0|75522|0   -- To be removed 
2019-10-28 06:30:00|0|0|75522|0   -- To be removed 
2019-10-28 06:40:00|0|0|75522|0   -- To be removed 
2019-10-28 06:50:00|0|0|75522|0   -- To be removed 
2019-10-28 07:00:00|0|0|75522|0   -- Keep this
2019-10-28 07:10:00|0|103|75526|4
2019-10-28 07:20:00|0|114|75535|9
2019-10-28 07:30:00|1|141|75550|15
2019-10-28 07:40:00|1|203|75575|15 
2019-10-28 07:50:00|1|203|75575|0 -- Keep this
2019-10-28 08:00:00|1|203|75575|0 -- Keep this
...
2019-10-28 15:30:00|1|144|79397|20
2019-10-28 15:40:00|1|127|79414|17
2019-10-28 15:50:00|0|113|79427|13
2019-10-28 16:00:00|0|106|79437|10
2019-10-28 16:10:00|0|99|79443|6
2019-10-28 16:20:00|0|96|79445|2
2019-10-28 16:30:01|0|96|79446|1
2019-10-28 16:40:00|0|0|79446|0   -- Keep this
2019-10-28 16:50:00|0|0|79446|0   -- To be removed 
2019-10-28 17:00:00|0|0|79446|0   -- To be removed 
2019-10-28 17:10:00|0|0|79446|0   -- To be removed

现在，如何调整查询以删除所有前0个和所有后0个，但保留最后一个前0个和第一个最后0个（您跟着我：-！），并且不要触摸中间的0个值？

在我的Python代码中进行后期处理是否更好？

编辑19年10月31日：idx列包含一个始终增加的数据，除非更改了源读取。就我而言，我从消耗量计中获取数据。但是电力公司决定更换电表...现在从0重新开始。因此，最好从时间戳分析日期+从当天的第一个/最后一个记录更改值！

尼基塔

我认为您可以从初始数据中做到这一点：

SELECT * FROM
    (
    SELECT T1.*, 
        MIN(TS) OVER (PARTITION BY IDX) AS MIN_TS,
        MAX(TS) OVER (PARTITION BY IDX) AS MAX_TS,
        MIN(TS) OVER () AS MIN_GLOBAL_TS,
        MAX(TS) OVER () AS MAX_GLOBAL_TS
    FROM TABLENAME T1
    ) T2
WHERE ((TS = MIN_TS OR TS = MAX_TS) 
    and TS !=  MIN_GLOBAL_TS 
    and TS !=  MAX_GLOBAL_TS)
    or MIN_TS = MAX_TS

在这里，您可以找到任何给定idx的第一个和最后一个时间戳，然后选择那些字符串作为第一个或最后一个时间戳，同时还要检查一天的开始和结束时间（全局最小值和最大值），并删除与它们对应的那些值。

我假设您的时间戳记是日期格式。

编辑查询，如果一天结束或一天开始没有重复的值，则返回该行。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。