Hive String格式时间戳按小时分组统计行数实现问题

问题原因 #

你原有写法存在两个核心问题：

1. 仅按天和小时两个独立字段分组，会导致不同年、不同月的同天同时段数据被错误归为同一组，统计逻辑错误
2. 输出字段为拆分的天、小时值，不符合你要求的完整时间格式输出需求

正确实现方案 #

你可以直接按小时级格式化后的时间字段分组，一步得到符合要求的结果，SQL写法如下：

SELECT 
  from_unixtime(unix_timestamp(`timestamp`, 'yyyyMMddHHmmss'), 'yyyy:MM:dd HH:00:00') AS date_time,
  COUNT(*) AS `count`
FROM table_name
WHERE `timestamp` BETWEEN '20181210070230' AND '20181212080230'
GROUP BY from_unixtime(unix_timestamp(`timestamp`, 'yyyyMMddHHmmss'), 'yyyy:MM:dd HH:00:00')
ORDER BY date_time;

说明 #

• 时间转换函数的第二个参数直接指定了你需要的输出格式，生成的date_time字段完全匹配预期输出要求
• WHERE条件直接用原始字符串过滤，由于你的时间字符串格式是按时间序排列的，字典序和时间序完全一致，不需要额外做时间转换，查询效率更高
• 如果你使用的Hive版本支持to_timestamp函数，也可以替换为更简洁的写法：

SELECT 
  date_format(to_timestamp(`timestamp`, 'yyyyMMddHHmmss'), 'yyyy:MM:dd HH:00:00') AS date_time,
  COUNT(*) AS `count`
FROM table_name
WHERE `timestamp` BETWEEN '20181210070230' AND '20181212080230'
GROUP BY date_format(to_timestamp(`timestamp`, 'yyyyMMddHHmmss'), 'yyyy:MM:dd HH:00:00')
ORDER BY date_time;

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