如何基于分区列给不同rank值分配相同列ID（SQL/Hive）

问题描述 #

现有一张包含from_app_id、from_app_name、to_app_name、id、rnk列的表，其中rnk由以下语句生成：

rank() over(partition by from_app_id, from_app_name, to_app_name order by lastseen desc)

当前数据中不同rank对应不同id，需求为：同一分区（即from_app_id、from_app_name、to_app_name组合相同的分组）内所有行的id，统一替换为该分区内rnk=1对应的id值。

输入示例 #

输出示例 #

解决方案 #

以下提供两种适用于Hive/SQL的实现方式：

方法1：使用窗口函数FIRST_VALUE（推荐） #

利用FIRST_VALUE窗口函数，在每个分区内直接获取rnk=1对应的id值，语法简洁且仅需一次表扫描：

SELECT 
    from_app_id,
    from_app_name,
    to_app_name,
    -- 按rnk升序取分区内第一个id（即rnk=1对应的id）
    FIRST_VALUE(id) OVER (
        PARTITION BY from_app_id, from_app_name, to_app_name 
        ORDER BY rnk ASC 
        -- 可选：确保全分区范围取第一个值
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS id,
    rnk
FROM your_table_name;

说明：如果想和生成rnk的逻辑保持一致，也可以将ORDER BY rnk ASC替换为ORDER BY lastseen DESC，效果完全相同。

方法2：子查询+关联查询 #

先提取每个分区内rnk=1的id，再通过关联将该id映射到分区内所有行：

WITH rank1_id_mapping AS (
    SELECT 
        from_app_id,
        from_app_name,
        to_app_name,
        id AS target_id
    FROM your_table_name
    WHERE rnk = 1
)
SELECT 
    t.from_app_id,
    t.from_app_name,
    t.to_app_name,
    m.target_id AS id,
    t.rnk
FROM your_table_name t
JOIN rank1_id_mapping m 
    ON t.from_app_id = m.from_app_id
    AND t.from_app_name = m.from_app_name
    AND t.to_app_name = m.to_app_name;

说明：这种方式逻辑直观，适合需要对rank=1的id做额外处理的场景，但需要两次扫描表，性能略逊于方法1。

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