sql - 请教一个数据查询问题（连续一段时间大于某个值），希望大家能够提供方法

问题描述

问 题

数据表中有时间和数据值两列，
现在需要查询连续大于等于3分钟大于某个值（从大于某个值开始一直到小于该值结束）的数据，
并计算出连续时间，和该段时间的平均值。如何通过SQL语句实现该要求。

解决方案

简单做了个测试，希望对你有帮助。

创建表和测试数据

CREATE TABLE t_log(f_time DATE, f_value NUMBER);
insert into T_LOG (F_TIME, F_VALUE)
values (to_date('24-02-2017 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 100);

insert into T_LOG (F_TIME, F_VALUE)
values (to_date('24-02-2017 12:00:01', 'dd-mm-yyyy hh24:mi:ss'), 101);

insert into T_LOG (F_TIME, F_VALUE)
values (to_date('24-02-2017 12:00:02', 'dd-mm-yyyy hh24:mi:ss'), 102);

insert into T_LOG (F_TIME, F_VALUE)
values (to_date('24-02-2017 12:00:03', 'dd-mm-yyyy hh24:mi:ss'), 103);

insert into T_LOG (F_TIME, F_VALUE)
values (to_date('24-02-2017 12:00:04', 'dd-mm-yyyy hh24:mi:ss'), 95);

insert into T_LOG (F_TIME, F_VALUE)
values (to_date('24-02-2017 13:00:00', 'dd-mm-yyyy hh24:mi:ss'), 108);

insert into T_LOG (F_TIME, F_VALUE)
values (to_date('24-02-2017 13:01:00', 'dd-mm-yyyy hh24:mi:ss'), 105);

insert into T_LOG (F_TIME, F_VALUE)
values (to_date('24-02-2017 13:03:00', 'dd-mm-yyyy hh24:mi:ss'), 99);

insert into T_LOG (F_TIME, F_VALUE)
values (to_date('24-02-2017 13:05:00', 'dd-mm-yyyy hh24:mi:ss'), 108);

查询语句，假设时间字段没有重复值结果才会正确：
大概分三个步骤：

1. 先按照时间字段排序，计算出每个连续大于等于目标值分组的第一行记录（判断标准是当前行大于等于目标值，同时下一行小于目标值，下面sql语句t表内容）

2. 计算上面分组的日期范围计算出来（下面sql语句中t2的内容），并过滤连续时间超过3分钟，形成表t3

3. 然后将数据表和t3表关联，就可以得到连续大于目标值的行分组。

WITH t2 AS (
  SELECT f_group1, LEAD(f_group1, 1, to_date('9999-12-31', 'yyyy-mm-dd')) OVER (ORDER BY f_time) AS f_group2
  FROM (
    SELECT f_time, f_value, CASE WHEN f_value >= 100 AND LAG(f_value, 1, 0) OVER(ORDER BY f_time) < 100 THEN f_time ELSE NULL END AS f_group1
    FROM t_log
    ORDER BY f_time
  ) t
  WHERE f_group1 IS NOT NULL
), t3 as (
  select * from t2 where (f_group2 - f_group1) > = 180
)
SELECT 
  t3.f_group1,
  (MAX(t_log.f_time) - MIN(t_log.f_time))*86400, AVG(t_log.f_value)
FROM t_log INNER JOIN t3 ON t_log.f_time >= t3.f_group1 AND t_log.f_time < t3.f_group2
WHERE f_value >= 100
GROUP BY t3.f_group1

这篇关于sql - 请教一个数据查询问题（连续一段时间大于某个值），希望大家能够提供方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！