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

# # 热度=(a*视频完播率+b*点赞数+c*评论数+d*转发数)*新鲜度；
# hot_rate = sum(a * finish_rate + b * likes_cnt + c * comments_cnt + d * retweets_cnt)
# hot_rate = sum(100 * finish_rate + 5 * likes_cnt + 3 * comments_cnt + 2 * retweets_cnt)

# # 新鲜度 = 1/(最近无播放天数 + 1)

# # 最近播放日期
# datediff(day,start_time,end_time) = 1

# # 计算每个视频的各项指标
# ## 关联用户-视频互动记录和短视频信息表 ：
# join tb_video_info using(video_id)

# ## 每一行追加当前日期列
# left join(
#     select max(date(end_time)) as cur_date from tb_user_video_log
# ) as t_max_date on 1

# ## 按照视频id分组
# group by video_id
# ## 计算各项指标
# ### 播放进度
# avg(if(timestampdiff(second,start_time,end_time)>duration,1,0)) as comp_play_rate
# ### 点赞量 
# sum(if_like) as like_cnt
# ### 评论量
# count(comment_id) as comment_cnt
# ### 转发量
# sum(if_retweet) as retweet_cnt
# ### 最近被播放日期
# max(date(end_time)) as recently_end_date
# ### 发布日期
# max(date(release_time)) as release_date
# ### 当前日期（非分组列，加max避免语法错误
# max(cur_date) as cur_date
# ## 分组后筛选，筛选金30天记录
# having timestampdiff(day,release_date, cur_date)<30
# # 每个视频热度
# (100 * comp_play_rate + 5 * like_cnt + 3 * comment_cnt + 2 8 retweet_cnt) / (timestampdiff(day, recently_end_date, cur_date) + 1) as hot_index

# # 保留整数  
# round(x,0)
# # 取热度top3高的视频
# order by hot_index desc limit 3

SELECT video_id,
    ROUND((100 * comp_play_rate + 5 * like_cnt + 3 * comment_cnt + 2 * retweet_cnt)
        / (TIMESTAMPDIFF(DAY, recently_end_date, cur_date) + 1), 0) as hot_index
FROM (
    SELECT video_id,
        AVG(IF(
            TIMESTAMPDIFF(SECOND, start_time, end_time)>=duration, 1, 0
        )) as comp_play_rate,
        SUM(if_like) as like_cnt,
        COUNT(comment_id) as comment_cnt,
        SUM(if_retweet) as retweet_cnt,
        MAX(DATE(end_time)) as recently_end_date,  -- 最近被播放日期
        MAX(DATE(release_time)) as release_date,  -- 发布日期
        MAX(cur_date) as cur_date  -- 非分组列，加MAX避免语法错误
    FROM tb_user_video_log
    JOIN tb_video_info USING(video_id)
    LEFT JOIN (
        SELECT MAX(DATE(end_time)) as cur_date FROM tb_user_video_log
    ) as t_max_date ON 1
    GROUP BY video_id
    HAVING TIMESTAMPDIFF(DAY, release_date, cur_date) < 30
) as t_video_info
ORDER BY hot_index DESC
LIMIT 3;