题解 | #国庆期间每类视频点赞量和转发量#

新知识点：

• 滑动窗口：sum、avg、count、max、min ➕ () over (),聚合函数在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。类似滑动窗口的效果。

  从date_time开始往前推6天的总和（近7天）：
  sum() over (order by date_time rows 6 preceding)

• 窗口函数：

  <窗口函数> over(partition by<用于分组的列名> order by<用于排列的列名>)

  适用于函数：rank、dense_rank、row_number;
• 自定义窗口：window [窗口名] as (patition by<用于分组的列名> order by<用于排列的列名>)

明确题意：
统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量，结果按视频类别降序、日期升序排序。假设数据库中数据足够多，至少每个类别下国庆头3天及之前一周的每天都有播放记录。

问题分解：

• 计算每类视频每天的点赞量和转发量：
  • 关联表：tb_user_video_log left join tb_video_info using video_id
  • 筛选必要时间窗的数据：where date(start_time) between '2021-09-25' and '2021-10-03'
  • 每天的点赞量和转发量：sum(if_like) like_cnt;sum(if_retweet) retweet_cnt
• 近七天汇总量：
  • 自定义窗口：每类视频每一天 window wd_tag_dt as(partition by tag order by dt desc)
  • 近一周总点赞量：sum(like_cnt) over (wd_tag_dt rows 6 preceding)
  • 近一周最大转发量：max(retweet_cnt) over (wd_tag_dt rows 6 preceding)

    select tag,dt,sum_like_cnt_7d,max_retweet_cnt_7d
    from(
    select tag,dt,
        sum(like_cnt) over (partition by tag order by dt rows 6 preceding) sum_like_cnt_7d,
        max(retweet_cnt) over (partition by tag order by dt rows 6 preceding) max_retweet_cnt_7d
    from
    (
        select 
            b.tag,
            date(start_time) dt,
            sum(if_like) like_cnt,
            sum(if_retweet) retweet_cnt
        from tb_user_video_log a 
        join tb_video_info b
        on a.video_id=b.video_id
        where date(start_time) 
        between '2021-09-25' and '2021-10-03'
        group by b.tag,date(start_time)
    )p
    )t
    where dt between '2021-10-01' and '2021-10-03'
    order by tag desc