SQL OLAP 计算相邻行之间的增量

select * from demo.emp_salary

id,name,salary,SAL_DATE
1,'A',1000.00,'2012-01-01'
2,'A',1100.00,'2012-02-01'
3,'A',1150.00,'2012-03-01'
4,'A',1300.00,'2012-04-01'
5,'A',1200.00,'2012-05-01'
6,'A',1500.00,'2012-06-01'
7,'B',1000.00,'2012-01-01'
8,'B',1100.00,'2012-02-01'
9,'B',1100.00,'2012-03-01'
10,'B',1300.00,'2012-04-01'
11,'B',1250.00,'2012-05-01'
12,'C',1200.00,'2012-04-01'

--查询某员工相邻月的工资变化情况

select name,sal_date,salary, max(salary) over(partition by name order by sal_date rows between current row and current row) as curr_sal,
max(salary) over(partition by name order by sal_date rows between 1 preceding and 1 preceding) as prev_sal,
(curr_sal-prev_sal) as delta
from demo.emp_salary
order by name,sal_date

--

name,sal_date,salary,curr_sal,prev_sal,delta
'A','2012-01-01',1000.00,1000.00,,
'A','2012-02-01',1100.00,1100.00,1000.00,100.00
'A','2012-03-01',1150.00,1150.00,1100.00,50.00
'A','2012-04-01',1300.00,1300.00,1150.00,150.00
'A','2012-05-01',1200.00,1200.00,1300.00,-100.00
'A','2012-06-01',1500.00,1500.00,1200.00,300.00
'B','2012-01-01',1000.00,1000.00,,
'B','2012-02-01',1100.00,1100.00,1000.00,100.00
'B','2012-03-01',1100.00,1100.00,1100.00,0.00
'B','2012-04-01',1300.00,1300.00,1100.00,200.00
'B','2012-05-01',1250.00,1250.00,1300.00,-50.00
'C','2012-04-01',1200.00,1200.00,,

--rows between current row and current row ： 只查当前行的max(salary) - 其实就是当前的salary，但这里需要集合函数（类似MAX,MIN,SUM...）

--partition by按什么规则来划分窗口

olap_function() OVER (PARTITION BY col1, col2...)