市场网站建设泉州网站设计
文章目录
- 题目需求
- 实现一
- 题目来源
题目需求
现有用户出勤表（user_login）如下。
| user_id (用户id) | course_id (课程id) | login_in （登录时间） | login_out （登出时间） |
|---|---|---|---|
| 1 | 1 | 2022-06-02 09:08:24 | 2022-06-02 10:09:36 |
| 1 | 1 | 2022-06-02 11:07:24 | 2022-06-02 11:44:21 |
| 1 | 2 | 2022-06-02 13:50:24 | 2022-06-02 14:21:50 |
| 2 | 2 | 2022-06-02 13:50:10 | 2022-06-02 15:30:20 |
课程报名表（course_apply）如下。
| course_id (课程id) | course_name (课程名称) | user_id (用户id) |
|---|---|---|
| 1 | java | [1,2,3,4,5,6] |
| 2 | 大数据 | [1,2,3,6] |
| 3 | 前端 | [2,3,4,5] |
注：出勤率指用户看直播时间超过40分钟，求出每个课程的出勤率（结果保留两位小数）。
期望结果如下（截取部分）：
| course_id (课程id) | adr <decimal(16,2)> (出勤率) |
|---|---|
| 1 | 0.33 |
| 2 | 0.50 |
| 3 | 0.25 |
实现一
with course_apply_num as (-- 1) 计算出每个课程的报名人数select course_id,size(user_id) as all_numfrom course_apply
),attendance_num as (-- 3) 计算每个课程中 出勤的人数select course_id,sum(if(study_time > 40 * 60, 1, 0)) as attendancefrom (-- 2) 计算每个用户在每个课程中总的学习时间(单位s)select user_id,course_id,-- 10位数的时间戳是以秒为单位；13位数的时间戳是以毫秒为单位。sum(unix_timestamp(login_out) - unix_timestamp(login_in)) as study_timefrom user_logingroup by user_id, course_id) t1group by course_id)select course_apply_num.course_id,cast((attendance / all_num) as decimal(16, 2)) as adr
from course_apply_numjoin attendance_numon course_apply_num.course_id = attendance_num.course_id;
题目来源
http://practice.atguigu.cn/#/question/46/desc?qType=SQL