mysql - 检查 MySQL 查询中的连续值

Question

我有一个像这样的 MySQL 表:

ID - Time - Value

我得到每对 ID，时间(按 ID 分组)，其中值大于某个阈值。所以基本上，我得到的每个 ID 的值至少有一次大于阈值。查询如下所示:

SELECT ID, Time FROM mydb.MYTABLE
WHERE Value>%s AND Time>=%s AND Time<=%s
GROUP BY ID

编辑:时间检查允许在我选择的时间范围内操作表中的所有数据；这与我的要求无关。

它工作得很好，但现在我想添加一些过滤:我希望它避免那些时间值大于阈值(我们称之为警报)，如果警报也没有发生，也就是之前或刚刚后。我的意思是:如果警报发生在一个单独的、孤立的时刻，而不是两个连续的时刻，我会认为这是一个误报，并避免在查询响应时返回它。

当然，我可以通过调用每个 Id 来检查这一点，但我想在单个查询中执行此操作以使其更快。我想我可以使用条件语句，但我不具备 MySQL 方面的专业知识。

有什么帮助吗？

EDIT2:阈值 = 10 的示例

ID - Time - Value
1  - 2004 -   9
1  - 2005 -  11
1  - 2006 -   8
2  - 2107 -  12
2  - 2109 -  13
3  - 3402 -  11
3  - 3403 -  12

在此示例中，只有 ID 3 应该是有效警报，因为此 ID 的 2 个连续时间值的值 > 阈值。 ID 1 有一个独立的警报，因此它应该是过滤器。 ID 2有2条告警，但不是连续的，所以也应该过滤掉。

Answer 1

像这样:

10 - is a threshold
0 - minimum of the time period
100000 - maximum of the time period


select ID, min(Time)

from
(
SELECT ID, Time,

       (select max(time) from t 
           where Time<t1.Time
           and Id=t1.Id
           and Value>10) LAG_G,
       (select max(time) from t 
           where Time<t1.Time
           and Id=t1.Id
           and Value<=10) LAG_L,
       (select min(time) from t 
           where Time>t1.Time
           and Id=t1.Id
           and Value>10) LEAD_G,
       (select min(time) from t 
           where Time>t1.Time
           and Id=t1.Id
           and Value<=10) LEAD_L

FROM t as t1
WHERE Value>10 AND Time>=0 AND Time<=100000
) t3
where ifnull(LAG_G,0)>ifnull(LAG_L,0)
      OR
      ifnull(LEAD_G,100000)<ifnull(LEAD_L,100000)

GROUP BY ID

SQLFiddle demo

此查询适用于搜索附近的记录。

如果您需要按评论中提到的时间 (+1, -1) 搜索记录，请尝试以下查询:

select ID, min(Time) from t as t1
where Value>10
      AND Time>=%s2 AND Time<=%s1
  and 
   ( 
      Exists(select 1 from t where Value>10 
                              and Id=t1.Id
                              and Time=t1.Time-1) 
      OR 

      Exists(select 1 from t where Value>10 
                              and Id=t1.Id
                              and Time=t1.Time+1) 
   )  
group by ID

SQLFiddle demo