主要信息

• 课程表course_tb如下（其中course_id代表课程编号，course_name表示课程名称）；行为表behavior_tb如下（其中user_id表示用户编号、if_sign表示是否报名、course_id代表课程编号）；上课情况表attend_tb如下（其中user_id表示用户编号、course_id代表课程编号、in_datetime表示进入直播间的时间、out_datetime表示离开直播间的时间）
• 请你统计每个科目的出勤率（attend_rate(%)，结果保留两位小数），出勤率=出勤（在线时长10分钟及以上）人数 / 报名人数，输出结果按course_id升序排序，以上数据的输出结果如下： ​

问题拆解

​

总体思路

• 出勤率=出勤（在线时长10分钟及以上）人数 / 报名人数

具体实现

• 需要返回course_name，故需要通过course_id连接course_tb和behavior_tb，取出course_name
• 出勤（在线时长10分钟及以上）人数 = 去重用户数，过滤出符合在线时长的人数（使用UNIX_TIMESTAMP转化out_datetime、in_datetime作差后的分钟数>=10min）
• 报名人数 = if_sign为1的去重用户数
• 返回course_id、course_name、出勤人数/报名人数 * 100 最后取两位有效数字

考点：子查询、join、order by、时间函数的使用UNIX_TIMESTAMP

SELECT course_id, course_name
	, round(attend_num / sign_num * 100, 2) AS "attend_rate(%)"
FROM (
	SELECT course_id, count(DISTINCT user_id) AS attend_num
	FROM attend_tb
	WHERE (UNIX_TIMESTAMP(out_datetime) - UNIX_TIMESTAMP(in_datetime)) / 60 >= 10
	GROUP BY course_id
) t1
	LEFT JOIN (
		SELECT course_id, count(DISTINCT user_id) AS sign_num
		FROM behavior_tb
		WHERE if_sign = 1
		GROUP BY course_id
	) t2 USING (course_id)
	LEFT JOIN course_tb USING (course_id)
ORDER BY course_id;