select tag, concat(round(avg(rate)*100,2),'%') from( select t2.tag, (if((unix_timestamp(t1.end_time)-unix_timestamp(t1.start_time))>t2.duration,t2.duration,(unix_timestamp(t1.end_time)-unix_timestamp(t1.start_time))))/t2.duration rate from tb_user_video_log t1 left join tb_video_info t2 on t...

select t1.video_id, round(sum(if(unix_timestamp(t1.end_time)-unix_timestamp(t1.start_time)>=t2.duration,1,0))/count(*),3) rate from tb_user_video_log t1 left join tb_video_info t2 on t1.video_id=t2.video_id where t1.start_time between '2021-01-01' and '2021=12-31' group by t1.video_id order by ...