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

明确题意：

找出近一个月发布的视频中热度最高的top3视频。

问题分解：

关联用户-视频互动表和短视频信息表：join tb_video_info tvi on tuvl.video_id = tvi.video_id

筛选近一个月发布的视频：datediff(date((select MAX(end_time) from tb_user_video_log)), date(release_time)) <= 29

计算视频完播率：avg(if(timestampdiff(second,start_time,end_time) >= duration, 1, 0))

计算视频点赞数：sum(if_like)

计算视频评论数：count(comment_id)

计算视频转发数：sum(if_retweet)

计算视频新鲜度：1/(datediff(date((select max(end_time) from tb_user_video_log)),max(date(end_time)))+1)

计算视频热度：(100 * finished_rate + 5 * like_cnt + 3 * comment_cnt + 2 * retweet_cnt) / (no_play_record_day + 1)

筛选热度top3的视频：limit3

细节问题：

热度保留为整数：round(x,0)

按热度降序排序：order by hot_index desc

select video_id,
    round((100 * finished_rate + 5 * like_cnt + 3 * comment_cnt + 2 * retweet_cnt) / (no_play_record_day + 1), 0) hot_index
from(
select tuvl.video_id,
    avg(if(timestampdiff(second,start_time,end_time) >= duration, 1, 0)) finished_rate,
    sum(if_like) like_cnt,
    count(comment_id) comment_cnt,
    sum(if_retweet) retweet_cnt,
    datediff(date((select max(end_time) from tb_user_video_log)),max(date(end_time))) no_play_record_day
from tb_user_video_log tuvl
join tb_video_info tvi on tuvl.video_id = tvi.video_id
where datediff(date((select MAX(end_time) from tb_user_video_log)), date(release_time)) <= 29
group by tuvl.video_id
) t1
order by hot_index desc
limit 3;