题解 | #各个视频的平均完播率#
平均播放进度大于60%的视频类别
http://www.nowcoder.com/practice/c60242566ad94bc29959de0cdc6d95ef
注意时间相减用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;
