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

select 
# uid,
video_id,
# timestampdiff(second,start_time,end_time),
# duration,
round(avg(if(timestampdiff(second,start_time,end_time)>=duration,1,0)),3)
from tb_user_video_log t1
left join tb_video_info
using(video_id)
where substr(start_time,1,4)=2021
group by video_id
order by round(avg(if(timestampdiff(second,start_time,end_time)>=duration,1,0)),3) desc