编写按小时统计/live//activate事件设备数的HiveQL查询

嘿，重复写24次查询这种事儿真的没必要！咱们完全可以利用Hive的日期函数和分组特性，一次性搞定每日所有24小时区间的设备数统计，彻底告别重复操作。下面给你两种实用的优化方案：

方案一：直接按小时分组（快速实现） #

这是最直接的方法，通过截断时间字段到小时级别，然后分组聚合，一次查询就能输出所有小时的结果。

假设你的事件表名为event_log，包含字段：

• event_time：事件发生时间（timestamp类型，若为字符串需先转换）
• device_id：设备唯一标识
• event_type：事件类型

对应的HiveQL代码：

SELECT
  -- 格式化小时区间起始时间，比如输出'2018-01-26 04:00:00'
  date_format(date_trunc('hour', event_time), 'yyyy-MM-dd HH:00:00') AS hour_interval_start,
  -- 统计该小时内的去重设备数
  COUNT(DISTINCT device_id) AS unique_device_count
FROM
  event_log
WHERE
  event_type = '/live//activate'
  -- 按需添加日期过滤，比如只统计2018-01-26当天
  AND date(event_time) = '2018-01-26'
GROUP BY
  date_trunc('hour', event_time)
ORDER BY
  hour_interval_start;

关键函数说明： #

• date_trunc('hour', event_time)：把任意时间截断到小时级别，比如2018-01-26 04:15:30会被处理成2018-01-26 04:00:00，这就是每个小时区间的起始点。
• COUNT(DISTINCT device_id)：确保同一设备在一小时内多次触发事件不会被重复计数。

如果你的event_time是字符串类型，先转换成timestamp即可：

to_timestamp(event_time, 'yyyy-MM-dd HH:mm:ss')

方案二：补全无数据的小时区间（显示0值） #

如果有些小时区间没有触发/live//activate事件，方案一的结果里不会出现该区间。如果需要强制显示24个小时（无数据时显示0），可以用维度表左连接的方式实现：

-- 生成当天24个小时的维度表
WITH hour_dim AS (
  SELECT
    -- 替换成你需要统计的日期
    date_add('2018-01-26', 0) + posexplode(array(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23)) * interval 1 hour AS hour_start
  FROM
    (SELECT 1) t
)
SELECT
  date_format(h.hour_start, 'yyyy-MM-dd HH:00:00') AS hour_interval_start,
  -- 把NULL值转换成0
  COALESCE(COUNT(DISTINCT e.device_id), 0) AS unique_device_count
FROM
  hour_dim h
LEFT JOIN
  event_log e
ON
  date_trunc('hour', e.event_time) = h.hour_start
  AND e.event_type = '/live//activate'
GROUP BY
  h.hour_start
ORDER BY
  h.hour_start;

这个方案通过posexplode生成0到23的数字，再和目标日期相加得到当天的24个小时起始时间，然后左连接事件表，确保每个小时都能出现在结果里。

性能优化小技巧 #

如果数据量很大，COUNT(DISTINCT)可能会有点慢，可以改用“先去重再计数”的方式，性能往往更优：

SELECT
  hour_interval_start,
  COUNT(device_id) AS unique_device_count
FROM (
  -- 先按小时和设备ID去重
  SELECT
    date_format(date_trunc('hour', event_time), 'yyyy-MM-dd HH:00:00') AS hour_interval_start,
    device_id
  FROM
    event_log
  WHERE
    event_type = '/live//activate'
    AND date(event_time) = '2018-01-26'
  GROUP BY
    date_trunc('hour', event_time), device_id
) t
GROUP BY
  hour_interval_start
ORDER BY
  hour_interval_start;

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