1、连接两表查询所有视频的播放时长,
2、根据播放进度定义输出每个视频的播放进度作为临时表
3、在临时表中查询分类视频的平均播放进度,并输出所有大于60的
4、因为带有%无法比较大小,所以这里再将上述表中的输出数据用%连接得到答案
with t2 as (select tag,case when playtime >= duration then 100 else playtime*100/duration end as progress from ( select a.video_id,tag,duration,timestampdiff(second,start_time,end_time) as playtime from tb_user_video_log a join tb_video_info b on a.video_id = b.video_id) t1) select tag,concat(avg_play_progress,'%') from ( select tag,round(avg(progress),2) as avg_play_progress from t2 group by tag having avg_play_progress > 60) t3 order by avg_play_progress desc