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

注意时间相减用TIMASTAMPDIFF函数

• 法一

select tag,concat(round(avg(watch_time*1.0/duration)*100,2),'%') avg_play_progress
from
(select tag,duration,
case when
timestampdiff(second,start_time,end_time)>duration
then duration
else timestampdiff(second,start_time,end_time)
end watch_time
from tb_user_video_log a,tb_video_info b
where a.video_id=b.video_id) ss
group by tag
having avg(watch_time*1.0/duration)>0.6
order by avg_play_progress desc;

• 法二

select tag,concat(round(avg((case when timestampdiff(second,start_time,end_time)>duration then duration else timestampdiff(second,start_time,end_time) end)*1.0/duration)*100,2),'%') avg_play_progress
from tb_user_video_log a,tb_video_info b
where a.video_id=b.video_id
group by tag
having avg((case when timestampdiff(second,start_time,end_time)>duration then duration else timestampdiff(second,start_time,end_time) end)*1.0/duration)>0.6
order by avg_play_progress desc;