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

# # 转发量
# sum(if(if_retweet = 1,1,if_retweet)) as total_retweet_cnt
# # 点赞量
# sum(if(if_like = 1, 1, if_like)) as total_like_cnt


# # sum(if (if_follow = 2, -1, if_follow)) as fans_add_cnt
# # 分组
# group by tag,week
# # 排序
# order by tag desc, data asc;


# # 开窗函数
# rows n perceding : 从当前行到前n行（一共n+行）
# range/rows between 边界规则1 and 边界规则2： range 表示按照值的范围进行定义框架，rows表示按照行的范围进行定义框架

# rows between 2 perceding and 2 following  # 当前行往前2行 + 当前行 + 当前行往后2行， 共5行
# rows between 1 following 3 following  # 当前行的后一行--后三行 ，共3行
# rows between unbounded preceding and current row    # 从第一行开始到当前行

# # 时间范围，10.1- 10.3 分别是 9.25-10.1, 9.26-10.2,9.27-103
# datediff('2021-10-03', date_format(start_time, '%Y-%m-%d')) < 9
# # 规定日期内，每类tage每天的点赞 （like_cnt) 和转发数据（ret_cnt)
# select tag, date_format(start_time, '%Y-%m-%d') dt,
#         sum(if_like) like_cnt, # 每类tag每天点赞数据
#         sum(if_retweet) ret_cnt # 每类tag每天的转发
# from tb_user_video_log join tb_video_info using(video_id)
# where datediff('2021-10-03', date_format(start_time,'%Y-%m-%d')) < 9
# grouy by dt,tag;



SELECT
  *
FROM (
  SELECT
    tag,
    dt,
    SUM(like_cnt) OVER w sum_like_cnt_7d,
    MAX(retweet_cnt) OVER w sum_retweet_cnt_7d
  FROM (
    SELECT
      tag,
      DATE(start_time) dt,
      SUM(if_like) like_cnt,
      SUM(if_retweet) retweet_cnt
    FROM tb_video_info
    LEFT JOIN tb_user_video_log USING(video_id)
    WHERE DATE(start_time) BETWEEN '2021-09-25' AND '2021-10-03'
    group by 1,2) t1
  WINDOW w AS (PARTITION BY tag ORDER BY dt DESC ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING)
) t2
GROUP BY 1, 2
HAVING dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY 1 DESC, 2

我就问，正常人谁能想到