题解 | #各个视频的平均完播率#
各个视频的平均完播率
https://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
WITH temp AS(
SELECT a.video_id AS video_id,
(end_time - start_time) AS real_dur,
duration
FROM tb_user_video_log a
LEFT JOIN tb_video_info b
ON a.video_id = b.video_id
# WHERE YEAR(a.start_time) = 2021
WHERE YEAR(start_time) = 2021
)
SELECT video_id,
ROUND(AVG(CASE WHEN real_dur >= duration THEN 1 ELSE 0 END), 3) AS avg_comp_play_rate
FROM temp
GROUP BY video_id
ORDER BY avg_comp_play_rate DESC;
理解题意：
2021年：在这一年有播放记录的，返回到表中就是第一个表的start_time或者end_time。（之前理解成了第二个表的release_time，导致一个用例过不去，因为有可能一个视频是20年发布的，但是21年也有播放）
每个视频：表明要按视频分组计算
完播率：根据定义，就是end_time-start_time>=duration时为完播
做法：
为了简化过程，首先创建了一个临时表（CTE）。之前我创建的视图，但是一直报错权限问题，遂放弃。
临时表temp选择了与结果相关的几个字段：vedio_id，通过计算字段得到每个视频的播放时长的real_dur，每个视频的时长duration。自联结和外联结都是可以的，只要能找到对应的就行
之后就是计算完播率了，首先是保留三位小数，使用ROUND；然后计算完播的比例（AVG）；后面接分组和排序即可。
难点：视图或临时表的创建；外联结；case when 语句
