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

思路：

首先求每个video_id的视频完播率(完整播放次数/播放总次数), 点赞数, 评论数, 转发数，

然后筛选近一个月发布的视频。

容易理解错误的是：当天日期不一定是2021-10-03，而是所有视频播放记录中的最近一天。

这里因为前面的数据需要group by，因此在group by之后如何求得当天日期是一个难点：max(date(max(end_time))) over ()

指的是 先对每个 video_id获取date(max(end_time))，之后对整个group by结果开窗求得全部date(max(end_time))的最大值 即当天日期。

select 
    video_id, 
    sum(if(timestampdiff(second,start_time,end_time)>=duration, 1, 0)) / count(start_time) complete_rate,
    sum(if_like) like_num,
    count(comment_id) comment_num,
    sum(if_retweet) retweet_num,
    1/(datediff(max(date(max(end_time))) over (), date(max(end_time))) + 1) freshness,
    max(date(max(end_time))) over () today #当天日期(每group最大日期中的最大日期)
from tb_user_video_log left join tb_video_info using(video_id)
group by video_id

解决上面内容，之后就比较容易理解了。完整代码如下：

select video_id,
    round((100*complete_rate+5*like_num+3*comment_num+2*retweet_num)*freshness, 0) hot_index
from (
    select 
        video_id, 
        sum(if(timestampdiff(second,start_time,end_time)>=duration, 1, 0)) / count(start_time) complete_rate,
        sum(if_like) like_num,
        count(comment_id) comment_num,
        sum(if_retweet) retweet_num,
        1/(datediff(max(date(max(end_time))) over (), date(max(end_time))) + 1) freshness,
        max(date(max(end_time))) over () today  #当天日期(每group最大日期中的最大日期)
    from tb_user_video_log left join tb_video_info using(video_id)
    group by video_id
) a left join tb_video_info using(video_id)
where date(release_time) between (today-interval 29 day) and today  #过滤近一个月发布的视频
order by hot_index DESC
limit 3