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

# SELECT video_id

#     ,ROUND((100*finish_rate

#     +5*if_like_cnt

#     +3*comment_id_cnt

#     +2*if_retweet_cnt)/(1+no_play_day),0) AS hot_index

# FROM (

# SELECT video_id

#     ,SUM(IF(TIMESTAMPDIFF(SECOND,start_time,end_time)>=duration,1,0))/count(start_time)  AS  finish_rate

#     ,SUM(if_like) if_like_cnt

#     ,COUNT(comment_id) comment_id_cnt

#     ,SUM(if_retweet) if_retweet_cnt

#     ,datediff((SELECT DATE(MAX(start_time)) FROM tb_user_video_log),max(end_time)) AS no_play_day

# FROM tb_video_info

# JOIN tb_user_video_log USING(video_id)

# WHERE DATE(start_time)>=(

#     SELECT DATE_SUB(DATE(MAX(start_time)),INTERVAL 29 DAY)

#     FROM tb_user_video_log)

# GROUP BY video_id

# )t

# limit 3

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

# 注：

# 热度=(a*视频完播率+b*点赞数+c*评论数+d*转发数)*新鲜度；

# 新鲜度=1/(最近无播放天数+1)；

# 当前配置的参数a,b,c,d分别为100、5、3、2。

# 最近播放日期以end_time-结束观看时间为准，假设为T，则最近一个月按[T-29, T]闭区间统计。

# 结果中热度保留为整数，并按热度降序排序。

SELECT video_id

    ,round((100*fullplay

    +5*like_cnt

    +3*comment_id_cnt

    +2*retweet_cnt)/noplay,0) hot_index

FROM (

SELECT video_id

    ,sum(if(TIMESTAMPDIFF(SECOND,start_time,end_time)>=duration,1,0))/count(start_time) fullplay

    ,sum(if_like) like_cnt

    ,count(comment_id) comment_id_cnt

    ,sum(if_retweet) retweet_cnt

    ,DATEDIFF((SELECT MAX(DATE(start_time))FROM tb_user_video_log),MAX(DATE(end_time)))+1 noplay

FROM tb_user_video_log

JOIN tb_video_info USING(video_id)

WHERE DATE_ADD(DATE(start_time),INTERVAL 29 DAY)>=(

SELECT MAX(DATE(start_time))

FROM tb_user_video_log)

group by video_id)t

group by video_id