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

select video_id, round(sum(if(a >= duration, 1, 0)) / count(video_id), 3) avg_comp_play_rate from ( select t.video_id, timestampdiff(second, t.start_time, t.end_time) as "a", tt.duration from tb_user_video_log t, tb_video_info tt where t.video_id = tt.video_id and date_format(t.start_time, '%Y') = 2021 and date_format(t.end_time, '%Y') = 2021 ) z group by video_id order by avg_comp_play_rate desc