如何在PieCloudDB中查询最高总分的用户（求简化SQL）

表结构与示例数据 #

users表 #

grade_info表 #

该表记录用户分数变动，add代表加分，reduce代表扣分：

查询需求 #

获取总分最高的用户的id、name和total_grade，若存在多个用户总分相同需全部返回，结果按id升序排列，预期输出如下：

原SQL实现 #

用户最初的实现如下，存在重复计算总分的冗余逻辑：

SELECT u.id, u.name, g.total_grade
FROM users u
JOIN (
    SELECT user_id, SUM(
        CASE WHEN type = 'add' THEN grade_num
             WHEN type = 'reduce' THEN -grade_num
             ELSE 0
        END
    ) AS total_grade
    FROM grade_info
    GROUP BY user_id
) g ON u.id = g.user_id
WHERE g.total_grade = (
    SELECT MAX(total_grade)
    FROM (
        SELECT user_id, SUM(
            CASE WHEN type = 'add' THEN grade_num
                 WHEN type = 'reduce' THEN -grade_num
                 ELSE 0
            END
        ) AS total_grade
        FROM grade_info
        GROUP BY user_id
    ) max_grades
)
ORDER BY u.id;

简洁优化方案 #

利用窗口函数RANK()可以避免重复计算，大幅简化代码：

SELECT id, name, total_grade
FROM (
    SELECT 
        u.id, 
        u.name, 
        SUM(grade_num * CASE WHEN type = 'add' THEN 1 ELSE -1 END) AS total_grade,
        RANK() OVER(ORDER BY SUM(grade_num * CASE WHEN type = 'add' THEN 1 ELSE -1 END) DESC) AS grade_rank
    FROM users u
    JOIN grade_info g ON u.id = g.user_id
    GROUP BY u.id, u.name
) ranked_users
WHERE grade_rank = 1
ORDER BY id;

优化说明 #

1. 仅计算一次用户总分，同时通过RANK()窗口函数直接生成总分排名，排名为1的即为最高分用户
2. 消除了原SQL中嵌套两层子查询计算最高总分的冗余逻辑，代码更紧凑易读
3. 完全满足需求：总分相同的用户全部返回，结果按id升序排列

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