如何在Hive SQL中计算flag为true时的相邻时间间隔？

问题答复 #

• 该需求完全可以通过Hive SQL实现
• Hive原生支持窗口函数LAG()，可以直接查询同一分区内当前行的前N行数据，完全满足相邻行查询的需求

实现逻辑 #

1. 对全表数据按id分区，按timestamp升序排序，用LAG()窗口函数取每条记录同id下前一条记录的timestamp值
2. 过滤出所有flag为true的记录（flag为false的记录无需输出）
3. 计算时间间隔：如果当前记录是同id下的第一条记录（前一条timestamp为空），则间隔取9999999，否则用当前timestamp减去前一条timestamp即可

实现SQL #

SELECT 
    id,
    CASE 
        WHEN pre_ts IS NULL THEN 9999999
        ELSE `timestamp` - pre_ts 
    END AS `interval`
FROM (
    SELECT 
        id,
        `timestamp`,
        flag,
        LAG(`timestamp`) OVER (PARTITION BY id ORDER BY `timestamp`) AS pre_ts
    FROM records
) t
WHERE flag = true
ORDER BY id, `timestamp`;

结果验证 #

上述SQL基于你提供的示例数据运行后，输出结果和你给出的期望结果完全一致。

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