SQL：窗口函数之OVER()

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

OVER函数
1、不加条件的OVER函数——得到所有的汇总结果
2、仅有排序的OVER函数——得到按顺序的累计结果
3、加分区条件的OVER函数——得到分区的汇总结果
4、加分区、排序条件的OVER函数——得到按分区后按顺序的累计结果
5、加窗口大小条件的OVER函数
- 5-1 当前行和前1行
- 5-2 当前行和前面所有行
- 5-3 当前行和后面所有行
搭配LEAD/LAG函数

OVER函数

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

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

结果1

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

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

结果2

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

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

结果3

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

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

结果4

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