如何使用Hive/SQL按列筛选数据并提取f1分组下的最新记录

实现规则 #

我们需要按f1字段分组提取符合要求的最新记录，具体规则如下：

• 同一f1分组内默认取时间戳最新的记录
• 若同一f1分组内存在f2字段值为3的记录，需先将该类记录的f4字段值与同分组其余记录的f3字段值匹配，删除所有匹配到的记录后，再从剩余记录中选取时间戳最新的记录

输入样例 #

输出样例 #

Hive SQL实现代码 #

WITH 
-- 收集每个f1分组下所有f2=3的记录的f4值，作为需要排除的f3列表
f3_exclude AS (
    SELECT 
        f1,
        collect_set(f4) AS exclude_f3_list
    FROM 你的表名
    WHERE f2 = 3
    GROUP BY f1
),
-- 过滤需要排除的记录后，按f1分组给剩余记录按时间戳倒序排名
filtered_rn AS (
    SELECT 
        t.*,
        row_number() OVER (PARTITION BY t.f1 ORDER BY t.timestamp DESC) AS rn
    FROM 你的表名 t
    LEFT JOIN f3_exclude e ON t.f1 = e.f1
    WHERE 
        e.exclude_f3_list IS NULL 
        OR array_contains(e.exclude_f3_list, t.f3) = false
)
-- 取每个分组排名第一的最新记录
SELECT f1, f2, f3, f4, timestamp 
FROM filtered_rn 
WHERE rn = 1;

代码说明 #

• 第一步先聚合每个f1分组下所有f2=3的f4值，生成需要排除的f3列表
• 第二步关联原表和排除列表，过滤掉所有f3在排除列表内的记录，再对剩余记录按f1分组、时间戳倒序排名
• 最后取每个分组排名第一的记录，即为符合要求的结果

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