与聚集函数一样,窗口函数也针对定义的行集(组)执行聚集,但它不像聚集函数那样每组只返回一个值,窗口函数可以为每组返回多个值。
例子: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