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

select
video_id,round((100*视频完播率+5*点赞数+3*评论数+2*转发数)/(最近无播放天数+1),0) 热度
from(select t1.video_id,
SUM(TIMESTAMPDIFF(second, start_time, end_time) >= duration) / COUNT(*)视频完播率,
sum(if_like) 点赞数,
count(comment_id) 评论数,
sum(if_retweet) 转发数,
DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)), MAX(DATE(end_time))) 最近无播放天数
from tb_user_video_log t1 left join tb_video_info t2
on t1.video_id=t2.video_id
where DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)),DATE(release_time)) <= 29
group by t1.video_id
)t3
order by 热度 desc
limit 3

拆解：

一、2表联结

from tb_user_video_log t1 left join tb_video_info t2
on t1.video_id=t2.video_id

（1）近一个月发布的视频

1.找到最近一次发布的视频时间

DATE((SELECT MAX(end_time) FROM tb_user_video_log))

2.计算每一个视频观看记录与最近一次发布的视频时间的差要小于等于29

DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)),DATE(release_time)) <= 29

（2）根据公式计算需要的每一个数值

(100视频完播率+5点赞数+3评论数+2转发数)/(最近无播放天数+1)

1.视频完播率

SUM(TIMESTAMPDIFF(second, start_time, end_time) >= duration) / COUNT(*)

分母代表每个视频总共的播放记录

分子代表统计每个视频播完的记录数，用sum和timestampdiff搭配计算

2.最近无播放天数

DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)), MAX(DATE(end_time))