题解 | #各个视频的平均完播率#

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 语句