力扣数据库热题70--No.1321题解

官方难度中等，体感中等稍繁琐，需要稍微思考下，算是比较经典的题目。

这里先分组聚合，同时用窗口函数对近7天营业额累计求和，用到rows between xx preceding and current row。

其次用窗口函数及条件，巧妙过滤掉前7天的记录（按题目要求）。

with tmp as
(select visited_on,sum(amount) as day_sum_amount,
    sum(sum(amount)) over(order by visited_on rows between 6 preceding and current row) as 7days_sum_amount,
    round(avg(sum(amount)) over(order by visited_on rows between 6 preceding and current row),2) as 7days_avg_amount,
    row_number() over(order by visited_on) as rnk
from Customer
group by visited_on
order by visited_on)

select visited_on,7days_sum_amount as amount,
    7days_avg_amount as average_amount
from tmp
where rnk > 6

其他题解会用到datediff搭配min，处理日期过滤问题。