基于rank为每个ID选取最低与次低rank对应value的查询问题

实现逻辑 #

要得到目标结果可按3步处理：

1. 数据去重：对同一ID下rank值相同的记录，仅保留LastUpdate最新的一条
2. 优先级排序：对每个ID下去重后的记录，按rank值从小到大排序
3. 结果聚合：提取每个ID排序后前两位的value，行转列得到最终输出

参考SQL实现（支持窗口函数的数据库通用，如MySQL 8.0+、PostgreSQL、Hive等） #

WITH dedup_records AS (
    -- 第一步：同ID同rank仅留最新更新的记录
    SELECT 
        ID,
        value,
        rank,
        ROW_NUMBER() OVER (PARTITION BY ID, rank ORDER BY LastUpdate DESC) AS rn_rank
    FROM 替换为你的实际表名
),
unique_rank_records AS (
    SELECT ID, value, rank
    FROM dedup_records
    WHERE rn_rank = 1
),
ranked_values AS (
    -- 第二步：给每个ID下的记录按rank从小到大排序
    SELECT 
        ID,
        value,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY rank ASC) AS rn_overall
    FROM unique_rank_records
)
-- 第三步：行转列得到最终结果
SELECT 
    ID,
    MAX(CASE WHEN rn_overall = 1 THEN value END) AS value_lowest_rank,
    MAX(CASE WHEN rn_overall = 2 THEN value END) AS value_second_lowest_rank
FROM ranked_values
WHERE rn_overall <= 2
GROUP BY ID
ORDER BY ID;

补充说明 #

如果部分ID仅存在1条有效记录，次低rank对应的value会返回NULL，可按需使用COALESCE函数设置默认值。使用你提供的示例数据执行上述代码，输出结果与你给出的期望输出完全匹配。

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