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

select
tmp.video_id,
round((100*tmp.spwbl+5*tmp.dzs+3*tmp.pls+2*tmp.zfs)*(1/(datediff((select max(end_time) from tb_user_video_log),tmp.maxet)+1)), 0) as hot_index
from
(
select
tvi.video_id,
sum(if((unix_timestamp(tuvl.end_time)-unix_timestamp(tuvl.start_time))-tvi.duration>=0,1,0))/count(tuvl.end_time) as spwbl,
sum(tuvl.if_like) as dzs,
count(tuvl.comment_id) as pls,
sum(tuvl.if_retweet) as zfs,
max(tuvl.end_time) as maxet
from
tb_video_info as tvi
inner join
tb_user_video_log as tuvl
on tvi.video_id = tuvl.video_id
where datediff((select max(end_time) from tb_user_video_log), tvi.release_time)<=29
group by tvi.video_id
) as tmp
order by hot_index desc
limit 3

spwbl视频完播率 dzs点赞数 pls评论数 zfs转发数 maxet视频的最后一次结束播放时间 xxd新鲜度

记录过程中犯的错误：

一开始我总结出应当建立一个统计函数的临时表再通过算数来运算出每个视频的热度值，思路是正确的。以下是错误代码。

select
tmp.video_id,
floor((100*tmp.spwbl+5*tmp.dzs+3*tmp.pls+2*tmp.zfs)*tmp.xxd) as hot_index
from
(
select
tvi.video_id,
sum(if((unix_timestamp(tuvl.end_time)-unix_timestamp(tuvl.start_time))-tvi.duration>=0,1,0))/count(tuvl.end_time) as spwbl,
sum(tuvl.if_like) as dzs,
count(tuvl.comment_id) as pls,
sum(tuvl.if_retweet) as zfs,
1/(datediff("2021-10-03",max(tuvl.end_time))+1) as xxd
from
tb_video_info as tvi
inner join
tb_user_video_log as tuvl
on tvi.video_id = tuvl.video_id
group by tvi.video_id
) as tmp
order by hot_index desc
limit 3

• 第一个错误，以为最近播放日期是写死的！结果写成datediff("2021-10-03",max(tuvl.end_time))+1)，这里的最近播放日期是tb_user_video_log表中的最大end_time时间。
• 第二个错误，开始无任何where条件，看到要求说要最近29天的视频，我理解有误，误以为是取最近29天有播放的视频并把条件附加在tmp临时表去。as tmp where datediff((select max(end_time) from tb_user_video_log),tmp.xxd)<=29

造成的错误就是：这样的条件会把 最近29天内保持有播放的视频 给算进来一起统计热度（包含了29天以前发布过但最近29天内有播放过的视频，排除了最近29天内发布过但29天内没有播放过的视频），而实际上要统计的是 最近29天内发布视频 的热度，所以后来将其更正，在临时表tmp内部做条件查询来取发布时间在最近时间29天内的视频。

• 第三个错误，要求说要对热度取整数，一开始我用的是floor()函数的向下取整，发现错误后改成round()函数的四舍五入取整。

这里再给自己标注一下，让自己记住，最近播放日期是通过 select max(end_time) from tb_user_video_log 取得！下次脑子要转的过来！