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

# 找出近一个月发布的视频中热度最高的top3视频

# 字段：video_id、hot_index
# 热度hot_index = (100*完播率+5*点赞数+3*评论数+2*转发数)*新鲜度
# 新鲜度 = 1/(最近无播放天数 + 1)

# tb1：
# 完播率 =(end_time - start_time) >= duration 则完播
with tb1 as(
    select video_id,date(end_time) as play_date,
    timestampdiff(second,start_time,end_time) as play_duration,
    duration,
    if_like,if(comment_id is not null,1,0) as if_comment,
    if_retweet
    from tb_user_video_log left join tb_video_info using(video_id)
    # 限定时间近一个月发布的视频
    where date(release_time) >= date_sub((select max(date(end_time)) from tb_user_video_log),interval 29 day)

),
# tb2：计算完播率、点赞数、评论数、转发数
tb2 as(
    select video_id,
    count(if(play_duration>=duration,1,null))/count(play_date) as Completion_rate,
    sum(if_like) as likes,sum(if_comment) as comments,sum(if_retweet) as retweets,
    # 新鲜度=1/(最近无播放天数+1)
    # (select max(date(end_time)) from tb_user_video_log)- max(play_date) as no_play_day,
    timestampdiff(day,max(play_date),(select max(play_date) from tb1)) as no_play_day
    from tb1
    group by video_id
)
# 热度hot_index = (100*完播率+5*点赞数+3*评论数+2*转发数)*新鲜度
select video_id,round((100*Completion_rate+5*likes+3*comments+2*retweets)/(1+no_play_day),0) as hot_index
from tb2
order by hot_index desc 
limit 3;
# 函数中的参数整体需要多用括号隔绝，date_sub((select max(date(end_time)) from tb_user_video_log),interval 29 day)

# 注意点：近一个月发布
# timestampdiff(unit,time1,time2)，其中time1<time2
# datediff(date1,date2)，其中date1>date2













# 这个题目真难理解，最近无播放天数 = 整体最近播放天数 - 单视频最近播放天数