用release time进行时间窗口筛选 + JOIN过滤无播放记录的视频

【2022.08.26 更新】

针对许多人的问题更新了代码~

SELECT
    video_id,
    ROUND((100 * finished_rate + 
           5 * like_cnt +
           3 * comment_cnt +
           2 * retweet_cnt) / (no_play_record_day_cnt + 1), 0) hot_index # 仔细查看指标定义，尤其是【新鲜度】
FROM (
        SELECT
            i.video_id,
            # 播放率：计算的是每个video_id在近一个月的播放率平均值。
            AVG(TIMESTAMPDIFF(second, start_time, end_time) >= duration) finished_rate, 
            SUM(if_like) like_cnt,
            COUNT(comment_id) comment_cnt, # COUNT会自动过滤空值。
            SUM(if_retweet) retweet_cnt,
            # 没有播放日的计算：从当天开始，到该视频的最后一个日期。
            # 【当天】：所有播放日期中的最大日期
            #【视频的最后播放日期】：按video_id聚合的维度下的最大日期，注意题目要求以【end-time】为准。
            DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)), 
                     MAX(DATE(end_time))) no_play_record_day_cnt # 注意这里先要取出日期，再取最大值。
        FROM tb_video_info i
        JOIN tb_user_video_log USING(video_id)
        # 【近一个月内发布】的日期筛选
        #【近】：表示从当天开始，即所有播放日期中的最大日期
        # 【一个月内】：表示从当天开始，前推29天 --- 日期前推是【n-1】
        WHERE DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)), DATE(release_time)) <= 29
        GROUP BY 1) t
ORDER BY 2 DESC 
LIMIT 3

【旧帖】

时间: 37ms 内存：6408KB

两个坑。

一、关于时间窗口的筛选

需求是最后播放日期往前推30天，如果用前面几题见到过的DATEDIFF法：

后面那个不可以用end_time，而是应该用release_time, 因为存在视频发布当天并没有播放记录的情况。

举个例子：10.03往前推30天是09.04。我们要获取的统计时间区间是09.04-10.03。假设某个视频09.01发布，但是5天后才有第一次播放记录，那么这个视频最早的end_time是09.06，按照这个end_time，用DATEDIFF法来生成，得到的时间区间是09.06-10.03，明显不完整。个人感觉题目也是没有完全讲清楚。。

——————————————————————————————————

二、要过滤掉没有播放记录的视频

因为存在list里有，但log完全没有播放记录的video，比如2004。。所以在最初的子查询里面：

如果用LEFT JOIN，后面的WHERE要加上end_time IS NOT NULL

或者

直接用内连接JOIN过滤

——————————————————————————————————

代码如下：

SELECT
  video_id,
  ROUND((100 * finished_rate 
   + 5 * like_cnt 
   + 3 * comment_count 
   + 2 * retweet_cnt) / (unfinished_day_cnt + 1)) hot_index
FROM (
  SELECT
    i.video_id,
    SUM(TIMESTAMPDIFF(second, start_time, end_time) >= duration) / COUNT(*) finished_rate,
    SUM(if_like = 1) like_cnt,
    SUM(IF(comment_id IS NOT NULL, 1, 0)) comment_count,
    SUM(if_retweet = 1) retweet_cnt,
    DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)), MAX(DATE(end_time))) unfinished_day_cnt
  FROM tb_video_info i
  JOIN tb_user_video_log USING(video_id)
  WHERE DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)), DATE(release_time)) <= 29 
  GROUP BY 1
) t
ORDER BY 2 DESC LIMIT 3