SQL怎样统计每个小时的流量峰值？ROW_NUMBER时间分区详解

在流量监控、接口调用统计、网络设备日志分析等场景中，经常需要回答这样一个问题：一天24小时里，每个小时的最大流量是多少？这个峰值出现在该小时的第几分钟，对应哪条记录？如果只用一个GROUP BY按小时取MAX(traffic_bytes)，确实能拿到每小时的最大流量数值，但不能直接带回峰值发生的时间点和其他维度的标识。要完整保留峰值行的上下文信息，可以使用窗口函数ROW_NUMBER()按小时分区、按流量降序排序，再筛选排名为1的行。下文以一个名为traffic_log的表为例，演示建表、时间截断、分区编号和最终查询的完整过程。

一、先把时间字段统一到小时粒度

原始流量日志通常精确到秒甚至毫秒，直接按visit_time分组无法把同一小时内的多条记录归到一起。时间截断是每小时峰值统计的第一步。以MySQL为例，可以使用DATE_FORMAT(visit_time, '%Y-%m-%d %H:00:00')把任意时间转换成整点小时槽，例如2025-01-15 10:23:11会变成2025-01-15 10:00:00。PostgreSQL里常用date_trunc('hour', visit_time)，SQL Server中可以使用DATEADD(HOUR, DATEDIFF(HOUR, 0, visit_time), 0)，思路完全一致。

先建立演示表并插入几条模拟数据，便于后续验证SQL结果。实际业务表的字段会更多，但核心字段只需要时间、流量值以及需要保留的业务标识。

CREATE TABLE traffic_log (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    visit_time DATETIME NOT NULL,
    traffic_bytes BIGINT NOT NULL,
    interface_name VARCHAR(50),
    device_id VARCHAR(50)
);

INSERT INTO traffic_log (visit_time, traffic_bytes, interface_name, device_id) VALUES
('2025-01-15 10:23:11', 2048, 'api_payment', 'server_01'),
('2025-01-15 10:41:52', 9216, 'api_order', 'server_02'),
('2025-01-15 10:58:03', 5120, 'api_payment', 'server_01'),
('2025-01-15 11:05:19', 3072, 'api_user', 'server_03'),
('2025-01-15 11:33:47', 8192, 'api_order', 'server_02');

把时间字段转换成小时槽之后，可以先观察每个小时内都有哪些记录，以及流量值如何分布。下面的查询会在结果集中额外增加一列小时槽，这列正是窗口函数后续分区的依据。

SELECT
    id,
    visit_time,
    traffic_bytes,
    DATE_FORMAT(visit_time, '%Y-%m-%d %H:00:00') AS hour_slot
FROM traffic_log;

二、用ROW_NUMBER()按小时分区提取峰值行

ROW_NUMBER()是常见的窗口函数，它的作用是给结果集中的每一行分配一个从1开始的连续序号。PARTITION BY用于划分窗口，这里按照小时槽分区，意味着每个小时内部独立编号；ORDER BY traffic_bytes DESC决定编号顺序，流量最大的行会得到序号1，因此只要在外层筛选rn = 1，就能拿到每个小时的流量峰值记录，而且保留了这一行原始的visit_time、interface_name等上下文字段。

完整查询语句如下。子查询里先完成时间截断和窗口函数编号，外层再过滤排名为1的行。这样一次扫描就能得到每小时峰值，不需要先聚合再回表关联。

SELECT
    hour_slot,
    visit_time,
    traffic_bytes,
    interface_name,
    device_id
FROM (
    SELECT
        id,
        visit_time,
        traffic_bytes,
        interface_name,
        device_id,
        DATE_FORMAT(visit_time, '%Y-%m-%d %H:00:00') AS hour_slot,
        ROW_NUMBER() OVER (
            PARTITION BY DATE_FORMAT(visit_time, '%Y-%m-%d %H:00:00')
            ORDER BY traffic_bytes DESC
        ) AS rn
    FROM traffic_log
) AS ranked
WHERE rn = 1
ORDER BY hour_slot;

这里有一个细节值得注意：如果同一个小时内存在两条流量值完全相同的记录，ROW_NUMBER()只会给其中一条编号为1，另一条编号为2，结果会丢失部分峰值信息。如果业务上需要保留所有并列峰值的记录，应该改用RANK()或DENSE_RANK()。RANK()遇到相同值会给出相同排名，例如两条并列第一都会是1，下一条直接变成3；DENSE_RANK()则下一条继续为2。下面的查询展示了用RANK()保留所有并列峰值行的写法。

SELECT
    hour_slot,
    visit_time,
    traffic_bytes,
    interface_name
