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

开窗函数的使用：https://blog.csdn.net/qq_42374697/article/details/115109386
题解：

• 求每一天的的点赞和转发，生成子表cnt
  • 联结表：using()
  • 点赞：sum(if_like) as sum_like_cnt,
  • 转发：sum(if_retweet) as sum_retweet_cnt
  • 条件：近一周where date(start_time) between '2021-09-25' and '2021-10-03'
  • 分组：group by tag,date(start_time)

    (
      select 
          tag,
          date(start_time) as dt,
          sum(if_like) as sum_like_cnt,
          sum(if_retweet) as sum_retweet_cnt
      from tb_user_video_log
      left join tb_video_info using(video_id)
      where date(start_time) between '2021-09-25' and '2021-10-03'
      group by tag,date(start_time)
    ) cnt

• 求7天的点赞和转发，生成子表cnt_7d
  • 点赞：sum(sum_like_cnt) over(wd_dt rows between 6 preceding and current row) as sum_like_cnt_7d,
  • 转发： max(sum_retweet_cnt) over(wd_dt rows 6 preceding ) as max_retweet_cnt_7d

(    select 
        tag,
        dt,
        sum(sum_like_cnt) over(wd_dt rows between 6 preceding and current row) as sum_like_cnt_7d,
        max(sum_retweet_cnt) over(wd_dt rows 6 preceding ) as max_retweet_cnt_7d
    from cnt
    window wd_dt as (partition by tag order by dt)
) cnt_7d

• 合并如下:

  select 
    tag,
    dt,
    sum_like_cnt_7d,
    max_retweet_cnt_7d
  from(
    select 
        tag,
        dt,
        sum(sum_like_cnt) over(wd_dt rows between 6 preceding and current row) as sum_like_cnt_7d,
        max(sum_retweet_cnt) over(wd_dt rows 6 preceding ) as max_retweet_cnt_7d
    from(
        select 
            tag,
            date(start_time) as dt,
            sum(if_like) as sum_like_cnt,
            sum(if_retweet) as sum_retweet_cnt
        from tb_user_video_log
        left join tb_video_info using(video_id)
        where date(start_time) between '2021-09-25' and '2021-10-03'
        group by tag,date(start_time)
    ) cnt
    window wd_dt as (partition by tag order by dt)
  ) cnt_7d
  where dt between '2021-10-01' and '2021-10-03'
  order by tag desc,dt

  易错点：
• 定义开窗的位置，可以直接写，也可以事先定义
• 开窗的使用
  rows between 6 preceding and current row 等同于 rows 6 preceding