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

最近一个月发布视频中热度最高

难点:

1. 新鲜度: 1 / (最近无播放天数 + 1), 首先要获取到当前时间, 也就是 tb_user_video_log 的最大播放时间; 具体思路: 根据 video_id 进行分组, 取到的 max(end_time) 只是对应 video_id 的 max 时间, 而不是当前时间, 此时就可以用到max() over() 窗口函数了, 窗口函数执行顺序在 group by 之后, 对 group by 后的 max(end_time) 再 max(max(ul.end_time)) over(), 如下即可

datediff(max(max(ul.end_time)) over(), max(ul.end_time)) + 1) as hot_index

• 注意点: 我看了很多题解, 都在这一步使用了子查询, 这样是答案是对的, 但是会极其消耗性能, 因为这种: select (select from) from 外层每有一条记录都会执行一次内层的子查询

2. 有的视频没有播放记录, 则不会出现在tb_user_video_log这张表中, 所以用join
3. 近一个月发布的视频中, 可以用vi.release_time >= date_sub((select max(ul.end_time) from tb_user_video_log ul), interval 29 day) 进行判断
4. 我遇到的问题:
   • datediff和timestampdiff; datediff只按日计算(时分秒不满一天也计1天), 而timestampdiff则会按照时分秒计算, 不满一天的则为0.

select ul.video_id,
    round((100 * avg(if(timestampdiff(second, ul.start_time, ul.end_time) >= vi.duration, 1, 0))
    + 5 * sum(ul.if_like)
    + 3 * count(ul.comment_id)
    + 2 * sum(ul.if_retweet)) /
    (datediff(max(max(ul.end_time)) over(), max(ul.end_time)) + 1), 0) as hot_index
    
    # 如下,1和2得出的结果是不同的 
    # 1: datediff(max(max(ul.end_time)) over(), max(ul.end_time)) + 1,
    # 2: timestampdiff(day, max(ul.end_time), max(max(ul.end_time)) over()) + 1
from tb_user_video_log ul
join tb_video_info vi on ul.video_id=vi.video_id
where vi.release_time >= date_sub((select max(ul.end_time) from tb_user_video_log ul), interval 29 day)
group by ul.video_id
order by hot_index desc
limit 3;

# select datediff('2022-05-04 00:00:00', '2022-05-03 10:00:00')
# select timestampdiff(day, '2022-05-03 00:00:00', '2022-05-04 10:00:00')