LeetCode--1321题:餐馆营业额变化增长（SQL窗口函数）

餐馆营业额变化增长

一、解题思路

题中要求我们统计七天内的销量总额和销量平均值，
窗口函数可以帮我们计算由某一天开始向前六天内的数据。

二、窗口函数

window_function_name
	OVER (
 
	partition by <用于分组的列名>
 
	order by <按序叠加的列名>
 
	ROWS|RANGE <窗口滑动的数据范围> )

（一）、window_function_name

静态窗口函数不能用frame子句；滑动窗口函数指加入order by或frame子句后，函数区域变为到当前行的数据集。

静态窗口函数：

排名函数 rank（）、dense_rank()、row_number()；

滑动窗口函数：

聚合函数 sum、 avg、count、max、min、percent_rank()、cum_dist()；

取值函数 first_value()、last_value()、nth_value()、lag()、lead()、ntile()

（二）、partition by <用于分组的列名>, order by <按序叠加的列名>

以1204题：最后一个能进公交车的人为数据举例。
partition by :根据用于分组的列名进行分组操作，类似group by
order by: 按照列名进行排序，并根据顺序进行叠加操作操作

1. 当over(order by xxx)时，对全表进行排序，依次求和

select *,sum(weight) over(order by turn) as sumWeight
from Queue

2. 当over(partition by xxx)时，分组求和

select *,sum(weight) over(partition by turn) as sumWeight //根据turn分组求和，但在该例子中不同turn只有一个weight
from Queue

3. 当over(partition by xxx order by xxxx时在每个分组内按照某顺序排序
4. 当over(order by xxx)当与from的子句order by 相同时，则相当只有over(order by xxx)

select *,sum(weight) over(order by turn) as sumWeight
from Queue
order by turn

5. 当不同时，from子句的order by 会覆盖掉over()中的order by

select *,sum(weight) over(order by turn) as sumWeight
from Queue
order by person_id

（三）、ROWS|RANGE <窗口滑动的数据范围>

<窗口滑动的数据范围> 用来限定 [你要的操作] 所运用的数据的范围，具体有如下这些

当前 - current row

之前的 - preceding

之后的 - following

无界限 - unbounded

表示从前面的起点 - unbounded preceding

表示到后面的终点 - unbounded following

举例：

取当前行和前五行：ROWS between 5 preceding and current row --共6行

取当前行和后五行：ROWS between current row and 5 following --共6行

取前五行和后五行：ROWS between 5 preceding and 5 following --共11行

取当前行和前六行：ROWS 6 preceding（等价于between...and current row） --共7行

这一天和前面6天：RANGE between interval 6 day preceding and current row --共7天

这一天和前面6天：RANGE interval 6 day preceding（等价于between...and current row） --共7天

字段值落在当前值-100到+200的区间：RANGE between 100 preceding and 200 following  --共301个数值

三、题解

select 
    visited_on,amount,average_amount 
from
    (select
        distinct visited_on,
        sum(amount) over(order by visited_on range interval 6 day preceding) as amount,
        //统计七天内的总额
        round(sum(amount) over(order by visited_on range interval 6 day preceding)/7,2) as average_amount
        //统计七天内的总额并除以7
    from
        Customer
    order by
        visited_on) a
where
    datediff(visited_on,(select min(visited_on)from Customer))>=6