Hive sql 每天场景题4646、现有用户出勤表（user_login）如下。

104 阅读 0 评论 69 点赞

我是靠谱客的博主风趣信封，最近开发中收集的这篇文章主要介绍Hive sql 每天场景题4646、现有用户出勤表（user_login）如下。，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

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 <int> (课程id)	adr <decimal(16,2)> (出勤率)
1	0.33
2	0.50
3	0.25

代码

with t1 as (
select
-- 用户 课程 出勤人数
course_id
,sum(if(user_course_time>=60*40,1,0)) adr_nums
from (
select
-- 用户 课程 登录总时间
user_id
,course_id
,sum(unix_timestamp(login_out)-unix_timestamp(login_in))as user_course_time
from user_login
group by
user_id
,course_id
)a
group by course_id
)
select
t.course_id
,cast(t1.adr_nums/size(t.user_id) as decimal(10,2)) as adr
from course_apply t
left join t1 on t.course_id=t1.course_id