SQL：窗口函数之OVER()

窗口函数 通用格式 “函数 OVER (PARTITION BY 分组 ORDER BY 排序依据 升降序)”。
这里记录下OVER() 以及搭配LEAD/LAG函数的使用方法（执行平台Impala）

OVER函数

1、不加条件的OVER函数——得到所有的汇总结果

select  day1, sale_money, sum(sale_money) over () as `cum_money`  from t

2、仅有排序的OVER函数——得到按顺序的累计结果

select  day1, sale_money, sum(sale_money) over (order by day1) as `cum_money`  from t

3、加分区条件的OVER函数——得到分区的汇总结果

select  day1, sale_money, sum(sale_money) over (partition by month(day1)) as `cum_money`  from t

4、加分区、排序条件的OVER函数——得到按分区后按顺序的累计结果

select  day1, sale_money, sum(sale_money) over (partition by month(day1) order by day1) as `cum_money`  from t

5、加窗口大小条件的OVER函数

current row 当前行
n preceding:往前n行
n following:往后n行
unbounded： 起点，对应前面的n

5-1 当前行和前1行

select  day1, sale_money, sum(sale_money) over (order by day1 rows between 1 preceding and current row) as `cum_money`  from t

5-2 当前行和前面所有行

select  day1, sale_money, sum(sale_money) over (order by day1 rows between unbounded preceding and current row) as `cum_money`  from t

5-3 当前行和后面所有行

select  day1, sale_money, sum(sale_money) over (order by day1 rows between current row and unbounded following) as `cum_money`  from t

搭配LEAD/LAG函数

lead(col,n) ：往后第n行
lag(col,n) ：往前第n行

-- 日期按升序排后，取当前往后的第2条数据
select day1, sale_money, lead(day1,2) over (order by day1 asc) as `后第2行day1`, lead(day1,2) over (order by day1 asc) as `后第2行sale_money`
from t

-- 日期按升序排后，取当前往前的第2条数据
select day1, sale_money, lag(day1,2) over (order by day1 asc) as `前第2行day1`, lag(day1,2) over (order by day1 asc) as `前第2行sale_money`
from t