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

select tag,concat(avg_play_progress,"%") as avg_play_progress
from(
select tag,round(avg(case when (timestampdiff(second,start_time,end_time) >= duration) then 1 else timestampdiff(second,start_time,end_time) / duration end)*100,2) as avg_play_progress

from tb_user_video_log tbg
left join tb_video_info tbo 
on tbg.video_id=tbo.video_id
group by tag
having avg_play_progress > 60
order by avg_play_progress desc
) t1

考察知识点

逻辑函数：用if还是casewhen都可以

计算列平均值avg

% 用格式化函数concat(X,”%“)