题解 | #近一个月发布的视频中热度最高的top3视频#
近一个月发布的视频中热度最高的top3视频
https://www.nowcoder.com/practice/0226c7b2541c41e59c3b8aec588b09ff
# 连接表,求得所需要的字段 # 计算热度相关的因素 # 找出近一个月热度最高的top 3 with month_time as ( select uvl.*, vi.duration, vi.release_time, case when timestampdiff(second, start_time, end_time) >= duration then 1 when timestampdiff(second, start_time, end_time) < duration then 0 end as broadcast_finish #这里需要判断求值 ,记住形使用方法 from tb_user_video_log as uvl join tb_video_info as vi on uvl.video_id = vi.video_id where date_format(end_time, '%Y-%m-%d') between date_add('2021-10-03', interval -29 day) and '2021-10-03' ), hot_value_factor as ( select video_id, count(*) as broadcast_num, sum(broadcast_finish) as broadcast_finish_num, sum(if_like) as like_num, count(comment_id) as comment_num, sum(if_retweet) as retweet_num, max(date_format(end_time, '%Y-%m-%d')) as end_time_max #这里不能直接使用max(end_time),不然计算出来的结果很精确,跟牛客要求不一样 from month_time group by video_id ) select video_id, round((100*round(broadcast_finish_num/broadcast_num, 1) + 5*like_num + 3*comment_num + 2*retweet_num)/(1+timestampdiff(day, end_time_max, '2021-10-03')),0) as hot_index from hot_value_factor order by hot_index desc limit 3