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

with tt as(select t1.uid, t1.video_id, t2.duration, TIMESTAMPDiff(second, t1.start_time, t1.end_time) time_diff from tb_user_video_log t1 left join tb_video_info t2 on t1.video_id=t2.video_id where date(t1.end_time)between '2021-01-01' and '2021-12-31')
select video_id, round(count(case when time_diff>=duration then 1 end)/count(*),3) avg_comp_play_rate from tt group by video_id order by avg_comp_play_rate desc

就还是简单的，先表连接把关键信息连在一起，用TIMESTAMPDiff计算一下播放时间差。再暂存tt表

再对video_id分组，count()聚合函数内部使用case when判断播放时间是否大于视频时长，再计数就可以了