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

此题共包含两张表：

表1：用户-视频互动表tb_user_video_log

表2：短视频信息表tb_video_info

要解决的问题： 找出近一个月发布的视频中热度最高的top3视频。

解题思路： 找到近一个月发布的视频【T-29,T】 依据热度公式，拆解获取各项数据。视频完播率、点赞数、评论数、转发数及最近无播放天数 结果中热度保留为整数，并按热度降序排序。

select video_id, round((finished_rate + like_cnt + comment_count+retweet_cnt)*unfinished_day_cnt，0) hot_index -- ④ 求得最终结果
from(
    select a.video_id video_id,
            100*sum(if(TIMESTAMPDIFF(second,start_time,end_time) - duration >=0, 1, 0)) / count(*) finished_rate,
            5*sum(if_like) like_cnt,
            3*SUM(IF(comment_id IS NOT NULL, 1, 0)) comment_count,
            2*sum(if_retweet) retweet_cnt,
            1 / (DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)), MAX(DATE(end_time)))+1) unfinished_day_cnt 
-- ① 计算各要求指标，这里要额外注意最近无播放记录的计算，利用原表取最近值和分组结果取最近值，巧妙得到最近无播放记录的时间差
    from tb_user_video_log a
    left join tb_video_info b
    on a.video_id = b.video_id
    where DATEDIFF(date((select max(end_time) from tb_user_video_log)), date(release_time)) <= 29
-- ② 最近一个月无播放记录，题目里说要和发布时间作比较，所以这里后面的时间节点是release_time
    group by a.video_id -- ③ 以video_id分组
    ) t1
order by hot_index desc
limit 3 -- ⑤ 排序，取前3