题解 | #平均播放进度大于60%的视频类别#
平均播放进度大于60%的视频类别
https://www.nowcoder.com/practice/c60242566ad94bc29959de0cdc6d95ef
我写的是一个三层select语句，第一层选出需要的数据：
select uid, tl.video_id, tag, duration, timestampdiff(second,start_time,end_time) as duration_a, if((timestampdiff(second,start_time,end_time))>duration, 1, ((timestampdiff(second,start_time,end_time))/duration)) as play_progress from tb_user_video_log tl left join tb_video_info ti on tl.video_id = ti.video_id;第二层计算平均播放进度avg_play_progress1，并选取平均播放进度大于60%的视频类别，将数据按倒序排列：
select tag, avg(play_progress) as avg_play_progress1
from(select uid, tl.video_id, tag, duration, timestampdiff(second, start_time, end_time) as duration_a,
if((timestampdiff(second,start_time,end_time))>duration, 1, ((timestampdiff(second,start_time,end_time))/duration)) as play_progress
from tb_user_video_log tl
left join tb_video_info ti on tl.video_id = ti.video_id
) tt
group by tt.tag
having avg_play_progress1 > 0.6
order by avg_play_progress1 desc;
最后一层按照要求将avg_play_progress以保留两位小数的百分比形式输出：
select tag, concat(round(avg_play_progress1*100,2),'%') as avg_play_progress
from(
select tag, avg(play_progress) as avg_play_progress1
from(
select uid, tl.video_id, tag, duration, timestampdiff(second,start_time,end_time) as duration_a,
if((timestampdiff(second,start_time,end_time))>duration, 1, ((timestampdiff(second,start_time,end_time))/duration)) as play_progress
from tb_user_video_log tl
left join tb_video_info ti on tl.video_id = ti.video_id) tt
group by tt.tag
having avg_play_progress1 > 0.6
order by avg_play_progress1 desc) ttt;
查看11道真题和解析