FROM (
    SELECT
        visit_time,
        traffic_bytes,
        interface_name,
        DATE_FORMAT(visit_time, '%Y-%m-%d %H:00:00') AS hour_slot,
        RANK() OVER (
            PARTITION BY DATE_FORMAT(visit_time, '%Y-%m-%d %H:00:00')
            ORDER BY traffic_bytes DESC
        ) AS rnk
    FROM traffic_log
) AS ranked
WHERE rnk = 1
ORDER BY hour_slot;

三、ROW_NUMBER方案与GROUP BY + MAX方案对比

在没有窗口函数支持的老版本数据库里，很多人会先按小时分组求出最大流量值，再用这个最大值回表关联原始记录。典型写法是先子查询计算每小时MAX(traffic_bytes)，然后与原表按小时槽和流量值做内连接。这种方式能得到类似结果，但存在两个明显问题：一是需要扫描原表至少两次，一次聚合、一次关联，逻辑上不够直观；二是如果同一小时最大值出现多次，关联结果会返回多条重复峰值，必须再做去重处理。

下面是传统的GROUP BY + JOIN实现示例。可以看到，关联条件里既包含小时槽又包含流量值，SQL更冗长。

SELECT
    t.hour_slot,
    t.visit_time,
    t.traffic_bytes,
    t.interface_name,
    t.device_id
FROM traffic_log t
JOIN (
    SELECT
        DATE_FORMAT(visit_time, '%Y-%m-%d %H:00:00') AS hour_slot,
        MAX(traffic_bytes) AS max_bytes
    FROM traffic_log
    GROUP BY DATE_FORMAT(visit_time, '%Y-%m-%d %H:00:00')
) m
    ON DATE_FORMAT(t.visit_time, '%Y-%m-%d %H:00:00') = m.hour_slot
   AND t.traffic_bytes = m.max_bytes;

窗口函数方案则一次扫描即可完成分区排序，尤其在MySQL 8.0、PostgreSQL、SQL Server等现代数据库上，执行计划通常会选择排序加窗口聚合，代码可读性和维护性更好。当然，如果数据量非常大，窗口排序会消耗较多内存和临时空间，这时需要配合索引和物化小时列来优化。

一个比较实用的优化手段是增加生成列，把小时槽预先计算并存储，再对小时列和流量值建立联合索引。这样窗口函数分区时可以直接走索引，避免对时间字段执行DATE_FORMAT导致索引失效。以MySQL 8.0为例，可以执行以下DDL。

ALTER TABLE traffic_log
    ADD COLUMN hour_slot DATETIME
    GENERATED ALWAYS AS (DATE_FORMAT(visit_time, '%Y-%m-%d %H:00:00')) STORED;

CREATE INDEX idx_hour_traffic ON traffic_log (hour_slot, traffic_bytes DESC);

添加生成列后，业务查询里的DATE_FORMAT(visit_time, ...)可以替换为直接使用hour_slot字段。由于索引已经按照小时和流量降序排列，优化器有机会只扫描索引就能完成窗口函数计算，大幅减少排序开销。

四、扩展场景与边界处理

每小时流量峰值只是时间分区分析的一个例子，把小时槽改成分钟槽、天槽或者周槽，就可以统计不同粒度的峰值。例如按分钟统计时，MySQL里把格式字符串改为'%Y-%m-%d %H:%i:00'即可；PostgreSQL则使用date_trunc('minute', visit_time)。还可以把PARTITION BY扩展为多个字段，比如同时按照小时槽和接口名称分区，这样就能得到每个接口在每个小时内的峰值流量。

边界处理同样不能忽略。如果traffic_bytes可能为空，排序时MySQL默认把NULL当作最小值处理，按降序排列时空值会排到最后，一般不会影响峰值选择；但如果业务上希望把空值当作0看待，需要使用COALESCE(traffic_bytes, 0)。另外，跨时区场景下，先将会话时区统一到UTC再按小时截断，可以避免因为服务器时区设置不同导致小时槽偏移。数据量特别大的表还可以结合时间范围分区，例如按天分区后，统计某一天的小时峰值时只需扫描对应分区，性能会进一步提升。

综合来看，使用ROW_NUMBER()配合时间截断来做小时流量峰值统计，代码清晰、结果完整，并且很容易扩展出多种维度组合。实际落地时，需要注意数据库版本是否支持窗口函数、并列峰值的处理方式以及时间字段索引失效的优化点。掌握这些要点之后，无论是分钟级接口监控、每小时出口带宽统计，还是每日活跃峰值分析，都可以用同一套思路快速实现。

SQL统计每小时流量峰值ROW_NUMBER时间分区窗口函数修改时间：2026-09-17 17:07:13