-- 计算2021年里有播放记录的每个视频的完播率(结果保留三位小数),并按完播率降序排序
-- 完播率=播放完成数/播放总数
-- 1、过滤2021年播放记录 2、判断是否播放完成,新建playFlag标记是否播放完成 3、根据vedio_id分组统计,播放完成数、播放总数 4、计算完播率,排序
SELECT
c.video_id,
ROUND(SUM(playFlag) / COUNT(c.video_id), 3) complete_rate
FROM
(SELECT
a.video_id,
IF((a.end_time - a.start_time) >= b.duration, 1, 0) playFlag
FROM
(SELECT
*
FROM
tb_user_video_log
WHERE
YEAR(start_time) = '2021') a
INNER JOIN tb_video_info b ON a.video_id = b.video_id) c
GROUP BY c.video_id
ORDER BY complete_rate DESC;