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

# 国庆前3天，每类视频每天近一周总点赞量和一周内最大每天转发量
# 字段要求：tag、dt、近一周总点赞量(包括当日)、近一周最大单日转发量

# tb1：宽表连接tag
with tb1 as (
    select video_id,start_time,end_time,tag,if_follow,if_like,if_retweet,comment_id
    from tb_user_video_log left join tb_video_info using(video_id)
),

# tb2：过程表，维度：tag、dt，指标：求和点赞量 & 最大值转发量
tb2 as(
    select tag,date(end_time) as dt,
    sum(if_like) as sum_like_cnt,
    sum(if_retweet) as sum_retweet_cnt
    from tb1 
    group by tag,date(end_time)
),

# tb3：过程表，采用rows关键字获取当前日期以及前6日窗口数据
# tb3 as (
#     select tag, dt,
#     sum(sum_like_cnt) over(order by dt rows 6 preceding) as sum_like_cnt_7d,
#     max(sum_retweet_cnt) over(order by dt rows 6 preceding) as max_retweet_cnt_7d
#     from tb2
#     group by tag,dt
#     # having dt between '2021-10-01' and '2021-10-03'
#     order by tag desc,dt asc
# )

# V2: 相较于上一版本，不采用tag，dt分组，而是单纯采用tag进行分组，因为出现了多个tag，按照tag分组不会出现混乱，按照两者分组会出现混乱
tb3 as (
    select tag, dt,
    sum(sum_like_cnt) over(partition by tag order by dt rows 6 preceding) as sum_like_cnt_7d,
    max(sum_retweet_cnt) over(partition by tag order by dt rows 6 preceding) as max_retweet_cnt_7d
    from tb2
    # group by tag,dt
    # having dt between '2021-10-01' and '2021-10-03'
    order by tag desc,dt asc
)

select *
from tb3
where dt between '2021-10-01' and '2021-10-03'

# 注意点1，限定窗口函数，应该是得到了结果后再筛选我们要的天数，因此后面单独筛选需要的天气
# 注意点2，rows 6 preceding限定窗口函数范围时，遇到多个tag，需要防止tag之间交叉统计，因此采用tag分组！！！