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

本文涉及的产品
实时数仓Hologres,5000CU*H 100GB 3个月
大数据开发治理平台 DataWorks,不限时长
检索分析服务 Elasticsearch 版,2核4GB开发者规格 1个月
简介: 该数据分析师任务是分析在线学习平台的学生行为,以优化课程内容和学习体验。提供的数据包括`students`表(含学生ID、姓名、年龄和性别)和`course_activity`表(含活动ID、学生ID、课程ID、活动日期和学习时长)。分析涉及:1) 学生参加的课程数量,2) 课程总学习时长,3) 按性别分组的平均学习时长,4) 学生首次参加的课程及日期,5) 学生最近一次学习的时长,以及6) 参与学生最多的课程。所有查询都使用了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;

image.png

解题思路

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

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

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

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

image.png

解题思路

  • 按课程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;

image.png

解题思路

  • 根据学生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;

image.png

解题思路

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

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

注意,在这里会出现一名学生首日参加多条数据的情况,但由于时间字段没有记录时分秒,无法界定哪条数据先产生,所以这里将首日的数据都进行了保留。如果想要仅保留一条数据,可以把 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;

image.png

解题思路

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

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

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

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;

image.png

解题思路

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

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

相关文章
|
2月前
|
SQL HIVE
【Hive SQL 每日一题】环比增长率、环比增长率、复合增长率
该文介绍了环比增长率、同比增长率和复合增长率的概念及计算公式,并提供了SQL代码示例来计算商品的月度增长率。环比增长率是相邻两期数据的增长率,同比增长率是与去年同期相比的增长率,复合增长率则是连续时间段内平均增长的速率。文章还包含了一组销售数据用于演示如何运用这些增长率进行计算。
|
14天前
|
SQL 分布式计算 关系型数据库
Hadoop-12-Hive 基本介绍 下载安装配置 MariaDB安装 3台云服务Hadoop集群 架构图 对比SQL HQL
Hadoop-12-Hive 基本介绍 下载安装配置 MariaDB安装 3台云服务Hadoop集群 架构图 对比SQL HQL
24 2
|
3天前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用问题之如何使用Flink SQL连接带有Kerberos认证的Hive
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
4天前
|
JSON 数据挖掘 API
在会议系统工程中,Python可以用于多种任务,如网络请求(用于视频会议的连接和会议数据的传输)、数据分析(用于分析会议参与者的行为或会议效果)等。
在会议系统工程中,Python可以用于多种任务,如网络请求(用于视频会议的连接和会议数据的传输)、数据分析(用于分析会议参与者的行为或会议效果)等。
|
2月前
|
SQL HIVE
【Hive SQL】字符串操作函数你真的会用吗?
本文介绍了SQL中判断字符串是否包含子串的几种方法。`IN`函数判断元素是否完全等于给定元素组中的某项,而非包含关系。`INSTR`和`LOCATE`函数返回子串在字符串中首次出现的位置,用于检测是否存在子串。`SUBSTR`则用于提取字符串的子串。`LIKE`用于模糊匹配,常与通配符配合使用。注意`IN`并非用于判断子串包含。
|
2月前
|
SQL BI HIVE
【Hive SQL 每日一题】统计用户留存率
用户留存率是衡量产品成功的关键指标,表示用户在特定时间内持续使用产品的比例。计算公式为留存用户数除以初始用户数。例如,游戏发行后第一天有10000玩家,第七天剩5000人,第一周留存率为50%。提供的SQL代码展示了如何根据用户活动数据统计每天的留存率。需求包括计算系统上线后的每日留存率,以及从第一天开始的累计N日留存率。通过窗口函数`LAG`和`COUNT(DISTINCT user_id)`,可以有效地分析用户留存趋势。
|
2月前
|
SQL HIVE 索引
【Hive SQL 每日一题】行列转换
该文介绍了如何使用SQL进行数据的行列转换。首先展示了行转列的例子,通过创建一个学生成绩表,利用`IF`和`SUM`函数按学生ID分组,将每个学生的各科成绩转换为独立列。然后,文章讲述了列转行的需求,利用`LATERAL VIEW`和`POSEXPLODE`将已转换的表格恢复为原始行格式,通过索引匹配过滤笛卡尔积避免错误结果。此外,还提到了使用`UNION ALL`的另一种列转行方法。
|
2月前
|
SQL HIVE
【Hive SQL 每日一题】分组排名取值
创建了一个名为`sales_data`的测试表,包含商品ID、销售额和销售日期。展示了部分示例数据。接着,提供了三个SQL查询:1) 查找每个商品销售额最高的记录;2) 获取每个商品最近和最远的销售记录;3) 求每个商品距今第二近的销售记录。每个查询都利用了窗口函数来处理数据,并给出了相应的查询结果图。
|
2月前
|
SQL HIVE
【Hive SQL 每日一题】统计用户连续下单的日期区间
该SQL代码用于统计用户连续下单的日期区间。首先按`user_id`和`order_date`分组并去除重复,然后使用`row_number()`标记行号,并通过`date_sub`与行号计算潜在的连续日期。接着按用户ID和计算后的日期分组,排除连续订单数少于2的情况,最后提取连续下单的起始和结束日期。输出结果展示了用户连续下单的日期范围。
|
2月前
|
SQL 关系型数据库 HIVE
【Hive SQL 每日一题】统计最近1天/7天/30天商品的销量
这段内容是关于SQL查询的示例,目标是统计`sales`表中最近1天、7天和30天的商品销量和销售次数。表结构包含`id`、`product_id`、`quantity`和`sale_date`字段。初始查询方法通过三个独立的子查询完成,但效率较低。优化后的查询使用了`lateral view explode`将数据炸裂,通过一次查询同时获取所有所需时间段的数据,提高了效率。示例中展示了优化前后的SQL代码及结果对比。