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

SELECT
    video_id,hot_index
FROM(
    SELECT video_id
        ,round((100*over_rate+5*like_cnt+3*comment_cnt+2*retweet_cnt)/(no_play+1),0) as hot_index
        ,row_number()over(order by (100*over_rate+5*like_cnt+3*comment_cnt+2*retweet_cnt)/(no_play+1) desc) as rn
    FROM(
        SELECT distinct a1.video_id
                ,avg(if(TIMESTAMPDIFF(second,a1.start_time,a1.end_time)>=a2.duration,1,0))over(partition by video_id) as over_rate
                ,sum(if_like)over(partition by video_id) as like_cnt
                ,sum(if(comment_id is not null,1,0))over(partition by video_id) as comment_cnt
                ,sum(if_retweet)over(partition by video_id) as retweet_cnt
                ,datediff(max(date(end_time))over(),max(date(end_time))over(partition by video_id)) as no_play
        FROM tb_user_video_log a1
        JOIN tb_video_info a2 ON a1.video_id=a2.video_id
        WHERE datediff((SELECT max(date(end_time)) FROM tb_user_video_log),date(release_time))<=29 and datediff((SELECT max(date(end_time)) FROM tb_user_video_log),date(end_time))<=29   
    ) temp1
)temp2
WHERE rn<=3