题解 | #近一个月发布的视频中热度最高的top3视频#

# 拆解问题，一步一步列出后再合并，尽可能不连接多表（找出一个主表）；
# 一旦连接就必须要有on，有on就影响聚合函数的使用；
# !DATEDIFF(t1,t2) 是t1-t2
SELECT video_id,
ROUND((100* (finish_cnt / cnt) + 5* like_cnt + 3* comment_cnt + 2* retweet_cnt) / (day_cnt +1),0) hot_index
FROM
    ( 
    SELECT b.video_id
    ,SUM(if_like) like_cnt
    ,SUM(if_retweet) retweet_cnt
    ,COUNT(comment_id) comment_cnt
    ,DATEDIFF((SELECT DATE(MAX(end_time)) FROM tb_user_video_log), DATE(MAX(b.end_time))) day_cnt 
    #无播放天数: 当前日期 与 某个视频最近播放日期的时间差,这里不能相减
    ,COUNT(b.id) cnt # 每个视频被播放次数
    ,SUM(CASE WHEN(TIMESTAMPDIFF(SECOND,b.start_time,b.end_time) >= a.duration) THEN 1 ELSE 0 END) finish_cnt 
        # 每个视频完成播放的次数,就算是0也要计算; !记得是sum不是count
    FROM tb_video_info a JOIN tb_user_video_log b
    ON a.video_id = b.video_id
    WHERE DATEDIFF((SELECT DATE(MAX(end_time)) FROM tb_user_video_log), #当前日期 #要在select里date，why？？？
                   DATE(a.release_time)
                  ) BETWEEN 0 AND 29 #发布日期与当前日期的差值：近一个月发布
    GROUP BY b.video_id 
    ) t1
ORDER BY hot_index DESC
LIMIT 3