原题链接:http://practice.atguigu.cn/#/question/46/desc?qType=SQL
题目需求
现有用户出勤表(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 |
解题思路
SELECT t1.course_id,
cast(SUM(t2.is_attended)/COUNT(t1.user_id) AS decimal(16,2)) AS adr
FROM
(
SELECT course_id,
uuser_id AS user_id
FROM course_apply LATERAL VIEW EXPLODE(user_id) tmp AS uuser_id
) t1
LEFT JOIN
(
SELECT user_id,
course_id,
IF(SUM(unix_timestamp(login_out) - unix_timestamp(login_in))/60 > 40,1,0) AS is_attended
FROM user_login
GROUP BY user_id,
course_id
) t2
ON t1.course_id = t2.course_id AND t1.user_id = t2.user_id
GROUP BY t1.course_id