SQL怎样统计每个小时的流量峰值_ROW_NUMBER时间分区-SQL-PHP中文网

必须先将时间截断到整点（如'2024-05-20 14:37:22'→'2024-05-20 14:00:00'），再按截断后时间分组并用row_number()取每小时流量峰值行，否则仅用hour()会跨天错误聚合。

sql怎样统计每个小时的流量峰值_row_number时间分区

用 `ROW_NUMBER()` 按小时分组取峰值行，核心是先截断时间再排序

直接按原始时间戳排序再 ROW_NUMBER() 会跨小时混排，必须先把时间归到整点。比如 '2024-05-20 14:37:22' 要转成 '2024-05-20 14:00:00' 才能正确分组。

不同数据库截断方式略有差异：

PostgreSQL / MySQL 8.0+：用 DATE_TRUNC('hour', event_time) 或 DATE_FORMAT(event_time, '%Y-%m-%d %H:00:00')
SQL Server：用 DATEADD(HOUR, DATEDIFF(HOUR, 0, event_time), 0)
SQLite：用 strftime('%Y-%m-%d %H:00:00', event_time)

归整后，再对每小时内的记录按流量字段（如 bytes）降序排，ROW_NUMBER() OVER (PARTITION BY hour_slot ORDER BY bytes DESC) 就能标出每小时最大值所在行。

为什么不能只用 `GROUP BY HOUR(event_time)`？

HOUR(event_time) 只返回 0–23 的数字，丢失了日期信息，会导致“今天 14 点”和“昨天 14 点”被合并在同一组——这不是你想要的每小时独立峰值。

真正需要的是带日期的完整小时槽位，例如：

✅ '2024-05-20 14:00:00'
❌ 14（无日期，跨天聚合）

所以必须用带日期的时间截断函数，而不是单纯提取小时数。

峰值统计结果里要不要保留原始时间戳？

要。仅靠 hour_slot 只知道是哪一小时，但不知道峰值具体发生在该小时内的哪个时刻。实际排查时，运维或产品常需要定位到秒级时间点。

写法上别只选 hour_slot 和 bytes，记得带上原始字段：

SELECT
  hour_slot,
  event_time,  -- 原始时间，精确到秒
  bytes,
  user_id
FROM (
  SELECT
    DATE_TRUNC('hour', event_time) AS hour_slot,
    event_time,
    bytes,
    user_id,
    ROW_NUMBER() OVER (
      PARTITION BY DATE_TRUNC('hour', event_time)
      ORDER BY bytes DESC
    ) AS rn
  FROM traffic_log
) t
WHERE rn = 1;

注意：如果某小时内有多个相同最大 bytes 值，默认按 event_time 稳定性不确定，可追加 , event_time ASC 避免非确定性结果。