sql - 甲骨文 SQL : Count a time period when the value was over the threshold

Question

我的表 MEASUREMENTS(Oracle SQL 12)有 3 列:DT - 测量时间戳，MEASUREMENT - 值，THRESHOLD - 上限。

有时测量值高于阈值。尝试计算测量值高于阈值的时间段。

DT             | MEASUREMENT | THRESHOLD          ---------------+-------------+--------------------04.08.16 01:10 | 60,5        | 70,0               04.08.16 01:20 | 65,5        | 70,0               04.08.16 01:30 | 68,1        | 70,0               04.08.16 01:40 | 70,1*       | 70,0 //period start04.08.16 01:50 | 70,1*       | 70,0               04.08.16 02:00 | 70,75*      | 70,0 //period end  04.08.16 02:10 | 53,5        | 70,0               04.08.16 02:20 | 50,15       | 70,0               04.08.16 02:30 | 52,15       | 70,0               04.08.16 02:40 | 53,15       | 70,0               

预期结果(02:00-01:40=00:20):

DURATION | START          | END---------+----------------+---------------00:20    | 04.08.16 01:40 | 04.08.16 02:00

Answer 1

您可以使用row_number() 来识别周期。这是一个缺口和孤岛问题。以下返回测量值超过阈值的每个时间段:

select max(dt) - min(dt) as duration, min(dt), max(dt)
from (select t.*,
             row_number() over (order by dt) as seqnum,
             row_number() over (partition by (case when measurement > threshold then 1 else 2 end), order by dt) as seqnum_t
      from t
     ) t
where measurement > threshold
group by (seqnum - seqnum_t)