Hive SQL如何实现按5分钟时间间隔分组聚合秒级海量采集数据

• 基础语法错误：表名前的关键字FORM拼写错误，正确写法是FROM
• 时间计算逻辑错误：秒级时间戳除以300后未做向下取整，会返回带小数的计算结果，from_unixtime无法正确解析非整数秒值，无法输出准确的5分钟区间边界
• 缺失核心聚合逻辑：既没有指定GROUP BY分组维度，也没有对指标字段value做聚合计算，无法实现分区间数据聚合降量的目标

以下写法适配Hive、Spark SQL、MySQL 8.x等主流支持Unix时间戳函数的数据库/数仓：

SELECT 
  operation,
  -- 按需替换聚合函数：求和用SUM、求平均用AVG、取最大值用MAX即可
  SUM(value) AS value,
  from_unixtime(
    FLOOR(unix_timestamp(update_time) / 300) * 300,
    'yyyy-MM-dd HH:mm:ss'
  ) AS update_time
FROM storage
GROUP BY 
  operation,
  FLOOR(unix_timestamp(update_time) / 300)

1. 先通过unix_timestamp函数把字符串格式的update_time转换为秒级Unix时间戳
2. 将时间戳除以300（单段5分钟对应的总秒数）后用FLOOR向下取整，得到当前时间所属5分钟区间的序列值，再乘以300即可得到对应区间起始点的准确秒级时间戳
3. 用from_unixtime把计算得到的区间起始时间戳转回yyyy-MM-dd HH:mm:ss格式的标准时间，和operation共同作为分组维度，对value做对应聚合计算即可
4. 如果不需要对齐时钟自然时间的整5分边界（如35分、40分、45分），而是要对齐业务采集的起始秒偏移（比如你给出的期望结果是从04秒开始每5分钟一段），只需要在时间计算时补入偏移量即可，以上文的4秒偏移为例，时间计算部分修改为：

from_unixtime(
  FLOOR( (unix_timestamp(update_time) - 4) / 300 ) * 300 + 4,
  'yyyy-MM-dd HH:mm:ss'
) AS update_time

计算得到的区间起始点就会和你给出的期望样例完全匹配，输出2021-03-18 22:37:04、2021-03-18 22:42:04这类格式的结果。

注意：如果使用MySQL 5.x版本，from_unixtime的格式化字符串需要调整为'%Y-%m-%d %H:%i:%s'，其余计算逻辑不变。

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