如何用MySQL统计任意60分钟时间窗口内的最大记录数并判断阈值？

可以仅通过MySQL查询完成需求，无需额外编码，以下是两种可行方案：

方案一：自关联统计（兼容所有MySQL版本） #

通过表自关联，以每条记录的时间作为窗口起始点，统计对应60分钟内的记录总数，最后取最大值判断是否超过100：

SELECT MAX(window_record_count) AS max_60min_records
FROM (
    SELECT COUNT(r2.id) AS window_record_count
    FROM records r1
    INNER JOIN records r2 
        ON r2.created_at BETWEEN r1.created_at AND DATE_ADD(r1.created_at, INTERVAL 60 MINUTE)
    -- 限定窗口起始时间在1点到2点之间
    WHERE r1.created_at BETWEEN 'YYYY-MM-DD 01:00:00' AND 'YYYY-MM-DD 02:00:00'
    GROUP BY r1.created_at
) AS window_stats;

替换语句中的YYYY-MM-DD为实际数据的日期。若返回的max_60min_records大于100，说明存在符合条件的连续60分钟窗口。

方案二：窗口函数（MySQL 8.0+ 推荐） #

利用MySQL 8.0及以上版本支持的滑动窗口函数，性能比自关联更优：

SELECT MAX(rolling_count) AS max_60min_records
FROM (
    SELECT 
        created_at,
        -- 以当前时间为起点，统计往后3599秒（59分59秒）内的记录数
        COUNT(*) OVER (
            ORDER BY UNIX_TIMESTAMP(created_at)
            RANGE BETWEEN 0 PRECEDING AND 3599 FOLLOWING
        ) AS rolling_count
    FROM records
    -- 覆盖所有可能涉及的记录时间范围
    WHERE created_at BETWEEN 'YYYY-MM-DD 01:00:00' AND 'YYYY-MM-DD 03:00:00'
) AS rolling_stats
-- 只保留起始时间在1点到2点之间的窗口
WHERE created_at BETWEEN 'YYYY-MM-DD 01:00:00' AND 'YYYY-MM-DD 02:00:00';

关于切片的疑问解答 #

• 整点小时切片无法满足需求：因为它只能统计固定整点区间（如1:00-2:00）的记录数，无法覆盖跨整点的滑动窗口（比如1:50-2:50这类起始于1点内的窗口）。
• 无需编码聚合：上述两种纯SQL方案均能实现任意连续60分钟窗口的统计，不需要额外编写代码处理数据。

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