开窗函数的使用：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
    图片说明