如何在Hive中为每个唯一ID筛选Status为Active的最新记录？

这是个非常常见的分组取TopN需求，在Hive里使用窗口函数是最高效的解决方式，尤其是面对大数据量时，能避免多次扫描表的开销。

先来看你的原始数据和预期结果：

原始表数据 #

预期结果 #

高效查询SQL #

SELECT id, value
FROM (
    SELECT 
        id, 
        value,
        -- 按ID分区，时间戳倒序排序，给每条记录分配行号
        row_number() OVER (PARTITION BY id ORDER BY timestamp DESC) AS rn
    FROM your_table_name  -- 替换成你的实际表名
    WHERE status = 'Active'  -- 先过滤出Active状态的记录，减少后续计算量
) t
WHERE rn = 1;  -- 只保留每个ID组内的第一条（最新的）记录

代码解释 #

1. 过滤Active记录：先通过WHERE status = 'Active'筛选出符合状态要求的数据，减少后续窗口函数的计算范围，直接提升查询效率。
2. 窗口函数分组排序：row_number() OVER (PARTITION BY id ORDER BY timestamp DESC)会把数据按ID分组，每个组内按时间戳从新到旧排序，给每条记录分配唯一的行号，最新的记录行号为1。
3. 筛选目标记录：外层查询只取行号为1的记录，就精准得到了每个ID下最新的Active记录。

扩展说明 #

如果存在多个记录Timestamp完全相同的情况，row_number()会随机选取其中一条。如果需要保留所有这些时间戳相同的最新记录，可以把row_number()替换为rank()或者dense_rank()：

• rank()：相同时间戳的记录会获得相同行号，后续行号会跳过（比如1,1,3）
• dense_rank()：相同时间戳的记录获得相同行号，后续行号连续（比如1,1,2）

根据你的实际业务需求选择即可。

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