如何结合CASE语句与WHERE条件使用聚合函数计算首次达level5的统计值

核心需求是：针对每个用户，计算从第一条记录到首次出现Level 5的记录（包含该条）范围内的max(level)和sum(hours)；如果用户从未达到Level 5，则计算所有记录的对应值。

实现思路 #

要解决这个问题，关键是先定位每个用户首次出现Level 5的记录位置，然后仅对该位置之前（含）的记录做聚合计算。以下提供两种常用的SQL实现方式，其中第二种直接用到了CASE与SUM/MAX的结合。

方法1：先定位首次Level 5的时间点，再筛选聚合 #

适用于数据有明确时间/排序字段的场景：

-- 第一步：找出每个用户首次达到Level 5的日期
WITH first_level5 AS (
    SELECT 
        user_id,
        MIN(log_date) AS first_l5_date  -- 假设log_date是记录的时间顺序字段
    FROM your_table
    WHERE level = 5
    GROUP BY user_id
)
-- 第二步：筛选出符合范围的记录并聚合
SELECT 
    t.user_id,
    MAX(t.level) AS max_level,
    SUM(t.hours) AS total_hours
FROM your_table t
LEFT JOIN first_level5 fl5 ON t.user_id = fl5.user_id
WHERE 
    -- 逻辑：如果用户有Level 5记录，只取到首次日期；否则取所有记录
    fl5.first_l5_date IS NULL OR t.log_date <= fl5.first_l5_date
GROUP BY t.user_id;

方法2：用窗口函数标记范围，结合CASE做聚合 #

这种方式直接在SUM和MAX中用CASE判断是否属于目标范围，更贴合你提到的CASE结合聚合函数的需求：

SELECT 
    user_id,
    -- 仅对首次Level 5之前的记录取最大等级
    MAX(CASE WHEN row_num <= first_l5_row THEN level END) AS max_level,
    -- 仅对首次Level 5之前的记录求和时长
    SUM(CASE WHEN row_num <= first_l5_row THEN hours ELSE 0 END) AS total_hours
FROM (
    SELECT 
        *,
        -- 给每个用户的记录按时间排序编行号
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY log_date) AS row_num,
        -- 获取首次出现Level 5的行号；如果没有则用总行数（确保包含所有记录）
        COALESCE(
            MIN(CASE WHEN level = 5 THEN row_num END) OVER (PARTITION BY user_id),
            COUNT(*) OVER (PARTITION BY user_id)
        ) AS first_l5_row
    FROM your_table
) t
GROUP BY user_id;

关键逻辑解释：

1. 内层子查询：
   • row_num：给每个用户的记录按时间顺序编号，确保能按顺序定位记录。
   • first_l5_row：用窗口函数找出每个用户首次出现Level 5的行号；如果用户从未达到Level 5，就用该用户的总记录数作为边界，保证所有记录都被纳入计算。
2. 外层聚合：
   • CASE WHEN row_num <= first_l5_row THEN ...：仅对首次Level 5之前（含）的记录做计算，不符合条件的记录在SUM中被设为0，在MAX中被设为NULL（不影响最终结果）。

示例验证 #

假设输入数据如下：

执行上述SQL后，最终结果为：

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