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

# SELECT
#   video_id,
#   ROUND((100 * finished_rate 
#    + 5 * like_cnt 
#    + 3 * comment_count 
#    + 2 * retweet_cnt) / (unfinished_day_cnt + 1)) hot_index
# FROM (
#   SELECT
#     i.video_id,
#     SUM(TIMESTAMPDIFF(second, start_time, end_time) >= duration) / COUNT(*) finished_rate,
#     SUM(if_like = 1) like_cnt,
#     SUM(IF(comment_id IS NOT NULL, 1, 0)) comment_count,
#     SUM(if_retweet = 1) retweet_cnt,
#     DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)), MAX(DATE(end_time))) unfinished_day_cnt
#   FROM tb_video_info i
#   JOIN tb_user_video_log USING(video_id)
#   WHERE DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)), DATE(release_time)) <= 29 
#   GROUP BY 1
# ) t
# ORDER BY 2 DESC LIMIT 3


# 计算视频完播率，点赞数，评论数，转发数 以及新鲜度
WITH temp AS (
    SELECT
    i.video_id,
    # 计算完播率
    sum(TIMESTAMPDIFF(second,start_time,end_time)>=duration)/count(*) finished_rate,
    # 点赞率
    sum(if_like=1) like_cnt,
    # 评论数
    count(comment_id) comment_count,
    # 转发数
    sum(if_retweet =1 ) retweet_cnt,
    # 计算最近播放时间
    DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)),MAX(DATE(end_time))) unfinished_day_cnt
    FROM tb_video_info i
    JOIN tb_user_video_log USING(video_id)
    WHERE DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)),DATE(release_time)) <= 29
    GROUP BY 1
)
SELECT video_id,
    round(
    (100 * finished_rate
    + 5 * like_cnt
    + 3 * comment_count
    + 2 * retweet_cnt) / (unfinished_day_cnt+1)) hot_index
FROM  temp
ORDER BY hot_index DESC LIMIT 3

大佬的思维能力就是强！

注意事项：

主要是时间的问题，因为要算的是近一个月内发布的视频的热度情况，所以在计算相差时间时一定要减的是发布时间；

二是在计算少于30天的情况时，先从查询中找到最大的那个为当前时间，然后再计算该视频与当前时间的差值。