Hive表查询event列值较前一行变化的记录的优化方案咨询

实现方案 #

你可以通过Hive窗口函数LAG()比对当前行与前一行的event值，筛选出发生变化的记录即可，该方案性能优异，适合大数据量场景：

完整SQL如下：

WITH event_diff AS (
    SELECT
        VIN,
        Mode,
        event,
        Start,
        End,
        -- 取同一VIN下，按时间升序排序的前一行event值，首行默认返回null
        LAG(event, 1, NULL) OVER (
            PARTITION BY VIN 
            ORDER BY to_timestamp(Start, 'MM/dd/yyyy HH:mm:ss') ASC
        ) AS pre_event
    FROM 你的Hive表名
)
SELECT
    VIN,
    Mode,
    event,
    Start,
    End
FROM event_diff
-- 筛选首行、或者event发生变化的行
WHERE pre_event IS NULL OR event != pre_event;

逻辑说明：

• 窗口函数按VIN分区，按Start时间升序排序，和你给出的数据顺序完全一致
• LAG(event,1,NULL)取当前行前一行的event值，第一行没有前一行返回NULL
• 最后过滤条件仅保留第一行、或者event和前一行不一致的行，刚好匹配你给出的期望输出

补充方案（合并连续相同event的时间区间）

如果你需要把连续相同event的多条记录合并为一条，取最早Start和最晚End，可以用如下会话分组写法：

WITH event_flag AS (
    SELECT
        VIN,
        Mode,
        event,
        Start,
        End,
        SUM(IF(pre_event IS NULL OR event != pre_event, 1, 0)) OVER (
            PARTITION BY VIN 
            ORDER BY to_timestamp(Start, 'MM/dd/yyyy HH:mm:ss') ASC
        ) AS session_id
    FROM (
        SELECT
            VIN,
            Mode,
            event,
            Start,
            End,
            LAG(event, 1, NULL) OVER (
                PARTITION BY VIN 
                ORDER BY to_timestamp(Start, 'MM/dd/yyyy HH:mm:ss') ASC
            ) AS pre_event
        FROM 你的Hive表名
    ) t1
)
SELECT
    VIN,
    MAX(Mode) AS Mode,
    event,
    MIN(Start) AS Start,
    MAX(End) AS End
FROM event_flag
GROUP BY VIN, event, session_id
ORDER BY MIN(Start) ASC;

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