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

#小白简单直接的窗口函数写法

select 
t.tag,
t.dt,
t.pt as sum_like_cnt_7d,
t.py as max_retweet_cnt_7d
from(
select 
tf.tag,
date(tg.start_time) as dt,
sum(sum(if_like=1))over(partition by  tf.tag order by date(tg.start_time) range interval 6 day preceding) as pt,
max(sum(if_retweet=1))over(partition by  tf.tag order by date(tg.start_time) range interval 6 day preceding) as py
from tb_user_video_log tg left join tb_video_info tf 
on tg.video_id=tf.video_id
group by tf.tag,dt
) as t 
where
t.dt between "2021-10-01" and "2021-10-03"
order by t.tag desc,t.dt