检测时间序列满足特定条件的连续项(Detect consecutive items meeting

我有每半小时超过92.000行与天气寄存器一个MySQL数据库。日| 本月| 年| 时间| 温度| ...我试图获得（在PHP）峰值温度：显示的时间与在每个月温度= <分钟（温度）3的最大数量（连续的寄存器）。

我将不胜感激任何帮助！

我的这种方法：先从时间序列的观测，并给每一个序列号。

该系列的编号是在MySQL颈部疼痛，但不管。给定一个带有TS柱（日期时间项目）以及一个临时列的表，这里的查询与序列号，让他们。

SELECT @sample:=@sample+1 AS ser, ts, temp
  FROM (
     SELECT ts,temp
       FROM t
      ORDER BY ts
    ) C,
  (SELECT @sample:=0) s

看看这个sqlfiddle： http://sqlfiddle.com/#!2/d81e2/5/0

OK，这是非常容易的。现在，让我们说，我们正在寻找的时间段在温度为25度以上。要做到这一点，我们需要那么它忽略了这些意见砍的时间序列。这是这样的：

SELECT @sample:=@sample+1 AS ser, ts, temp
  FROM (
     SELECT ts,temp
       FROM t
      WHERE NOT temp >= 25
      ORDER BY ts
    ) C,
  (SELECT @sample:=0) s

这里的sqlfiddle： http://sqlfiddle.com/#!2/d81e2/6/0

现在，下一个关键是要找到在这个序列中的时间差距。我们可以利用该技术从该SO张贴这样做。在MySQL中寻找时间序列数据差距的方法？

下一步，我们将它加入到自身。

SELECT two.ser, two.ts, two.temp, 
       TIMESTAMPDIFF(MINUTE, two.ts, one.ts) gap
  FROM (
     /* virtual table */
  ) ONE
  JOIN (
     /* same virtual table */
  ) TWO ON (TWO.ser+ 1 = ONE.ser)

此查询获取系列中的每个项目，并在其后的一个之间的时间间隔。这是从概念上做一个简单的事情，但在棘手的SQL的MySQL版本。下面是完整的查询。

SELECT two.ser, two.ts, two.temp, 
       TIMESTAMPDIFF(MINUTE, two.ts, one.ts) gap
      FROM (
 SELECT @sample:=@sample+1 AS ser, ts, temp
  FROM (
     SELECT ts,temp
       FROM t
      WHERE NOT temp >= 25
      ORDER BY ts
    ) C,
  (SELECT @sample:=0) s
      ) ONE
      JOIN (
SELECT @sample2:=@sample2+1 AS ser, ts, temp
  FROM (
     SELECT ts,temp
       FROM t
      WHERE NOT temp >= 25
      ORDER BY ts
    ) C,
  (SELECT @sample2:=0) s
      ) TWO ON (TWO.ser+ 1 = ONE.ser)

这里的sqlfiddle： http://sqlfiddle.com/#!2/d81e2/13/0注意到有些差距在持续时间30分钟。这是正常的，连续的读数。有些是60分钟。这也是正常的，因为我使用的是时间序列缺少某些条目。在此结果集的条目显示的时间和温度立即的差距了。

因此，所有剩下的就是摆脱垃圾间隙（30和60分钟），然后顺序降序存在的差距。

SELECT two.ts, two.temp, 
       TIMESTAMPDIFF(MINUTE, two.ts, one.ts) gap
      FROM (
 SELECT @sample:=@sample+1 AS ser, ts, temp
  FROM (
     SELECT ts,temp
       FROM t
      WHERE NOT temp >= 25
      ORDER BY ts
    ) C,
  (SELECT @sample:=0) s
      ) ONE
      JOIN (
SELECT @sample2:=@sample2+1 AS ser, ts, temp
  FROM (
     SELECT ts,temp
       FROM t
      WHERE NOT temp >= 25
      ORDER BY ts
    ) C,
  (SELECT @sample2:=0) s
      ) TWO ON (TWO.ser+ 1 = ONE.ser)
 WHERE TIMESTAMPDIFF(MINUTE, two.ts, one.ts)> 60
 ORDER BY TIMESTAMPDIFF(MINUTE, two.ts, one.ts) DESC

这给出了一个行的时间每个序列，其中温度是25度以上; 时间最长的第一。在结果集中显示的产品低于25最后一次温度之前上升。 SQL小提琴。 http://sqlfiddle.com/#!2/d81e2/14/0

有趣的，是吗？