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

1. 注意点: timestampdiff(format, start, end) format为second, day, hour...都行,

2. 直接时间相减, 个人感觉不太严谨. 尽量避免直接使用时间加减, 容易出问题

select uv.video_id,
    round(sum(if(timestampdiff(second, uv.start_time, uv.end_time) >= vi.duration, 1, 0)) / count(1), 3) as avg_comp_play_rate
from tb_user_video_log uv
join tb_video_info 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