题目：
https://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753?tpId=268&tqId=2285032&ru=/exam/oj&qru=/ta/sql-factory-interview/question-ranking&sourceUrl=%2Fexam%2Foj%3Fpage%3D1%26tab%3DSQL%25E7%25AF%2587%26topicId%3D268
自己思路：
-- 求每个视频的完成播放次数
SELECT
a.video_id,
ROUND(AVG(IF(TIMESTAMPDIFF(SECOND,start_time,end_time)>=duration,1,0))/COUNT(a.video_id),3) AS avg_comp_play_rate
FROM tb_user_video_log a
JOIN tb_video_info b
ON a.video_id=b.video_id
GROUP BY a.video_id -- 求每个视频的总播放次数
# SELECT
# video_id,
# COUNT(video_id) AS COMPLETE_NUM
# FROM tb_user_video_log
# GROUP BY video_id
修改后答案：
SELECT
a.video_id,
ROUND(SUM(IF(TIMESTAMPDIFF(SECOND,start_time,end_time)>=duration,1,0))/COUNT(a.video_id),3) AS avg_comp_play_rate
FROM tb_user_video_log a
JOIN tb_video_info b
ON a.video_id=b.video_id
WHERE
YEAR(start_time)='2021'
AND
TIMESTAMPDIFF(SECOND,start_time,end_time)<>0
GROUP BY a.video_id
ORDER BY avg_comp_play_rate DESC
参考答案/其他思路1
可以用avg代替指标/总和---计算比率，传递参数指标即可
SELECT
a.video_id,
ROUND(AVG(IF(TIMESTAMPDIFF(SECOND,start_time,end_time)>=duration,1,0)),3) AS avg_comp_play_rate
FROM tb_user_video_log a
JOIN tb_video_info b
ON a.video_id=b.video_id
WHERE
YEAR(start_time)='2021'
GROUP BY a.video_id
ORDER BY avg_comp_play_rate DESC
参考答案/其他思路2
可以用CASE WHEN THEN ELSE END
SELECT
a.video_id,
ROUND(AVG(CASE WHEN TIMESTAMPDIFF(SECOND,start_time,end_time)>=duration THEN 1 ELSE 0 END),3) AS avg_comp_play_rate
FROM tb_user_video_log a
JOIN tb_video_info b
ON a.video_id=b.video_id
WHERE
YEAR(start_time)='2021'
GROUP BY a.video_id
ORDER BY avg_comp_play_rate DESC
