如何用SQL统计每小时处于进行状态的事件数量？

嘿，这个需求我之前做餐厅客流统计的时候刚好碰到过，纯SQL完全能搞定，给你两种实用的思路，根据你的数据库类型和数据量选就行：

方法一：小时区间匹配法（通用型，适合大多数数据库） #

这个思路最直观：先生成你需要统计的所有小时区间，然后逐个判断每个事件是否在该小时内处于活跃状态。

核心逻辑 #

一个事件在某个小时内活跃，需要满足两个条件：

• 事件的开始时间 ≤ 该小时的结束时刻（比如12点的结束时刻是13:00）
• 事件的结束时间 ≥ 该小时的开始时刻（比如12点的开始时刻是12:00）

示例SQL（以MySQL 8+为例） #

-- 首先生成需要统计的小时序列，这里从12到14，也可以动态生成
WITH hours AS (
  SELECT 12 AS hour_num
  UNION ALL
  SELECT hour_num + 1
  FROM hours
  WHERE hour_num < (SELECT MAX(HOUR(end_time)) FROM events)
),
-- 为每个小时生成完整的时间区间
hour_intervals AS (
  SELECT
    hour_num,
    STR_TO_DATE(CONCAT(hour_num, ':00:00'), '%H:%i:%s') AS hour_start,
    STR_TO_DATE(CONCAT(hour_num + 1, ':00:00'), '%H:%i:%s') AS hour_end
  FROM hours
)
-- 关联事件表统计活跃数量
SELECT
  hi.hour_num AS `Hour`,
  COUNT(e.event) AS `Count`
FROM hour_intervals hi
LEFT JOIN events e
  ON e.start_time <= hi.hour_end
  AND e.end_time >= hi.hour_start
GROUP BY hi.hour_num
ORDER BY hi.hour_num;

补充说明 #

• 如果你的数据库不支持CTE（比如MySQL 5.x），可以用数字表或者临时表来生成小时序列；
• 时间类型如果是DATETIME，只需要调整hour_start和hour_end的生成逻辑，保留日期部分即可；
• 这个方法逻辑清晰，适合数据量不大的场景，容易调试。

方法二：事件标记累加发（高效型，适合大数据量） #

如果你的事件数据量很大，逐个匹配会比较慢，可以用事件点标记+累加的思路，效率更高。

核心逻辑 #

把每个事件拆成两个标记：

• 事件开始时，对应小时的活跃数+1；
• 事件结束时，对应小时的活跃数-1；
  然后按小时排序，用累加的方式得到每个小时的实时活跃数。

示例SQL（支持窗口函数的数据库） #

-- 生成事件的开始/结束标记
WITH event_markers AS (
  SELECT
    HOUR(start_time) AS event_hour,
    1 AS delta
  FROM events
  UNION ALL
  SELECT
    HOUR(end_time) AS event_hour,
    -1 AS delta
  FROM events
),
-- 统计每个小时的delta总和
hour_totals AS (
  SELECT
    event_hour,
    SUM(delta) AS total_delta
  FROM event_markers
  GROUP BY event_hour
),
-- 生成连续的小时序列，避免遗漏中间小时
hours_list AS (
  SELECT DISTINCT event_hour AS hour_num FROM event_markers
  UNION
  SELECT DISTINCT event_hour + 1 FROM event_markers 
  WHERE event_hour + 1 <= (SELECT MAX(HOUR(end_time)) FROM events)
),
-- 累加得到每个小时的活跃数
running_totals AS (
  SELECT
    hl.hour_num,
    SUM(COALESCE(ht.total_delta, 0)) OVER (ORDER BY hl.hour_num) AS active_count
  FROM hours_list hl
  LEFT JOIN hour_totals ht ON hl.hour_num = ht.event_hour
)
SELECT
  hour_num AS `Hour`,
  active_count AS `Count`
FROM running_totals
WHERE active_count > 0
ORDER BY hour_num;

补充说明 #

• 这个方法通过“拆事件为标记”的方式，把复杂度从O(n*m)降到O(n)，大数据量下优势明显；
• COALESCE用来处理没有标记的小时（delta为0），保证累加逻辑正确；
• 如果事件结束时间刚好在某个小时的起始点（比如13:00结束），这个标记会算在13点，不会影响12点的统计，符合业务逻辑。

内容的提问来源于stack exchange，提问作者Randy Jay Yarger