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

select  
    video_id,
    round((finish_rate+like_cnt+count_cnt+retweet_cnt)*(1/(fresh_cnt+1)) ,0)as hot_index from (
    select
    video_id,
    sum(if(timestampdiff(second,start_time,end_time)>=duration,1,0))*100/count(1) as finish_rate,
    DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)),
    MAX(DATE(end_time))) fresh_cnt,#求出每个video最大的结束日期和整张表最大的结束日期的时间差值
    sum(if_like)*5 as like_cnt,#点赞总数换算成热度
    count(comment_id)*3 count_cnt,#评论总数换算成热度
    sum(if_retweet)*2 retweet_cnt#转发总数换算成热度
from
    tb_user_video_log
     join tb_video_info using (video_id)
where
    date_format (release_time, '%Y-%m-%d') > DATE_SUB((select date_format(max(start_time),'%Y-%m-%d') from tb_user_video_log), INTERVAL 29 DAY)
    # DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)), DATE(release_time)) <= 29
group by video_id
) t1
order by hot_index desc 
limit 3


#  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

本题主要是内容比较难懂，其他部分不怎么困难，可以选择datediff 或者timestampdiff来对一个月的区间进行计算，当然也可以选择date_sub,代码如上