题解 | 统计每个班级的关键指标

-- 现通过学习日志表统计学习日志去重人数，至少完成一节的人数，总学习分钟
with temp as (
    select
        class_id,
        count(distinct student_id) as learners_active_m,
        count(distinct case when finished_flag = 1 then student_id end) as finishers_m,
        sum(watch_minutes) as total_minutes_m
        from study_logs_
        where log_ts >= '2024-08-01' and log_ts < '2024-09-01'
        group by class_id
),

-- 统计报名表中的报名人数
temp1 as (
    select
        class_id,
        count(distinct student_id) as learners_enrolled
        from course_enroll_
        group by class_id
),

-- 将两张表中的数据合并
temp2 as (
    select 
        c.class_id,
        c.course_id,
        c.teacher_id,
        coalesce(tp1.learners_enrolled , 0) as learners_enrolled,
        coalesce(tp.learners_active_m , 0) as learners_active_m,
        coalesce(tp.finishers_m , 0) as finishers_m,
        coalesce(tp.total_minutes_m, 0) as total_minutes_m,
        ROUND(
            CASE 
                WHEN COALESCE(tp.learners_active_m, 0) = 0 THEN 0.00
                ELSE COALESCE(tp.finishers_m, 0) / CAST(tp.learners_active_m AS DECIMAL(10,2))
            END, 2
        ) AS completion_rate,
        ROUND(
            CASE 
                WHEN COALESCE(tp.learners_active_m, 0) = 0 THEN 0.00
                ELSE COALESCE(tp.total_minutes_m, 0) / CAST(tp.learners_active_m AS DECIMAL(10,2))
            END, 2
        ) AS avg_minutes_per_active
        from course_class_ c 
        left join temp tp
        on tp.class_id = c.class_id
        left join temp1 tp1
        on tp1.class_id = c.class_id

)
-- 4. 最终查询：使用窗口函数排名并排序
SELECT 
    class_id,
    course_id,
    teacher_id,
    learners_enrolled,
    learners_active_m,
    finishers_m,
    completion_rate,
    total_minutes_m,
    avg_minutes_per_active,
    -- 窗口函数：在同一 course_id 内按 avg_minutes_per_active 降序排名
    RANK() OVER (PARTITION BY course_id ORDER BY avg_minutes_per_active DESC) AS rank_in_course
FROM temp2
-- 排序：先按 course_id, 再按 rank_in_course, 再按 class_id 升序
ORDER BY 
    course_id ASC,
    rank_in_course ASC,
    class_id ASC;