select video_id, 
cast( (100*wanbo+5*dianzan+3*pinglun+2*zhuanfa)*xinxiandu as signed) as hot_index
from(
select 
tv.video_id,

avg(timestampdiff(second,tu.start_time,tu.end_time)>=tv.duration) as wanbo,

sum(tu.if_like) as dianzan,

count(tu.comment_id) as pinglun,

sum(tu.if_retweet) as zhuanfa,

1/(datediff((select max(date(end_time)) from tb_user_video_log),max(end_time))+1) as xinxiandu

from tb_video_info tv
 join tb_user_video_log tu
on tv.video_id =tu.video_id
where datediff((select max(date(end_time)) from tb_user_video_log),date(tv.release_time))<=29 
group by tv.video_id

)a
order by hot_index desc
limit 3

很难的题目。。

确定问题：计算每个video_id(下用id表示)下近发布一个月的几个指标。

  • 完播率：完整播放的次数/播放次数，可以直接通过avg(条件)计算，不用再sum()/sum()
  • 点赞数：直接用sum(if_like)
  • 评论数：sum()或count()字段不会计算null值，count(comment_id)
  • 转发数：直接用sum(if_retweet)
  • 新鲜度：完全没理解1/（1+没有播放的天数）。。通过别人的答案明白，没有播放的天数就是：从当天（所有id下的最大日期）看这个视频（某个id）距离其最近一次播放日有几天没播放了，因此没有播放的天数=(datediff((select max(date(end_time)) from tb_user_video_log),max(end_time))，，注意这里括号很容易错

条件是：每个id发布近1个月内，因此可以表达为：某id最近一次播放时间-发布时间<=29，为什么不是等于，而是小于等于，因为有的视频可能是当前日期的5天前发布的，那某id最近一次播放时间-发布时间=5 就不满足条件，就会被剔除，但实际应该包含这个，因此取的是小于等于。

表达式：where datediff((select max(date(end_time)) from tb_user_video_log),date(tv.release_time))<=29 需要记住

where(函数（子查询）<N)是可以实现的