SQL按分组最大值过滤近重复行 查询用户最高技能等级记录

现有3张数据库表，所有存量历史数据因监管要求不可删除：

• dbo.skills：技能维度表，主键为skill_ID_PK
• dbo.people：用户维度表，主键为user_ID_PK
• dbo.skillHistory：技能持有/变更历史表，通过外键关联上述两张表的主键，存储用户所有技能的全量历史记录，存在同一用户同一技能多次等级变更的重复行
  需求为按用户ID+技能ID维度分组，返回每个用户对应每项技能最高等级对应的完整记录，过滤同维度下等级更低的冗余历史行。

优先使用窗口函数实现，该写法仅需扫描一次历史表，执行效率远高于分组自连接等传统写法，且全程为只读操作，完全符合合规要求，兼容SQL Server、MySQL 8.0+、PostgreSQL等所有支持窗口函数的主流数据库。

WITH history_ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY user_ID_PK, skill_ID_PK
            ORDER BY Skill_Level DESC, -- 按技能等级倒序，最高等级记录排分区首位
                    record_create_time DESC -- 若同用户同技能存在多条相同最高等级记录，取最新生成的一条，可根据业务实际时间字段调整排序规则
        ) AS row_rank
    FROM dbo.skillHistory
)
-- 需要关联用户、技能维度信息时直接关联维度表即可，不需要可以去掉join部分
SELECT
    h.*,
    p.user_name, -- 按需取people表字段
    s.skill_name -- 按需取skills表字段
FROM history_ranked h
LEFT JOIN dbo.people p ON h.user_ID_PK = p.user_ID_PK
LEFT JOIN dbo.skills s ON h.skill_ID_PK = s.skill_ID_PK
WHERE h.row_rank = 1;

低版本兼容写法 #

如果数据库版本不支持窗口函数（如MySQL 5.x、SQL Server 2005以下版本），可以使用分组关联的写法，性能弱于窗口函数版本，数据量较大时不推荐：

SELECT h.*
FROM dbo.skillHistory h
INNER JOIN (
    SELECT
        user_ID_PK,
        skill_ID_PK,
        MAX(Skill_Level) AS max_skill_level
    FROM dbo.skillHistory
    GROUP BY user_ID_PK, skill_ID_PK
) t ON h.user_ID_PK = t.user_ID_PK
    AND h.skill_ID_PK = t.skill_ID_PK
    AND h.Skill_Level = t.max_skill_level;

注意：该兼容写法在同用户同技能存在多条相同最高等级记录时，会返回所有匹配行，需要只取单条的话可以追加一层最大记录时间的关联条件过滤。

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