第一次查询可以将时间限定为10.1往前推7天,以减少数据扫描select *
from (
select vi.tag,
date(ul.start_time) as start_time,
# 注意: 窗口函数和groupby可以同时使用, 但是窗口函数执行顺序在groupby之后
# rows 3 preceding窗口向前便宜多少行
sum(sum(ul.if_like)) over(partition by vi.tag order by date(ul.start_time) rows 6 preceding),
max(sum(ul.if_retweet)) over(partition by vi.tag order by date(ul.start_time) rows 6 preceding)
from tb_user_video_log ul
join tb_video_info vi on ul.video_id=vi.video_id
# 注意: 不能在where中用between '2021-10-01' and '2021-10-04', 这样窗口计算出来的只有这3天的值
-- where ul.start_time between '2021-10-01' and '2021-10-04'
group by vi.tag, date(ul.start_time)
order by vi.tag desc, date(ul.start_time)
) t1
# 注意: between是包含左右边界的
where t1.start_time >= '2021-10-01' and t1.start_time < '2021-10-04'