主要信息
- 已知课程表course_tb(course_id代表课程编号,course_name表示课程名称);attend_tb(user_id表示用户编号、course_id代表课程编号、in_datetime表示进入直播间的时间、out_datetime表示离开直播间的时间):
- 统计每个科目的平均观看时长(观看时长定义为离开直播间的时间与进入直播间的时间之差,单位是分钟),输出结果按平均观看时长降序排序,结果保留两位小数。
问题拆解
总体思路
- 观看时长 = 离开直播间的时间 - 进入直播间的时间(分)
具体实现
- 时间相减需要使用UNIX_TIMESTAMP函数把两个时间都转化为unix系统时间作差,由于计算的结果单位为分钟,(UNIX_TIMESTAMP(out_datetime) - UNIX_TIMESTAMP(in_datetime)) / 60为观看时长(分)
- 对观看时长(分)求平均值,AVG函数使用,最后根据平均只保留两位有效数字使用round(x,2),倒序排列 order by x DESC
- 结果中需要返回course_name,使用course_id连接attend_tb和course_tb
考点:UNIX_TIMESTAMP、AVG、ROUND、JOIN GROUP BY、ORDER BY使用
SELECT
course_name,
ROUND(AVG(
(UNIX_TIMESTAMP(out_datetime) - UNIX_TIMESTAMP(in_datetime)) / 60), 2) AS avg_Len
FROM
attend_tb a
LEFT JOIN course_tb c
ON a.course_id = c.course_id
GROUP BY course_name
ORDER BY avg_Len DESC;