大数据SQL题46 出勤率问题

原题链接:http://practice.atguigu.cn/#/question/46/desc?qType=SQL

题目需求

现有用户出勤表(user_login)如下。

user_id(用户id)course_id(课程id)login_in(登录时间)login_out(登出时间)
112022-06-02 09:08:242022-06-02 10:09:36
112022-06-02 11:07:242022-06-02 11:44:21
122022-06-02 13:50:242022-06-02 14:21:50
222022-06-02 13:50:102022-06-02 15:30:20

课程报名表(course_apply)如下。

course_id(课程id)course_name(课程名称)user_id(用户id)
1java[1,2,3,4,5,6]
2大数据[1,2,3,6]
3前端[2,3,4,5]

注:出勤率指用户看直播时间超过40分钟,求出每个课程的出勤率(结果保留两位小数)。

期望结果如下:

course_id(课程id)adr<decimal(16,2)>(出勤率)
10.33
20.50
30.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

  • 9
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值