SQL窗口函数——SQL Cookbook读书笔记之一

与聚集函数一样，窗口函数也针对定义的行集（组）执行聚集，但它不像聚集函数那样每组只返回一个值，窗口函数可以为每组返回多个值。

例子：select ename, deptno, count(*) over() as cnt

            from emp

            order by 2

 

ENAME                    DEPTNO                  CNT

-----------------------------------------------------------

CLARK                                 10                     14

KING                                     10                     14

MILLER                                10                      14

SMITH                                   20                      14

ADAMS                                  20                      14

FORD                                    20                      14

SCOTT                                  20                      14

JONES                                  20                      14

ALLEN                                  30                      14

BLAKE                                  30                      14

MARTIN                                30                      14

JAMES                                  30                      14

TURNER                              30                      14

WARD                                   30                      14

 

注意：在SQL处理中，窗口函数都是最后一步执行，而且仅位于ORDER BY子句之前。

 

分区

select ename, deptno, count(*) over(partition by deptno) as cnt

from emp

order by 2

 

ENAME                    DEPTNO                  CNT

-----------------------------------------------------------

CLARK                                 10                     3

KING                                     10                     3

MILLER                                10                      3

SMITH                                   20                      5

ADAMS                                  20                      5

FORD                                    20                      5

SCOTT                                  20                      5

JONES                                  20                      5

ALLEN                                  30                      6

BLAKE                                  30                      6

MARTIN                                30                      6

JAMES                                  30                      6

TURNER                              30                      6

WARD                                   30                      6

 

PARTITION BY子句的优点是：在同一个SELECT语句中， 一个窗口函数的计算独立于按其他列分区的其他窗口函数的计算。

select ename, deptno,

    count(*) over(partition by deptno) as dept_cnt,

    job,

    count(*) over(partition by job) as job_cnt

from emp

order by 2

 

ENAME                    DEPTNO        DEPT_CNT    JOB                      JOB_CNT

---------------------------------------------------------------------------------------------- ----

CLARK                                 10                     3          CLERK                               4

KING                                     10                     3          MANAGER                         3

MILLER                                10                      3          PRESIDENT                     1

SMITH                                   20                      5          ANALYST                          2

ADAMS                                  20                      5          ANALYST                          2

FORD                                    20                      5          CLERK                             4

SCOTT                                  20                      5          MANAGER                        3

JONES                                  20                      5         CLERK                              4

ALLEN                                  30                      6         CLERK                               4

BLAKE                                  30                      6          SALESMAN                      4

MARTIN                                30                      6          SALESMAN                      4

JAMES                                  30                      6          SALESMAN                      4

TURNER                              30                      6          SALESMAN                      4

WARD                                   30                      6          MANAGER                        3

 

项目中的实际例子：

SELECT C.RELATIONSHIP_ID, C.CNTR_N CNTR_N, O.OPERATION_REJIME_C REJIME, O.OPERATION_TYPE_C OPRTYPE, O.OPERATION_DT OPRDATE,
O.OPERATION_SHIPPING_STATUS_C SS, O.OPERATION_INLAND_TRADE_I TRADE,
COUNT(DISTINCT O.OPERATION_REJIME_C) OVER (PARTITION BY C.RELATIONSHIP_ID)REJIMECOUNT
FROM REPORT_CNTR C, REPORT_CNTR_OPERATION O
WHERE O.OPERATION_TYPE_C IN ('DISC', 'DDISC','EXIT','EXIW')
AND C.CNTR_ID = O.CNTR_ID
ORDER BY C.RELATIONSHIP_ID