在一个时间范围内分组为 5 分钟的间隔

Question

我对想要执行的 mySQL 命令有一些困难。

 SELECT a.timestamp, name, count(b.name)
FROM time a, id b
WHERE a.user = b.user
  AND a.id = b.id
  AND b.name = 'John'
  AND a.timestamp BETWEEN '2010-11-16 10:30:00' AND '2010-11-16 11:00:00'
GROUP BY a.timestamp

这是我当前的输出语句。

 timestamp            name  count(b.name)
-------------------  ----  -------------
2010-11-16 10:32:22  John  2
2010-11-16 10:35:12  John  7
2010-11-16 10:36:34  John  1
2010-11-16 10:37:45  John  2
2010-11-16 10:48:26  John  8
2010-11-16 10:55:00  John  9
2010-11-16 10:58:08  John  2

如何将它们分组为 5 分钟间隔结果？

我希望我的输出像

timestamp            name  count(b.name)
-------------------  ----  -------------
2010-11-16 10:30:00  John  2
2010-11-16 10:35:00  John  10
2010-11-16 10:40:00  John  0
2010-11-16 10:45:00  John  8
2010-11-16 10:50:00  John  0
2010-11-16 10:55:00  John  11

原文由 sky 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

基于@boecko 对 MySQL 的回答，我使用了 CTE（通用表表达式）来加速查询执行时间：

所以这 ：

 SELECT
    `timestamp`,
    `name`,
     count(b.`name`)
FROM `time` a, `id` b
WHERE …
GROUP BY
UNIX_TIMESTAMP(`timestamp`) DIV 300, name

变成：

 WITH cte AS (
    SELECT
        `timestamp`,
        `name`,
         count(b.`name`),
         UNIX_TIMESTAMP(`timestamp`) DIV 300 AS `intervals`
    FROM `time` a, `id` b
    WHERE …
)
SELECT * FROM cte GROUP BY `intervals`

在海量数据中，速度提升10多倍！

由于 timestamp 和 time 在 MySQL 中是保留的，所以不要忘记在每个表和列名上使用 `…` ！

希望它会帮助你们中的一些人。

原文由 Meloman 发布，翻译遵循 CC BY-SA 4.0 许可协议