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

select video_id, 
round((100 * comp_play_rate + 5 * num_like + 3 * num_comment + 2 * num_retweet)
      * fresh_rate, 0) as hot_index
from(
    select V.video_id,
    1 / (datediff((select max(end_time) from tb_user_video_log), max(U.end_time)) + 1) as fresh_rate,
    avg(if(timestampdiff(second, U.start_time, U.end_time) >= duration, 1, 0)) as comp_play_rate,
    # U.end_time - U.start_time 会报错
    sum(if_like) as num_like,
    count(comment_id) as num_comment,
    sum(if_retweet) as num_retweet
    from tb_user_video_log as U
    left join tb_video_info as V
    on U.video_id = V.video_id
    where datediff((select max(end_time) from tb_user_video_log), V.release_time) <= 29
    group by V.video_id
) as play_info
order by hot_index desc
limit 3

select video_id,
        round((100 * comp_play_rate + 5 * num_like + 3 * num_comment + 2 * num_retweet)
              * fresh_rate, 0) as hot_index
from
(
select video_id,
        1/(DATEDIFF((select max(end_time) from tb_user_video_log), max(end_time))+1),0) as fresh_rate,
        avg(if(timestampdiff(second,start_time,end_time)>=duration,1,0)) as comp_play_rate,
        sum(if_like) as num_like,
        count(comment_id) as num_comment,
        sum(if_retweet) as num_retweet
from tb_user_video_log uv join tb_video_info vi using(video_id)
where datediff(date(select max(end_time)),release_time)<=29
group by tag
) a
order by hot_index desc
limit 3