mysql - SQL查询获取列值的次数和持续时间达到序列中的特定值

Question

我有一个包含两列的表格ReceivedOn(Date/Time) 和Speed(int)示例数据如下所示

     ReceivedOn       |   Speed
 ----------------------------------------------
 2012-11-05 06:30:00          10
 2012-11-05 06:31:00          45
 2012-11-05 06:32:00          48
 2012-11-05 06:33:00          53
 2012-11-05 06:34:00          47
 2012-11-05 06:35:00          38
 2012-11-05 06:36:00          22
 2012-11-05 06:37:00          36
 2012-11-05 06:38:00          41
 2012-11-05 06:39:00          47
 2012-11-05 06:40:00          49
 2012-11-05 06:41:00          22
 2012-11-05 06:42:00          36

我需要在速度 > 40 时对行进行分组，以便生成的输出为

       StartTime      |   EndTime               |  Count
 --------------------------------------------------------
 2012-11-05 06:31:00     2012-11-05 06:34:00         4
 2012-11-05 06:38:00     2012-11-05 06:40:00         3

StartTime为速度首次超过40时的ReceivedOn值，EndTime值为连续记录中最后一次超过40时的ReceivedOn值，Count为连续超过40条记录的总数。

我尽了最大的努力，但无法得到它。有没有可能用sql查询得到这个？请建议。提前致谢。

Answer 1

下面是对此类问题的一般回答。 Detect consecutive items meeting particular criteria in a time series

您的具体情况似乎更容易，因为您没有时间间隔(他满怀希望地说道)。您正试图找到时间顺序中的差距。在您的情况下，间隙定义为 >= 40 的那些项目。因此，您正在寻找事件序列中值小于 40 的间隙。

这是一个查询，它提供带有行号的时间序列。

SELECT @RowA := @RowA + 1 AS ROWNUM,
       ReceivedOn, Speed
   FROM (
        SELECT ReceivedOn, Speed
          FROM obs
         WHERE NOT Speed >= 40
   ) AS A
   JOIN (SELECT @RowA := 0) AS B

现在您使用一些严肃的 SQL monkey business 将此序列自连接到自身。它是这样工作的:

SELECT B.ReceivedON + INTERVAL 1 MINUTE As StartTime,
       A.ReceivedOn - INTERVAL 1 MINUTE AS EndTime,
       -1 + TIMESTAMPDIFF(MINUTE, B.ReceivedOn, A.ReceivedOn) AS Count
  FROM (
         SELECT @RowA := @RowA + 1 AS ROWNUM,
                ReceivedOn, Speed
           FROM (
              SELECT ReceivedOn, Speed
                FROM obs
               WHERE NOT Speed >= 40
                ) AS A
           JOIN (SELECT @RowA := 0) AS B
       ) AS A
  JOIN (
          SELECT @RowB := @RowB + 1 AS ROWNUM,
                 ReceivedOn, Speed
         FROM (
              SELECT ReceivedOn, Speed
                FROM obs
               WHERE NOT Speed >= 40
              ) AS A
         JOIN (SELECT @RowB := 0) AS B
        ) AS B ON B.ROWNUM+1 = A.ROWNUM
 WHERE TIMESTAMPDIFF(MINUTE, B.ReceivedOn, A.ReceivedOn) > 1

http://sqlfiddle.com/#!2/2cb57/24/0

这看起来很复杂，但它只是第一个查询与其自身的连接 ON B.ROWNUM+1 = A.ROWNUM。这会将查询的结果集与其自身偏​​移一行对齐，因此您可以比较连续的行。

这会给出您需要的结果。请注意，如果您的第一个观察值 >= 40，则此查询将忽略第一个观察值序列。