一、sql测试错误
牛客sql题:SQL157 平均播放进度大于60%的视频类别
平均播放进度大于60%的视频类别:
- 代码运行逻辑没错, 但是测试用例通过不了
select tag,
concat(round(avg(case when (end_time-start_time)<=duration then (end_time-start_time)/duration else 1 end)*100,2),'%') as avg_play_progress
from tb_user_video_log as us
join tb_video_info as vd
on us.video_id=vd.video_id
group by tag
having replace(avg_play_progress,'%','') > 60
order by avg_play_progress desc
- 显示:
- 但是将时间戳方法换成timestampdiff后, 就可以通过
select tag,
concat(round(avg(case when timestampdiff(second,start_time,end_time)<=duration then timestampdiff(second,start_time,end_time)/duration else 1 end)*100,2),'%') as avg_play_progress
from tb_user_video_log as us
join tb_video_info as vd
on us.video_id=vd.video_id
group by tag
having replace(avg_play_progress,'%','') > 60
order by avg_play_progress desc
二、原因分析
使用TIMESTAMPDIFF函数与直接使用两个字段进行作差,两种方法存在区别:
1.如果用end_time − start_ time ,那么两个时间戳的时间差是以100为进制。
例如相差1分钟,但查询出来的是却是100。
2.如果用 timestampdiff(second,start_time,end_time),时间差是按正常60为进制。
例如相差1分钟,运算结果就为60。
参考:https://blog.csdn.net/qq_41688840/article/details/123450457