hivesql练习_出勤率问题
现有用户出勤表（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 <int> (课程id) | adr <decimal(16,2)> (出勤率) |
|---|---|
| 1 | 0.33 |
| 2 | 0.50 |
| 3 | 0.25 |
解答：
1 with tt as( 2 SELECT 3 ul.course_id, 4 if(unix_timestamp(login_out) - unix_timestamp(login_in) >= 40 * 60, 1, 0) flag, 5 size(ca.user_id) total_user --总用户数 6 FROM 7 user_login ul 8 join course_apply ca on ul.course_id = ca.course_id 9 ) 10 select course_id, cast(sum(flag)/ max(total_user) as decimal(16, 2)) adr 11 from tt 12 group by course_id
