Postgres 16.5中带Modifier的关卡完成积分聚合查询需求

根据你描述的积分计算规则，以下是符合要求的PostgreSQL 16.5查询语句：

WITH completion_details AS (
    SELECT
        c.user_id,
        c.level_id,
        l.points,
        -- 计算每条完成记录的乘数：full_completion×3，alternate_strategy×2，叠加相乘
        (CASE WHEN c.full_completion THEN 3 ELSE 1 END) * 
        (CASE WHEN c.alternate_strategy THEN 2 ELSE 1 END) AS multiplier,
        c.is_fastest
    FROM completions c
    JOIN levels l ON c.level_id = l.id
),
user_level_agg AS (
    SELECT
        user_id,
        level_id,
        points,
        -- 获取该用户该关卡的最高乘数（优先规则）
        MAX(multiplier) AS highest_multiplier,
        -- 判断是否存在最快完成记录（仅加一次20分）
        BOOL_OR(is_fastest) AS has_fastest_completion
    FROM completion_details
    GROUP BY user_id, level_id, points
)
SELECT
    user_id,
    level_id,
    -- 计算最终积分：基础分×最高乘数 + 20分（如果有最快完成）
    (points * highest_multiplier) + CASE WHEN has_fastest_completion THEN 20 ELSE 0 END AS total_points
FROM user_level_agg
ORDER BY user_id, level_id;

逻辑说明 #

1. completion_details CTE

   • 关联completions和levels表，获取关卡基础积分
   • 按规则计算每条完成记录的乘数：
     • 仅full_completion=true：乘数为3
     • 仅alternate_strategy=true：乘数为2
     • 两者同时为true：乘数为3×2=6
     • 无修饰符：乘数为1

2. user_level_agg CTE

   • 按user_id和level_id分组，聚合得到：
     • 该用户该关卡的最高乘数（符合“优先采用乘数最高的记录”的规则）
     • 是否存在任何is_fastest=true的记录（通过BOOL_OR函数实现，确保仅加一次20分）

3. 最终查询

   • 计算总积分：关卡基础积分 × 最高乘数，若存在最快完成记录则额外加20分
   • 按用户ID和关卡ID排序，方便生成排行榜

示例结果验证 #

针对你提供的测试数据，查询结果如下：

注：关卡3的结果为10×3 + 20 = 50，其中最高乘数来自full_completion=true的记录，且存在最快完成记录故加20分。

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