46. 出勤率问题

题目需求

现有用户出勤表（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