如何用Hive计算连续0-1序列时长？原MySQL代码转Hive实现

我来帮你把这段MySQL代码转换成Hive版本的——毕竟Hive不支持MySQL那种用户变量的连续赋值逻辑，得用窗口函数来实现相同的连续序列分组效果。下面是具体的转换思路和代码：

核心思路 #

1. 先把字符串格式的时间转换成Hive可识别的时间类型；
2. 使用LAG()窗口函数获取当前行的上一行id，判断是否和当前id一致，生成分组标记；
3. 通过累加分组标记，生成唯一的连续序列组标签；
4. 最后按组标签和id分组，计算每组的时长。

完整Hive代码 #

WITH time_converted AS (
    -- 第一步：转换时间格式，把字符串转成Hive可处理的时间类型
    SELECT 
        id,
        from_unixtime(unix_timestamp(time, 'yyyyMMddHHmm')) AS date1, -- 转成标准时间字符串
        unix_timestamp(time, 'yyyyMMddHHmm') AS time_unix -- 转成时间戳，辅助排序
    FROM test.t
),
group_marker AS (
    -- 第二步：用LAG获取前一行的id，标记是否开启新的连续组
    SELECT 
        id,
        date1,
        -- 如果当前id和前一行id不同，标记为1（新组），否则0
        CASE WHEN LAG(id) OVER (ORDER BY time_unix) != id THEN 1 ELSE 0 END AS is_new_group
    FROM time_converted
),
group_labeled AS (
    -- 第三步：累加标记生成连续组的唯一标签
    SELECT 
        id,
        date1,
        SUM(is_new_group) OVER (ORDER BY time_unix ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS id_label
    FROM group_marker
)
-- 第四步：按组标签和id分组，计算每个连续段的时长（分钟）
SELECT 
    id_label,
    id,
    TIMESTAMPDIFF(MINUTE, MIN(date1), MAX(date1)) AS duration_minutes
FROM group_labeled
GROUP BY id_label, id
ORDER BY id_label;

代码说明 #

• time_converted CTE：把原始的time字符串（格式yyyyMMddHHmm）转换成Hive标准时间格式和时间戳，确保排序和时间计算的准确性；
• group_marker CTE：用LAG(id) OVER (ORDER BY time_unix)获取当前行的上一行id，通过CASE判断是否需要开启新的连续组；
• group_labeled CTE：用累加窗口函数SUM(is_new_group) OVER (...)生成连续组的唯一标签，相同且连续的id会被分到同一个id_label下；
• 最后分组计算：和你的MySQL逻辑一致，通过MIN(date1)和MAX(date1)计算每个连续段的时长，用TIMESTAMPDIFF转换成分钟数。

用你给出的示例数据测试的话，会得到三个符合预期的分组：

1. id=0，id_label=0，时长5分钟（11:27到11:32）；
2. id=1，id_label=1，时长6分钟（11:35到11:41）；
3. id=0，id_label=2，时长2分钟（11:45到11:47）；

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