【Hive SQL 每日一题】在线课程学生行为数据分析

需求描述

假设你是一位数据分析师，负责分析某在线学习平台的学生行为数据，平台上有多个课程，学生可以在不同的日期参加不同的课程，请你完成相关业务分析，帮助平台优化课程内容和学生学习体验。

数据准备

我们有两张表，表的字段信息如下：

students 表

记录学生的信息

字段名	字段类型	备注
student_id	int	学生ID
name	string	学生姓名
age	int	学生年龄
gender	string	学生性别

示例数据：

student_id	name	age	gender
1	‘Alice’	23	‘F’
2	‘Bob’	22	‘M’
3	‘Cathy’	24	‘F’
4	‘David’	23	‘M’
5	‘Eve’	25	‘F’

course_activity 表

记录学生的在线学习课程的数据

字段名	字段类型	备注
activity_id	int	活动ID
student_id	int	学生ID
course_id	int	课程ID
activity_date	string	活动日期
duration	int	学习时长（分钟）

示例数据：

activity_id	student_id	course_id	activity_date	duration
101	1	1001	‘2024-01-01’	30
102	1	1002	‘2024-01-01’	45
103	2	1001	‘2024-01-02’	20
104	3	1001	‘2024-01-03’	25
105	3	1003	‘2024-01-04’	60
106	4	1002	‘2024-01-02’	40
107	5	1001	‘2024-01-03’	50
108	5	1002	‘2024-01-04’	30

数据集 SQL 下载

• 在线课程学生行为数据分析-数据集

需求分析与实现

1.每个学生参加的课程数量

select
    student_id,
    count(distinct course_id) course_num
from
    course_activity
group by
    student_id
order by
    student_id;

解题思路

1. 按学生ID进行分组，统计课程ID的数量；

2. 在统计时需要去重，因为学生在学习同一门课程时会存在多条记录。

2.每个课程的总学习时长

select
    course_id,
    sum(duration) total_minutes
from
    course_activity
group by
    course_id
order by
    course_id;

解题思路

• 按课程ID进行分组，根据学习时长累加求和。

3.按性别分组的平均学习时长

select
    gender,
    cast(gender_duration / gender_count as decimal(6,0)) avg_duration
from
    (select
        gender,
        sum(duration) gender_duration,
        count(distinct ca.student_id) gender_count
    from
        course_activity ca
    join
        students si
    on
        si.student_id = ca.student_id
    group by
        gender)t1;

解题思路

• 根据学生ID对两个表进行 join 操作；

• 按学生性别进行分组，对学习时长累加求和，并统计学生数量；

• 计算每组的平均学习时长。

4.每个学生首次参加的课程及其日期

select
    student_id,
    course_id,
    activity_date
from
    (select
        student_id,
        course_id,
        activity_date,
        min(activity_date) over(partition by student_id) min_activity_date
    from
        course_activity)t1
where
    activity_date = min_activity_date
order by
    student_id;

解题思路

• 利用窗口函数分组取每名学生最早的学习日期；

• 通过获取到的日期进行等值过滤，最终得到结果。

注意，在这里会出现一名学生首日参加多条数据的情况，但由于时间字段没有记录时分秒，无法界定哪条数据先产生，所以这里将首日的数据都进行了保留。如果想要仅保留一条数据，可以把 min 换成 rank 窗口排序，然后将过滤条件设置为 rank=1 即可。

5.每个学生最近一次参加课程的持续时间

select
    student_id,
    duration
from
    (select
        student_id,
        activity_date,
        duration,
        max(activity_date) over(partition by student_id) max_activity_date
    from
        course_activity)t1
where
    activity_date = max_activity_date
order by
    student_id;

解题思路

• 利用窗口函数分组取每名学生距今最近的学习日期；

• 通过获取到的日期进行等值过滤，最终得到结果。

这个需求和上面的需求类似，反过来而已。

6.活跃度最高的课程（按参与学生人数计）

select
    course_id,
    stu_count
from
    (select
        course_id,
        count(distinct student_id) stu_count
    from
        course_activity
    group by
        course_id)t1
order by
    stu_count desc
limit 1;

解题思路

• 按课程ID分组去重统计学习该课的人数；

• 通过全局降序排列，取前 1 条数据，获取到活跃度最高的课程。