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

with tb_tag_log as -- 统计每个tag每天的点赞数(没有取消赞)以及转发数
         (
             select tag,
                    date(start_time) dt,
                    sum(if_like)     like_cnt,
                    sum(if_retweet)  retweet_cnt
             from tb_user_video_log
                      left join tb_video_info using (video_id)
             group by tag, dt
         )

select t1.tag                   as tag,
       t1.dt                    as dt,
       sum(t2.like_cnt)         as sum_like_cnt_7d, -- 7日总点赞数
       max(t2.retweet_cnt)      as max_retweet_cnt_7d -- 7日最大转发数
from tb_tag_log t1

-- 自连接,考察on的条件运用,此处将近7天数据对应当天进行笛卡尔积,并确保tag一致
    left join tb_tag_log t2
        on (t2.dt between date_sub(t1.dt, interval 6 day) and t1.dt)
        and t1.tag = t2.tag
-- 结果只需要查看国庆前三天的数据
where t1.dt between '2021-10-01' and '2021-10-03'
group by t1.tag, t1.dt
-- 按照tag降序,日期升序
order by t1.tag desc, t1.dt