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

差点自闭，个人认为这样子用一个with as可以避免嵌套过多，增加可读性，
第一步是先对各个指标拆解并且附加权重，第二步是计算热度值并且排序等等。
注意的点在于表格中的日期都带有时秒分，所以用date()处理去掉时秒分，不然结果就会是错误的。
with a as(
select 
    u.video_id,
    100*avg(
        if(timestampdiff(second, start_time, end_time)>=duration, 1, 0)
        )  as comp_val,
    5*Sum(if_like) as l_val,
    3*count(comment_id)  as c_val,
    # sum(if(comment_id is null,0,1)) * 3 as c_val, # count（字段）会计算非空字段
    2*Sum(if_retweet)  as r_val,
    1/(datediff((SELECT MAX(DATE(end_time)) FROM tb_user_video_log),max(date(end_time)))+1) as f_val
from tb_user_video_log u join tb_video_info i using(video_id) 
where date(release_time)>=date_sub((select date(max(end_time)) from tb_user_video_log),interval 29 day)
group by u.video_id
	)
select 
    video_id,
    round((comp_val+ l_val+ c_val+ r_val)*f_val) as hot_index
from a
order by hot_index desc
limit 3