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

# 指标：平均播放进度=总播放进度/播放次数
# 播放进度=（播放时长/视频时长）*100%
# 播放时长>=视频时长 播放进度=100% 
# 1.各类视频播放次数
# SELECT tag,count(*)
# from tb_user_video_log tvl
# inner join tb_video_info tvi
# on tvl.video_id=tvi.video_id
# group by tag
# 2.各类视频总播放进度
 # 2.1 因为进行比较或计算的两个字段在不同的表中所以首先要连接两个表
#  SELECT tvl.video_id,start_time,end_time,duration,tag
#  from tb_user_video_log tvl
#  inner join tb_video_info tvi
#  on tvl.video_id=tvi.video_id
 # 2.2 如果播放时长（end_time-start_time）>=视频时长，播放进度=100%,否则播放进度=（播放时长/视频时长）*100%
# SELECT tag
# ,sum(case when (end_time-start_time)>=duration then 1 else (end_time-start_time)/duration end) progress
# from tb_user_video_log tvl
# inner join tb_video_info tvi
# on tvl.video_id=tvi.video_id
# group by tag
# 3.代码整合：
SELECT tag
,concat(round((sum(case when TIMESTAMPDIFF(second, start_time, end_time)>=duration then 1 else TIMESTAMPDIFF(second, start_time, end_time)/duration end)/count(*))*100,2),'%') avg_play_progress
from tb_user_video_log tvl
inner join tb_video_info tvi
on tvl.video_id=tvi.video_id
group by tag
HAVING SUBSTRING_INDEX(AVG_play_progress,"%",1)>60
order by AVG_play_progress desc

易错点：时间类型的时间差不能直接相减，要用TIMESTAMPDIFF函数计算