mysql - 在一个时间段内进行计数，一组较小的时间段

使用MySQL，我希望能够确定某个代码在过去30分钟内的任何5分钟内是否有4个条目。

    name     stamp                 code
    a        2013-02-03 13:12:01   red
    b        2013-02-03 13:23:01   red
    c        2013-02-03 13:23:11   red
    d        2013-02-03 13:24:01   green
    e        2013-02-03 13:24:05   red
    f        2013-02-03 13:24:21   red
    g        2013-02-03 13:25:01   red
    h        2013-02-03 13:48:01   red

因此，在2013-02-03 13:50:00，我运行了一个查询，它返回了过去30分钟内条目的＃个，这些条目在5分钟内有一组4个或更多条目和第一个匹配条目的标记，但仅用于红色代码条目。

我想要使​​用此示例表的结果将是：5，2013-02-03 13:23:01

在单个查询中可能吗？谢谢！

编辑：看到此查询的任何问题？

    SELECT COUNT(*), rolling.stamp
    FROM mytable thisrow JOIN mytable rolling
       ON (thisrow.stamp BETWEEN rolling.stamp AND
              rolling.stamp + INTERVAL 5 MINUTE)
    WHERE rolling.stamp >= DATE_SUB(NOW(), INTERVAL 30 MINUTE)
      AND code = 'red'
    GROUP BY 2 HAVING COUNT(*) >= 4

当对照上表运行时，我得到了所需的“ 5，2013-02-03 13:23:01”。

附言在此示例中，我将now（）替换为“ 2013-02-03 13:50:00”

最佳答案

是的，可以通过GROUP BY和HAVING使用一些UNIX_TIMESTAMP舍入（从stamp % 300减去stamp）将其分组为300秒（5分钟）的块。

SELECT COUNT(*), MIN(stamp)
FROM mytable
WHERE stamp >= NOW() - INTERVAL 30 minute
GROUP BY
    FROM_UNIXTIME(
       UNIX_TIMESTAMP(stamp) -
       MOD(UNIX_TIMESTAMP(stamp),300)
    )
HAVING COUNT(*) >= 4

假设您是指时钟块而不是滚动块，即您要从00:00:00到00:04:59等，而不是从13:23:01到13:28:00。这会导致“ 30分钟前”规则出现一些问题，因此您可能需要仔细考虑一下。

编辑：很高兴看到您坚持要使其成为滚动块...让我们添加一些美味的性能出色的连接。

SELECT COUNT(*), rolling.stamp
FROM mytable thisrow JOIN mytable rolling
   ON (thisrow.stamp BETWEEN rolling.stamp AND
          rolling.stamp + INTERVAL 5 MINUTE)
WHERE rolling.stamp >= NOW() - INTERVAL 30 minute
GROUP BY 2 HAVING COUNT(*) >= 4;

如果您在stamp上建立索引，它的性能会更好，但这实际上是一种交叉连接，具有可怕的性能，您正在筛选n²行，其中n是行数。不要在大桌子上运行它。

关于mysql - 在一个时间段内进行计数，一组较小的时间段，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/18703338/