【背景】 在线教育平台需要按月评估各班级的活跃学习与完课情况，用于课后督学与教师考核。请你基于“班级信息”“课程报名”“学习日志”三张表，统计目标月（2024-08）每个班级的关键指标与在同课程内的排名。 【原始表】 1)course_class_（班级信息） class_idINT 主键，0 course_idINT 非空 teacher_idINT 非空 start_dateDATE 非空 end_dateDATE NULL，可为空（表示在读） 2)course_enroll_（报名记录） student_idINT 非空 class_idINT 非空 enroll_dateDATE 非空 说明：同一学生可报名多个班；同一班同一学生只出现一次 3)study_logs_（学习日志） student_idINT 非空 class_idINT 非空 lesson_idINT 非空 watch_minutesINT 非负 finished_flagTINYINT(1) 非空（1=本条日志对应课节完成） log_tsDATETIME 非空 【要求】 以月份 2024-08 为统计窗口（含 2024-08-01 ～ 2024-08-31），输出每个班级一行： class_id，course_id，teacher_id learners_enrolled：报名人数（course_enroll_） learners_active_m：当月有学习日志的去重人数（study_logs_） finishers_m：当月至少完成过1节课的去重人数（COUNT DISTINCT CASE WHEN finished_flag=1 THEN student_id END） completion_rate：当月完课率 =finishers_mlearners_active_m，活跃为0时记0，保留两位小数 total_minutes_m：当月总学习分钟数（SUM watch_minutes） avg_minutes_per_active：人均分钟 =total_minutes_mlearners_active_m（活跃为0时记0），保留两位小数 rank_in_course：在同一course_id内按avg_minutes_per_active降序排名（使用 RANK()） 排序：先按course_id，再按rank_in_course，再按class_id升序。 要求使用到全部三张表；需要用到分组聚合、条件去重、日期过滤、窗口函数与空值处理。 【示例输入】 course_class_ +----------+-----------+------------+------------+ class_id course_id teacher_id start_date +----------+-----------+------------+------------+ 101 1 9001 2024-07-01 102 1 9002 2024-07-10 201 2 9010 2024-06-01 +----------+-----------+------------+------------+ course_enroll_ +------------+----------+-------------+ student_id class_id enroll_date +------------+----------+-------------+ 1 101 2024-07-01 2 101 2024-07-01 3 101 2024-07-02 4 101 2024-07-02 5 101 2024-07-03 6 102 2024-07-10 7 102 2024-07-10 8 102 2024-07-12 9 201 2024-06-03 10 201 2024-06-05 11 201 2024-06-08 12 201 2024-06-09 +------------+----------+-------------+ study_logs_ +------------+----------+-----------+---------------+---------------+---------------------+ student_id class_id lesson_id watch_minutes finished_flag log_ts +------------+----------+-----------+---------------+---------------+---------------------+ 1 101 1 50 1 2024-08-02 10:00:00 1 101 2 40 0 2024-08-05 11:00:00 2 101 1 30 0 2024-08-03 10:00:00 3 101 1 60 1 2024-08-04 09:00:00 4 101 1 20 0 2024-08-06 09:00:00 6 102 1 30 1 2024-08-03 09:00:00 7 102 1 20 0 2024-08-04 09:00:00 +------------+----------+-----------+---------------+---------------+---------------------+ 【示例输出】 +----------+-----------+------------+-------------------+-------------------+-------------+-----------------+------------------------+----------------+ class_id course_id teacher_id learners_enrolled learners_active_m finishers_m completion_rate total_minutes_m avg_minutes_per_active rank_in_course +----------+-----------+------------+-------------------+-------------------+-------------+-----------------+------------------------+------------------------+---------------+ 101 1 9001 5 4 2 0.50 200 50.00 1 102 1 9002 3 2 1 0.50 50 25.00 2 201 2 9010 4 0 0 0.00 0 0.00 1 +----------+-----------+------------+-------------------+-------------------+-------------+-----------------+------------------------+------------------------+---------------+
示例1

输入

DROP TABLE IF EXISTS study_logs_;
DROP TABLE IF EXISTS course_enroll_;
DROP TABLE IF EXISTS course_class_;

CREATE TABLE course_class_(
  class_id INT PRIMARY KEY,
  course_id INT NOT NULL,
  teacher_id INT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NULL
);

CREATE TABLE course_enroll_(
  student_id INT NOT NULL,
  class_id INT NOT NULL,
  enroll_date DATE NOT NULL,
  PRIMARY KEY(student_id, class_id)
);

CREATE TABLE study_logs_(
  student_id INT NOT NULL,
  class_id INT NOT NULL,
  lesson_id INT NOT NULL,
  watch_minutes INT NOT NULL,
  finished_flag TINYINT(1) NOT NULL,
  log_ts DATETIME NOT NULL
);

INSERT INTO course_class_ VALUES
(101,1,9001,'2024-07-01',NULL),
(102,1,9002,'2024-07-10',NULL),
(201,2,9010,'2024-06-01',NULL);

INSERT INTO course_enroll_ VALUES
(1,101,'2024-07-01'),(2,101,'2024-07-01'),(3,101,'2024-07-02'),
(4,101,'2024-07-02'),(5,101,'2024-07-03'),
(6,102,'2024-07-10'),(7,102,'2024-07-10'),(8,102,'2024-07-12'),
(9,201,'2024-06-03'),(10,201,'2024-06-05'),(11,201,'2024-06-08'),(12,201,'2024-06-09');

INSERT INTO study_logs_ VALUES
(1,101,1,50,1,'2024-08-02 10:00:00'),
(1,101,2,40,0,'2024-08-05 11:00:00'),
(2,101,1,30,0,'2024-08-03 10:00:00'),
(3,101,1,60,1,'2024-08-04 09:00:00'),
(4,101,1,20,0,'2024-08-06 09:00:00'),
(6,102,1,30,1,'2024-08-03 09:00:00'),
(7,102,1,20,0,'2024-08-04 09:00:00');

输出

class_id|course_id|teacher_id|learners_enrolled|learners_active_m|finishers_m|completion_rate|total_minutes_m|avg_minutes_per_active|rank_in_course
101|1|9001|5|4|2|0.50|200|50.00|1
102|1|9002|3|2|1|0.50|50|25.00|2
201|2|9010|4|0|0|0.00|0|0.00|1
加载中...