mysql - 获取每小时发生的事件数

Question

我有一个包含事件和 Unix 时间戳的表:

event_id | start_time | end_time
----------------------------------
1        | 1485388800 | 1485410400
2        | 1485396000 | 1485403200
3        | 1485406800 | 1485414000

我想编写一个查询，它需要一个开始时间和一个结束时间，并告诉我每小时发生了多少事件。给定开始时间 1485385200 和结束时间 1485414000，上表的结果将是:

event_count | time
------------------------
0           | 1485385200
1           | 1485388800
1           | 1485392400
2           | 1485396000
2           | 1485399600
1           | 1485403200
2           | 1485406800
1           | 1485410400
0           | 1485414000

编写此查询的最佳方式是什么？我既要生成这个范围，也要检查事件的范围，最好不要多次读取该表，因为它可能非常大。

Answer 1

如评论中所述，最好的方法是加入某种日历表。

但是，这里有一个不使用此类表格的(有点老套的)解决方案。它使用变量生成小时序列。

您唯一需要记住的是，您需要一个足够大的表来容纳您尝试创建的间隔数(即，一个表的记录数至少与间隔的小时数一样多)您选择的开始和结束时间)。在我的示例中，我使用了内置的 mysql.help_topic 表，但是如果您的 events 表本身足够大，您可以使用该表(或任何其他表) 而不是。

SET @x:=1485385200, @y:=1485414000;

SELECT COUNT(event_id) AS event_count, hours.start AS time
FROM (
    SELECT @x AS start, @x := @x + 3600 AS end
    FROM mysql.help_topic
    WHERE @x <= @y
) AS hours
LEFT JOIN events AS e
ON e.start_time < hours.end AND e.end_time > hours.start
GROUP BY hours.start

这为我提供了您提供的测试数据的以下输出:

+-------------+------------+
| event_count | time       |
+-------------+------------+
|           0 | 1485385200 |
|           1 | 1485388800 |
|           1 | 1485392400 |
|           2 | 1485396000 |
|           2 | 1485399600 |
|           1 | 1485403200 |
|           2 | 1485406800 |
|           1 | 1485410400 |
|           0 | 1485414000 |
+-------------+------------+