基于现有Hive代码实现自定义规则的0-1序列时长计算

完整Hive实现代码

with q1 as (
    select 
        -- 将字符串时间转换为从 epoch 开始的分钟数，简化时长计算
        unix_timestamp(time, 'yyyyMMddHHmm')/60 as time_min,
        id,
        -- 标记每个连续ID组的第一条记录：当前ID与上一行不同时标记为1，否则为null
        case 
            when lag(id) over(order by time) is not null and lag(id) over(order by time) = id then null
            else 1 
        end as first_in_group
    from t
), 
q2 as (
    select 
        time_min, 
        id,
        -- 累加标记值，为每个连续ID组生成唯一的组ID
        count(first_in_group) over (order by time_min) as grp_id
    from q1
),
q3 as (
    select 
        grp_id,
        id,
        -- 获取当前组的起始时间（组内最早的分钟数）
        min(time_min) as group_start_min,
        -- 获取当前组的结束时间：下一组的起始时间，最后一组用12:00对应的分钟数
        lead(min(time_min), 1, unix_timestamp('201801051200', 'yyyyMMddHHmm')/60) over(order by grp_id) as group_end_min
    from q2
    group by grp_id, id
)
select 
    id,
    -- 按规则计算每组时长
    case 
        when grp_id = 1 then group_end_min - unix_timestamp('201801051100', 'yyyyMMddHHmm')/60
        else group_end_min - group_start_min
    end as duration_minutes
from q3
order by grp_id;

代码逻辑拆解

1. q1：标记连续ID组的起始行

   • 用unix_timestamp将字符串时间转为分钟数，避免字符串时间计算的复杂度
   • 通过lag(id) over(order by time)对比当前行与上一行的ID，标记出每个连续ID组的第一条记录（first_in_group=1）

2. q2：生成唯一组ID

   • 利用count(first_in_group) over(order by time_min)的累加特性，为每一段连续的相同ID分配一个唯一的grp_id，实现连续分组

3. q3：确定每组的起止时间

   • 按grp_id和id分组，得到每组的起始分钟数group_start_min
   • 使用lead()窗口函数获取下一组的起始时间作为当前组的结束时间；如果是最后一组，则用固定结束时间201801051200的分钟数填充

4. 最终计算：按规则输出时长

   • 第一个组（grp_id=1）：用结束时间减去固定起始时间201801051100的分钟数
   • 其他组：直接用结束时间减去自身起始时间，最后按组ID排序输出

示例数据验证结果

运行上述代码后，示例数据的输出结果为：

完全符合规则：

• 第一个0组：从11:00到11:35，时长35分钟
• 1组：从11:35到11:45，时长10分钟
• 最后一个0组：从11:45到12:00，时长15分钟

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