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

【场景】：近一个月

【分类】：聚合函数、日期函数、限量查询、date_sub

分析思路

难点：

1.近一个月的数据变化情况

（1）计算当天日期和近一个月的开始日期

[条件]：当天就是最大日期
[使用]：date(end_time)；date(date_sub(end_time,interval 29 day))；limit 1

（2）统计被播放次数，完成播放次数，被点赞次数，评论次数，转发次数，最近无播放天数

[条件]：release_time >= date_min
[使用]：count；sum；min

（3）按照公式查询找出近一个月发布的视频中热度最高的top3视频

[使用]：limit 3

求解代码

方法一：

with 子句

with
    main as(
        #计算当天日期和近一个月的开始日期
        select
            date(end_time) as date_max,
            date(date_sub(end_time,interval 29 day)) as date_min
        from tb_user_video_log
        join tb_video_info using(video_id)
        order by date(end_time) desc
        limit 1
    )
    ,main1 as(
        #统计被播放次数，完成播放次数，被点赞次数，评论次数，转发次数，最近无播放天数
        select 
            a.video_id,
            count(end_time) as look,
            count(if(timestampdiff(second,start_time,end_time) >= duration,1,null)) as finsh_look,
            sum(if_like) as like_cnt,
            count(comment_id) as comment_cnt,
            sum(if_retweet) as retweet_cnt,
            min(timestampdiff(day,date(end_time),date_max)) as date_not
        from tb_user_video_log a,tb_video_info b,main
        where a.video_id = b.video_id
        and release_time >= date_min
        group by video_id
    ) 

#按照公式查询找出近一个月发布的视频中热度最高的top3视频
select
    video_id,
    round((100*(finsh_look/look)+5*like_cnt+3*comment_cnt+2*retweet_cnt)/(date_not+1)) as hot_index
from main1
order by hot_index desc
limit 3

方法二：

from嵌套子查询

#按照公式查询找出近一个月发布的视频中热度最高的top3视频
select
    video_id,
    round((100*(finsh_look/look)+5*like_cnt+3*comment_cnt+2*retweet_cnt)/(date_not+1)) as hot_index
from(
    #统计被播放次数，完成播放次数，被点赞次数，评论次数，转发次数，最近无播放天数
    select 
        a.video_id,
        count(end_time) as look,
        count(if(timestampdiff(second,start_time,end_time) >= duration,1,null)) as finsh_look,
        sum(if_like) as like_cnt,
        count(comment_id) as comment_cnt,
        sum(if_retweet) as retweet_cnt,
        min(timestampdiff(day,date(end_time),date_max)) as date_not
    from tb_user_video_log a,tb_video_info b,(
        #计算当天日期和近一个月的开始日期
        select
            date(end_time) as date_max,
            date(date_sub(end_time,interval 29 day)) as date_min
        from tb_user_video_log
        join tb_video_info using(video_id)
        order by date(end_time) desc
        limit 1
    )main
    where a.video_id = b.video_id
    and release_time >= date_min
    group by video_id
)  main1
order by hot_index desc
limit 3