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

SUM: 某行所有数之和

COUNT：总行数（忽略NULL）

-- 条件： year()=2021，三位小数round，降序desc
-- 完播率：sum(if(end_time-start_time >= duration,1,0)) / count(start_time)
-- 多表链接 using(video_id)

SELECT
a.video_id,
round(sum(if(end_time-start_time >= duration,1,0)) / count(start_time),3) as avg_comp_play_rate
FROM tb_user_video_log a
LEFT JOIN tb_video_info b USING(video_id)
WHERE year(start_time) = 2021
GROUP BY a.video_id
ORDER BY avg_comp_play_rate DESC;