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

平均播放进度大于60%的视频类别:

• 开始想了半天, 代码也写出来了, 但是测试用例死活就是通过不了

select vi.tag,
    concat(round(avg(
    if((vl.end_time - vl.start_time) > vi.duration, 1,  (vl.end_time - vl.start_time)/vi.duration)
    ) * 100, 2), '%') as avg_play_progress
from tb_user_video_log vl
join tb_video_info vi on vl.video_id=vi.video_id
group by vi.tag
having avg(
    if((vl.end_time - vl.start_time) > vi.duration, 1,  (vl.end_time - vl.start_time)/vi.duration)
    ) > 0.6
order by avg_play_progress desc

• 这样的到的测试用例死活过不去, 但是逻辑上又是完全没问题的

换成timestampdiff后, 直接就通过了, 求万能的牛友解惑.

关于上代码vl.end_time - vl.start_time我测试过, 是可以正常相减的

select vi.tag,
    concat(round(avg(
    if(timestampdiff(second, vl.start_time, vl.end_time) > vi.duration, 1,  timestampdiff(second, vl.start_time, vl.end_time) / vi.duration)
    ) * 100, 2), '%') as avg_play_progress
from tb_user_video_log vl
join tb_video_info vi on vl.video_id=vi.video_id
group by vi.tag
having avg(
    if(timestampdiff(second, vl.start_time, vl.end_time) > vi.duration, 1,  timestampdiff(second, vl.start_time, vl.end_time) / vi.duration)
    ) > 0.6
order by avg_play_progress desc