查找满足条件的范围

5kgi1eie 于 2021-06-20 发布在 Mysql

关注(0)|答案(3)|浏览(305)

我有一个mysql表，由一个传感器的浮点值和读取数据的日期时间戳组成。读数每隔1分钟存储一次（有些可能丢失）。
我正在尝试设计一个查询，它将为我提供一个事件列表（浮点值大于某个阈值的连续范围），其中包括该范围内第一条超过阈值的记录的时间戳，以及该值回落到阈值以下的结束时间戳。

ID      Value   Timestamp
2172846 1.0 2018-06-29 17:28:00
2172853 1.1 2018-06-29 17:29:00
2172860 1.1 2018-06-29 17:31:00
2172867 1.3 2018-06-29 17:32:00
2172874 1.3 2018-06-29 17:33:00
2172881 1.5 2018-06-29 17:34:00
2172888 1.4 2018-06-29 17:35:00
2172895 1.3 2018-06-29 17:36:00
2172902 1.2 2018-06-29 17:37:00
2172909 1.1 2018-06-29 17:38:00
2172916 1.0 2018-06-29 17:39:00
2172923 1.0 2018-06-29 17:40:00
2172930 1.0 2018-06-29 17:41:00
2172937 1.0 2018-06-29 17:42:00
2172944 1.0 2018-06-29 17:43:00
2172951 1.7 2018-06-29 17:44:00
2172958 2.0 2018-06-29 17:45:00
2172965 1.8 2018-06-29 17:46:00
2172972 1.3 2018-06-29 17:47:00
2172979 1.0 2018-06-29 17:48:00
2172986 1.0 2018-06-29 17:49:00
2172993 1.0 2018-06-29 17:50:00
2173000 1.0 2018-06-29 17:51:00
2173007 1.0 2018-06-29 17:52:00
2173014 1.0 2018-06-29 17:53:00

我已经做了一些初步的研究，但还没有深入。
阈值大于1的示例数据集的预期输出如下所示。

start_timestamp      end_timestamp
2018-06-29 17:29:00  2018-06-29 17:39:00
2018-06-29 17:44:00  2018-06-29 17:48:00

sql mysql time-series

来源：https://stackoverflow.com/questions/51867207/finding-ranges-where-condition-is-met

3条答案

按热度按时间

mbskvtky1#

在mysql中，可以使用变量：

select min(timestamp), max(timestamp)
from (select t.*,
             (@grp := if(value > 1.0, @grp, @grp + 1)) as grp
      from (select t.* from t order by timestamp) t cross join
           (select @grp := 0) params
     ) t
where value > 1.0
group by grp;

赞(0）回复(0）举报 2021-06-20

kzmpq1sx2#

如果您的mysql支持window函数，那么这是一个缺口和孤岛问题
您可以尝试此查询。

SELECT MIN(`Timestamp`)start_timestamp,MAX(`Timestamp`)end_timestamp
FROM (
  select *,MIN(`Timestamp`) over(order by `Timestamp`) mindt,
   ROW_NUMBER() over(order by `Timestamp`) rn
  from T
  where value > 1.0
)t1
group by (TIMESTAMPDIFF(MINUTE,mindt,`Timestamp`)+1  - rn)

sqlfiddle公司

赞(0）回复(0）举报 2021-06-20

zbq4xfa03#

这里有一个主意。它返回的范围与你的非常微妙的不同，但也许你能想出如何调整它。。。

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY 
,value DECIMAL(3,1)
,dt DATETIME
);

INSERT INTO my_table VALUES
(2846, 1.0, '2018-06-29 17:28:00'),
(2853, 1.1, '2018-06-29 17:29:00'),
(2860, 1.1, '2018-06-29 17:31:00'),
(2867, 1.3, '2018-06-29 17:32:00'),
(2874, 1.3, '2018-06-29 17:33:00'),
(2881, 1.5, '2018-06-29 17:34:00'),
(2888, 1.4, '2018-06-29 17:35:00'),
(2895, 1.3, '2018-06-29 17:36:00'),
(2902, 1.2, '2018-06-29 17:37:00'),
(2909, 1.1, '2018-06-29 17:38:00'),
(2916, 1.0, '2018-06-29 17:39:00'),
(2923, 1.0, '2018-06-29 17:40:00'),
(2930, 1.0, '2018-06-29 17:41:00'),
(2937, 1.0, '2018-06-29 17:42:00'),
(2944, 1.0, '2018-06-29 17:43:00'),
(2951, 1.7, '2018-06-29 17:44:00'),
(2958, 2.0, '2018-06-29 17:45:00'),
(2965, 1.8, '2018-06-29 17:46:00'),
(2972, 1.3, '2018-06-29 17:47:00'),
(2979, 1.0, '2018-06-29 17:48:00'),
(2986, 1.0, '2018-06-29 17:49:00'),
(2993, 1.0, '2018-06-29 17:50:00'),
(3000, 1.0, '2018-06-29 17:51:00'),
(3007, 1.0, '2018-06-29 17:52:00'),
(3014, 1.0, '2018-06-29 17:53:00');

SELECT MIN(dt) dt_start
     , MAX(dt) dt_end 
  FROM 
     ( SELECT x.*
            , CASE WHEN @prev=(value<=1) THEN @i:=@i ELSE @i:=@i+1 END i
            , @prev:=value<=1 prev 
         FROM my_table x
            , (SELECT @prev:=null, @i:=0) vars 
        ORDER 
           BY id
     ) x 
 WHERE prev=0 
 GROUP   
    BY i;
+---------------------+---------------------+
| dt_start            | dt_end              |
+---------------------+---------------------+
| 2018-06-29 17:29:00 | 2018-06-29 17:38:00 |
| 2018-06-29 17:44:00 | 2018-06-29 17:47:00 |
+---------------------+---------------------+

赞(0）回复(0）举报 2021-06-20

我来回答

查找满足条件的范围

3条答案

相关问题

热门标签

最新问答