1321：sum(cost) over(PARTITION BY NAME ORDER BY p_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

知识点

sum(cost) over(PARTITION BY NAME ORDER BY p_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sample4,--和sample3一样,由起点到当前行的聚合
sum(cost) over(PARTITION BY NAME ORDER BY p_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS sample5,--前面一行和当前行做聚合
sum(cost) over(PARTITION BY NAME ORDER BY p_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sample6,--前面一行和后面一行做聚合
sum(cost) over(PARTITION BY NAME ORDER BY p_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS sample7 --当前行和后面所有行

 

 

 

 

 

 

select distinct visited_on,sum_amount as amount,round(average_amount,2) as average_amount 
from
(
    select visited_on,
        sum(amount) over(order by visited_on rows 6 preceding) as sum_amount,
        avg(amount) over(order by visited_on rows 6 preceding) as average_amount
    from(
         select visited_on,sum(amount) as amount 
         from Customer
         group by visited_on
         )t1
)t2 
where datediff(visited_on,(select min(visited_on)from Customer))>=6