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

SELECT uv.video_id, ROUND(SUM(IF((uv.end_time - uv.start_time) >= vi.duration, 1, 0)) / COUNT(uv.video_id), 3) AS avg_comp_play_rate
FROM tb_user_video_log AS uv LEFT OUTER JOIN tb_video_info AS vi
ON uv.video_id = vi.video_id
WHERE YEAR(uv.start_time) = 2021
GROUP BY uv.video_id
ORDER BY avg_comp_play_rate DESC;

先联结 再过滤 再分组 再聚合 再排序

保留三位小数的函数ROUND(, 3)

SUM(IF((uv.end_time - uv.start_time) >= vi.duration, 1, 0))表示满足条件返回一并加和，即满足条件的个数