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

# 条件：2021年，则 SUBSTR(start_time,1,4)='2021'
# tb_user_video_log left join tb_video_info，拿到视频时长
# 视频id分组，如果播放时长>=视频时长，则算完播


select video_id
      ,round(sum(case when df>=duration then 1 else 0 end)/count(id),3) as avg_comp_play_rate
from (
select tvl.*
      ,tvi.duration
	  ,timediff(end_time,start_time) as df
from tb_user_video_log as tvl
left join  tb_video_info as tvi
on tvl.video_id=tvi.video_id
where SUBSTR(start_time,1,4)=2021
) as a
group by video_id
order by avg_comp_play_rate desc