MySQL中关联两表并按唯一时间戳汇总第二表数据的实现方法

先理清楚你的核心需求：针对指定用户（userid=1），按10分钟的时间粒度，汇总该用户所有MAC地址的流量delta总和。我先假设两张表的典型结构（如果你的表结构有差异，可以调整字段名）：

• users表：userid（主键）、username等用户基础信息
• devices表：mac_address（设备MAC）、userid（关联用户）、record_time（记录时间戳）、delta_traffic（本次记录的流量增量）

基础实现方案 #

这里分两种场景，你可以根据实际情况选择：

场景1：直接从devices表过滤（如果devices表已关联userid）

如果devices表本身有userid字段，不需要额外关联users表也能过滤用户，SQL会更简洁：

-- 按10分钟粒度分组，计算每个时间区间的总流量增量
SELECT
  -- 将时间戳转换为10分钟起始点（比如2024-05-20 14:32:15 → 2024-05-20 14:30:00）
  FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(record_time)/600)*600) AS time_window,
  SUM(delta_traffic) AS total_delta_traffic
FROM devices
WHERE userid = 1  -- 指定目标用户
GROUP BY time_window
ORDER BY time_window;

场景2：关联users表（用于验证用户存在或结合用户属性过滤）

如果需要确保只统计存在于users表中的用户，或者要结合users表的其他字段过滤，就需要关联两张表：

SELECT
  FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(d.record_time)/600)*600) AS time_window,
  SUM(d.delta_traffic) AS total_delta_traffic
FROM users u
JOIN devices d ON u.userid = d.userid
WHERE u.userid = 1  -- 指定目标用户
GROUP BY time_window
ORDER BY time_window;

关键细节说明 #

• 时间粒度处理：UNIX_TIMESTAMP(record_time)把时间转成秒级时间戳，除以600（10分钟=600秒）后取整，再转成时间格式，这样就能把所有属于同一10分钟区间的记录归为一组。如果你习惯用DATE_FORMAT，也可以写成：

  DATE_FORMAT(record_time, '%Y-%m-%d %H:') || FLOOR(MINUTE(record_time)/10)*10 || ':00' AS time_window
  

• 关联逻辑：用JOIN（内连接）只会统计同时存在于users和devices表中的数据，如果要包含没有设备记录的用户时间区间（比如该用户某10分钟没有流量），可以考虑用LEFT JOIN结合生成的时间序列表，但这需要额外处理时间范围。
• 性能优化：如果数据量较大，建议给devices表的userid和record_time字段建立联合索引，能大幅提升分组查询的速度：

  CREATE INDEX idx_devices_userid_time ON devices(userid, record_time);
  

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