一、开窗函数
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
1、over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数。
SELECT
EMPLOYEE_ID, SALARY, MANAGER_ID, DEPARTMENT_ID,
SUM(SALARY) OVER (ORDER BY SALARY) DD
FROM INFA_TEST.EMPLOYEES EMP
ORDER BY SALARY
功能：按salary升序排序，统计小于等于当前salary的salary总和。
返回结果：
EMPLOYEE_ID SALARY MANAGER_ID DEPARTMENT_ID DD
132 2100 121 50 2100
128 2200 120 50 6500
136 2200 122 50 6500
127 2400 120 50 11300
135 2400 122 50 11300
119 2500 114 30 26300
140 2500 123 50 26300
144 2500 124 50 26300
191 2500 122 50 26300
182 2500 120 50 26300
注意 SALARY为2200、2400和2500行的DD值
2、over（partition by DEPARTMENT_ID）按照部门分区。
SELECT
EMPLOYEE_ID, SALARY, MANAGER_ID, DEPARTMENT_ID,
SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID) DD
FROM INFA_TEST.EMPLOYEES EMP
ORDER BY DEPARTMENT_ID
功能：按DEPARTMENT_ID分区，汇总各个部门的SALARY总和。
返回结果：
EMPLOYEE_ID SALARY MANAGER_ID DEPARTMENT_ID DD
200 4400 101 10 4400
201 13000 100 20 19000
202 6000 201 20 19000
114 11000 100 30 24900
115 3100 114 30 24900
116 2900 114 30 24900
119 2500 114 30 24900
118 2600 114 30 24900
117 2800 114 30 24900
注意 DEPARTMENT_ID为20，30的DD值
3、over（partition by DEPARTMENT_ID order by SALARY）按照部门分区。
SELECT
EMPLOYEE_ID, SALARY, MANAGER_ID, DEPARTMENT_ID,
SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) DD
FROM INFA_TEST.EMPLOYEES EMP
ORDER BY DEPARTMENT_ID
功能：按DEPARTMENT_ID分区，按SALARY升序排序，统计各个部门内部小于当前SALARY的和。
返回结果：
EMPLOYEE_ID SALARY MANAGER_ID DEPARTMENT_ID DD
200 4400 101 10 4400
201 13000 100 20 6000
202 6000 201 20 19000
114 11000 100 30 2500
115 3100 114 30 5100
116 2900 114 30 7900
119 2500 114 30 10800
118 2600 114 30 13900
117 2800 114 30 24900
注意 DEPARTMENT_ID为20、30的DD值和2中的区别
4、over（order by salary range between 50 preceding and 150 following）
SELECT
EMPLOYEE_ID, SALARY, MANAGER_ID, DEPARTMENT_ID,
SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY
RANGE BETWEEN 0 PRECEDING AND 100 FOLLOWING) DD
FROM INFA_TEST.EMPLOYEES EMP
ORDER BY DEPARTMENT_ID
功能：按DEPARTMENT_ID分区，按SALARY升序排序，汇总比当前SALARY大100的SALARY总和。
返回结果：
EMPLOYEE_ID SALARY MANAGER_ID DEPARTMENT_ID DD
200 4400 101 10 4400
201 13000 100 20 6000
202 6000 201 20 13000
114 11000 100 30 5100
115 3100 114 30 2600
116 2900 114 30 5700
119 2500 114 30 2900
118 2600 114 30 3100
117 2800 114 30 11000
解释：返回前置行和当前行SALARY相等，后续行比他大100的记录，在SALARY列上求和。
上下边界没有限制：OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
5、over（order by salary rows between 1 preceding and 2 following）-- 每行对应的数据窗口是之前行幅度值不超过1，之后行幅度值不超过2
SELECT
EMPLOYEE_ID, SALARY, MANAGER_ID, DEPARTMENT_ID,
SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY
ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) DD
FROM INFA_TEST.EMPLOYEES EMP
ORDER BY DEPARTMENT_ID
返回结果
EMPLOYEE_ID SALARY MANAGER_ID DEPARTMENT_ID DD
200 4400 101 10 4400
201 13000 100 20 19000
202 6000 201 20 19000
114 11000 100 30 7900
115 3100 114 30 10800
116 2900 114 30 11400
119 2500 114 30 19800
118 2600 114 30 17000
117 2800 114 30 14100
6、over（order by salary rows between unbounded preceding and unbounded following）
over（order by salary range between unbounded preceding and unbounded following）
返回结果:
EMPLOYEE_ID SALARY MANAGER_ID DEPARTMENT_ID DD
200 4400 101 10 4400
201 13000 100 20 19000
202 6000 201 20 19000
114 11000 100 30 24900
115 3100 114 30 24900
116 2900 114 30 24900
119 2500 114 30 24900
118 2600 114 30 24900
117 2800 114 30 24900