基于MySQL滑动窗口统计带创建/更新时间的每小时待处理行数

核心判定逻辑：对于某一整点区间[h, h+1小时)，所有满足created_at < h+1小时 且（updated_at IS NULL 或 updated_at >= h+1小时）的行，都属于该小时的待处理行。

方案1：递归CTE生成时间序列+直接关联统计（适合中小数据量） #

写法简单直观，适合数据量在十万级以下的场景：

SET @start_time = '2024-01-01 00:00:00'; -- 替换为你的查询起始时间
SET @end_time = '2024-01-07 23:00:00'; -- 替换为你的查询结束时间

WITH RECURSIVE hours AS (
    SELECT @start_time AS hour_start
    UNION ALL
    SELECT DATE_ADD(hour_start, INTERVAL 1 HOUR)
    FROM hours
    WHERE hour_start < @end_time
)
SELECT 
    h.hour_start,
    COUNT(i.id) AS pending_count
FROM hours h
LEFT JOIN items i
ON  i.created_at < DATE_ADD(h.hour_start, INTERVAL 1 HOUR)
AND (i.updated_at IS NULL OR i.updated_at >= DATE_ADD(h.hour_start, INTERVAL 1 HOUR))
GROUP BY h.hour_start
ORDER BY h.hour_start;

缺点是每个小时都会关联一次全表，数据量过大时性能较差。

方案2：事件累加计算（适合大数据量，性能更高） #

将每条数据的生命周期拆为+1的创建事件、-1的更新事件，累加后按小时取最终值，仅需扫描一次items表，适合百万级以上数据量：

SET @start_time = '2024-01-01 00:00:00';
SET @end_time = '2024-01-07 23:00:00';
SET @current_pending = 0;

-- 先计算查询起始时间之前的初始待处理数
SELECT COUNT(*) INTO @init_pending
FROM items
WHERE created_at < @start_time
AND (updated_at IS NULL OR updated_at >= @start_time);

WITH all_events AS (
    -- 插入所有创建事件
    SELECT created_at AS event_time, 1 AS delta FROM items
    UNION ALL
    -- 插入所有更新事件
    SELECT updated_at AS event_time, -1 AS delta FROM items WHERE updated_at IS NOT NULL
    UNION ALL
    -- 插入小时占位事件，保证无数据的小时也能输出
    SELECT hour_start AS event_time, 0 AS delta 
    FROM (
        WITH RECURSIVE hours AS (
            SELECT @start_time AS hour_start
            UNION ALL
            SELECT DATE_ADD(hour_start, INTERVAL 1 HOUR)
            FROM hours WHERE hour_start < @end_time
        ) SELECT hour_start FROM hours
    ) h
),
sorted_events AS (
    -- 按时间排序计算待处理数的累加值
    SELECT 
        event_time,
        delta,
        @current_pending := @current_pending + delta AS running_total
    FROM all_events
    WHERE event_time BETWEEN @start_time AND DATE_ADD(@end_time, INTERVAL 1 HOUR)
    ORDER BY event_time
)
-- 按小时分组取每个小时最后一个累加值作为该小时的待处理数
SELECT 
    DATE_FORMAT(event_time, '%Y-%m-%d %H:00:00') AS hour_start,
    MAX(CASE WHEN rn = 1 THEN running_total ELSE @init_pending END) AS pending_count
FROM (
    SELECT 
        event_time,
        running_total,
        ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(event_time, '%Y-%m-%d %H:00:00') ORDER BY event_time DESC) AS rn
    FROM sorted_events
) t
WHERE rn = 1
AND hour_start BETWEEN @start_time AND @end_time
ORDER BY hour_start;

如果你的MySQL版本低于8.0不支持CTE，可以用临时表替代递归CTE生成时间序列，也可以提前维护一张独立的时间维度表提升查询效率。

内容的提问来源于stack exchange，提问作者est