SQL2 平均播放进度大于60%的视频类别
SELECT tag,AVG(
CASE WHEN
TIMESTAMPDIFF(SECOND,end_time,start_time)>=duration
THEN
1
ELSE
TIMESTAMPDIFF(SECOND,end_time,start_time)/v.duration
END)播放进度
FROM tb_user_video_log u
LEFT JOIN tb_video_info v
ON u.video_id = v.video_id
GROUP BY v.video_id
为什么我输出的结果是负的,抓耳挠腮
因为start_time和end_time弄反了