基于状态变化生成Hive表时间戳列的高效实现方法

我在Hive中有如下数据表：

需要输出两个新列actioned和completed，其中actioned列对应state=1的时间戳，completed列对应state=0的时间戳，且要求将每个state=1的记录与后续最近的state=0记录配对，忽略无对应前置state=1的state=0记录。期望输出结果如下：

请问实现该需求的最高效方式是什么？

高效实现方案 #

处理这个需求最高效的办法是用窗口函数，不用多表关联就能搞定，性能拉满，具体SQL如下：

WITH numbered_data AS (
    SELECT 
        id,
        state,
        timestamp,
        -- 按id分组、时间排序，每碰到state=1就给后续记录分配同一个group_id，把1和对应的0归为一组
        SUM(CASE WHEN state = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY id ORDER BY timestamp) AS group_id
    FROM your_table_name -- 这里替换成你的表名
)
SELECT 
    id,
    MAX(CASE WHEN state = 1 THEN timestamp END) AS actioned,
    MIN(CASE WHEN state = 0 THEN timestamp END) AS completed
FROM numbered_data
WHERE group_id > 0 -- 滤掉没对应前置1的孤立0记录
GROUP BY id, group_id
HAVING completed IS NOT NULL -- 只保留有完成时间的有效分组
ORDER BY id, actioned;

逻辑说明 #

1. 分组打标：用SUM() OVER()窗口函数，按id分组、时间从小到大排序，每遇到一条state=1的记录，就给这条记录和后面直到下一个state=1之前的所有记录，打上同一个group_id。这样每一组就是一个“启动（1）→完成（0）”的配对单元。
2. 聚合取数：按id和group_id分组后，每组里只会有一条state=1的记录，用MAX就能把它的时间捞出来当actioned；组里可能有多个state=0的记录，用MIN取最早的那个，正好对应最近的完成时间。
3. 过滤无效项：group_id=0的是那些在第一个state=1之前的孤立0记录，直接滤掉；HAVING条件确保只保留有对应完成时间的分组，避免出现只有启动没有完成的无效行。

这个方案只扫一次原始表，窗口函数的计算开销比多表join小得多，是Hive里处理这类配对需求的最优选择。

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