题解 | #平均播放进度大于60%的视频类别#

# 先查询用户信息表中每个用户每个视频对应的播放时长以及播放进展 
select 
tag 
,concat(round(avg(progress)*100,2),"%") as 	avg_play_progress 
from (
select 
a.uid
,a.video_id 
,b.tag 
,timestampdiff(second,start_time,end_time) as time_1 
,b.duration 
,if(timestampdiff(second,start_time,end_time) > duration ,1,timestampdiff(second,start_time,end_time)/duration) as progress 
from 
tb_user_video_log a 
inner join 
tb_video_info b 
on a.video_id = b.video_id ) t1 
group by tag 
having avg(progress) > 0.6 
order by concat(round(avg(progress)*100,2),"%") desc

知识点：

1、时间函数timestampdiff

2、将结果呈现为百分比，concat(xx,"%")