我有一个随时间变化的压力测量表。我需要创建一个查询,创建压力测量值低于预定义值的历史时间,包括开始时间、结束时间和间隔期间的压力平均值。
| TEMPO | PRESSAO |
|:-----------------------:|:-------:|
| 1900-01-01 16:26:01.000 | 14.1 |
| 1900-01-01 16:27:05.000 | 14.3 |
| 1900-01-01 16:28:09.000 | 14.5 |
| 1900-01-01 16:29:13.000 | 14.7 |
| 1900-01-01 16:30:17.000 | 14.9 |
| 1900-01-01 16:31:21.000 | 15.1 |
| 1900-01-01 16:32:25.000 | 15.3 |
| 1900-01-01 16:33:29.000 | 15.5 |
| 1900-01-01 16:34:33.000 | 15.7 |
| 1900-01-01 16:35:37.000 | 15.9 |
| 1900-01-01 16:36:41.000 | 16.1 |
| 1900-01-01 16:37:45.000 | 16.3 |
| 1900-01-01 16:38:49.000 | 16.5 |
| 1900-01-01 16:39:53.000 | 16.7 |
| 1900-01-01 16:40:57.000 | 16.9 |
| 1900-01-01 16:42:01.000 | 17.1 |
| 1900-01-01 16:43:05.000 | 17.3 |
| 1900-01-01 16:44:09.000 | 16.1 |
| 1900-01-01 16:45:13.000 | 16.5 |
| 1900-01-01 16:46:17.000 | 16 |
| 1900-01-01 16:47:21.000 | 15.8 |
| 1900-01-01 16:48:25.000 | 15.6 |
| 1900-01-01 16:49:29.000 | 15.4 |
| 1900-01-01 16:50:33.000 | 15.3 |
| 1900-01-01 16:51:37.000 | 14.8 |
| 1900-01-01 16:52:41.000 | 15 |
| 1900-01-01 16:53:45.000 | 15.3 |
| 1900-01-01 16:54:49.000 | 15.5 |
| 1900-01-01 16:55:53.000 | 15.8 |
| 1900-01-01 16:56:57.000 | 16 |
| 1900-01-01 16:58:01.000 | 16.1 |
每当压力值下降16.0时,就会开始"事件发生"。只有当再次达到16.0时,这段时间才会停止。我不在乎大于16的值,我不想使用它们。
所以我的结果集应该是:
| OCCURRENCE | START | END | PRESSURE AVG |
|:----------:|:-------------------:|:-------------------:|:-----------------:|
| 1 | 1900-01-01 16:26:01 | 1900-01-01 16:35:37 | <avg of period 1> |
| 2 | 1900-01-01 16:47:21 | 1900-01-01 16:55:53 | <avg of period 2> |
我试图让这个查询工作,但没有成功。甚至尝试使用临时表。
如有任何帮助,我们将不胜感激。
使用累积和来定义组。在这种情况下,值为16或更大的行数之和。然后过滤和聚集:
select min(time), max(time), avg(pressure)
from (select t.*,
sum(case when pressure >= 16 then 1 else 0 end) over (order by time) as grp
from t
) t
where pressure < 16
group by grp;