PostgreSQL如何查询每个用户value最大值对应的所有category

需求为查询业务表中每个用户（对应id字段）下value取最大值的所有category记录。

业务表样例数据 #

预期查询结果 #

实现SQL #

方案1：使用RANK()窗口函数（推荐，适配并列最大值场景） #

该方案可以完美处理同一个用户下多个category并列最大值的场景，性能表现也更优：

SELECT id, category
FROM (
    SELECT 
        id,
        category,
        -- 按用户id分组，组内按value倒序排名，相同value排名一致
        RANK() OVER (PARTITION BY id ORDER BY value DESC) AS rk
    FROM 你的业务表名 -- 替换为实际表名
) t
WHERE rk = 1;

方案2：关联子查询实现 #

如果不习惯使用窗口函数，也可以用先求每组最大值再关联的方式实现：

SELECT t1.id, t1.category
FROM 你的业务表名 t1 -- 替换为实际表名
INNER JOIN (
    -- 先查询每个用户的最大value值
    SELECT id, MAX(value) AS max_val
    FROM 你的业务表名 -- 替换为实际表名
    GROUP BY id
) t2 ON t1.id = t2.id AND t1.value = t2.max_val;

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