SQL 开窗函数

一、开窗函数

开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：

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