python - 在 SQL select 查询中，删除第一个和最后一个 0 值，但不要删除中间的值

Question

这是我的问题陈述。我从传感器获得了一些以下格式的数据:

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 未更改但仅在当天开始和结束时更改的数据:

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 之间的 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 代码中的后处理来完成此操作是否更好？

<小时/>

编辑 2019 年 10 月 31 日:idx 列包含的数据始终在增加，除非源读取发生更改。就我而言，我从消耗表中获取数据。但是电力公司决定更换电表...现在从 0 重新开始。所以最好从时间戳+当天第一/最后记录的值变化来分析天数!

Answer 1

我认为您可以根据初始数据做到这一点:

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 的第一个和最后一个时间戳，然后选择那些时间戳是第一个或最后一个的字符串，同时还检查一天的开始和结束(全局最小值和最大值)并删除对应于的那些值他们。

我假设您的时间戳采用日期格式。

编辑查询以在当天结束或开始时没有重复值的情况下返回该行。