Hive如何提取匹配条件行及前后相邻记录并转指定宽表格式

需求梳理 #

现有Hive对话记录表包含字段my_id（对话分组ID）、my_words（评论内容）、my_people（评论人）、my_number（同组内评论顺序号），需要实现如下逻辑：

• 筛选出评论人是Mary、且评论内容以now（不区分大小写）开头的记录
• 同时取出同my_id分组下，按my_number排序时，紧邻该条Mary记录前后位置的Jim的评论
• 最终将「前一条Jim评论、当前Mary评论、后一条Jim评论」拼接为宽表，方便导出Excel使用

实现逻辑 #

• 用Hive内置的LAG、LEAD窗口函数，按my_id分组、my_number排序，直接拿到每条记录相邻的上一条、下一条记录的评论人和评论内容
• 筛选符合条件的Mary记录，同时校验相邻两条记录的评论人均为Jim，避免取到不满足紧邻要求的无效数据
• 直接提取相邻字段即可得到需要的宽表结构，无需额外多表关联

代码实现 #

1. 直接生成最终宽表的SQL #

WITH base_with_adjacent AS (
    SELECT
        my_id,
        my_words,
        my_people,
        my_number,
        -- 取同组排序后上一条记录的评论人、评论内容
        LAG(my_people) OVER (PARTITION BY my_id ORDER BY my_number) AS prev_people,
        LAG(my_words) OVER (PARTITION BY my_id ORDER BY my_number) AS Jim_words,
        -- 取同组排序后下一条记录的评论人、评论内容
        LEAD(my_people) OVER (PARTITION BY my_id ORDER BY my_number) AS next_people,
        LEAD(my_words) OVER (PARTITION BY my_id ORDER BY my_number) AS Jim_next_words
    FROM your_hive_table -- 替换为实际的Hive表名
)
SELECT
    my_id,
    Jim_words,
    my_words AS Mary_words,
    Jim_next_words
FROM base_with_adjacent
WHERE
    my_people = 'Mary'
    AND LOWER(my_words) LIKE 'now%'
    AND prev_people = 'Jim'
    AND next_people = 'Jim';

2. 如果需要先获取题目中提到的中间明细结果（三条连续记录全量返回），可使用以下SQL #

WITH target_mary_record AS (
    SELECT my_id, my_number AS target_mary_num
    FROM your_hive_table -- 替换为实际的Hive表名
    WHERE my_people = 'Mary' AND LOWER(my_words) LIKE 'now%'
)
SELECT t.my_id, t.my_words, t.my_people, t.my_number
FROM your_hive_table t
JOIN target_mary_record m
    ON t.my_id = m.my_id
    AND t.my_number BETWEEN m.target_mary_num - 1 AND m.target_mary_num + 1
-- 过滤前后不是Jim的无效分组
WHERE EXISTS (
    SELECT 1 FROM your_hive_table prev
    WHERE prev.my_id = m.my_id
        AND prev.my_number = m.target_mary_num - 1
        AND prev.my_people = 'Jim'
)
AND EXISTS (
    SELECT 1 FROM your_hive_table next
    WHERE next.my_id = m.my_id
        AND next.my_number = m.target_mary_num + 1
        AND next.my_people = 'Jim'
)
ORDER BY t.my_id, t.my_number;

运行结果 #

执行宽表SQL后直接得到如下输出，可直接导出到Excel：

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