窗口功能:
====================
可以基于指定的窗口在结果集中为每一行计算Oracle的窗口函数,可以通过以下三种方法之一来定义聚集窗口。
1.通过指定一组行:
2.通过指定时间间隔:
3.通过指定一系列值:
示例ex#1
------------------------
SELECT HIREDATE,SUM(SAL) SALARY, SUM(SUM(SAL)) OVER(ORDER BY HIREDATE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TOTALSALARY FROM EMP
GROUP BY HIREDATE ORDER BY HIREDATE;
示例ex#2
------------------------
SELECT HIREDATE,SUM(SAL) SALARY,MAX(SUM(SAL)) OVER(ORDER BY HIREDATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) TOTALSALARY
FROM EMP
WHERE DEPTNO=30
GROUP BY HIREDATE ORDER BY HIREDATE;
示例ex#3
-----------------------
SELECT HIREDATE,SUM(SAL) SALARY,SUM(SUM(SAL)) OVER(ORDER BY HIREDATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RUNNINGTOTAL
FROM EMP
WHERE DEPTNO=30
GROUP BY HIREDATE ORDER BY HIREDATE;
示例样本#4
-----------------------
SELECT HIREDATE,SUM(SAL) SALARY,AVG(SUM(SAL)) OVER(ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ROLLINGAVERAGE
FROM EMP
WHERE DEPTNO=30
GROUP BY HIREDATE ORDER BY HIREDATE;
示例ex#5
-----------------------
SELECT HIREDATE,SUM(SAL) SALARY,AVG(SUM(SAL)) OVER(ORDER BY HIREDATE RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) ROLLINGAVERAGE
FROM EMP
GROUP BY HIREDATE ORDER BY HIREDATE;
示例ex#6
--------------------
SELECT HIREDATE,SUM(SAL) SALARY,AVG(SUM(SAL)) OVER(ORDER BY HIREDATE RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING) ROLLINGAVERAGE
FROM EMP
GROUP BY HIREDATE ORDER BY HIREDATE;
示例ex#7
--------------------
SELECT TRUNC(HIREDATE)DAY,SUM (SAL) SALARY,AVG(SUM(SAL)) OVER (ORDER BY TRUNC(HIREDATE)
RANGE BETWEEN INTERVAL '2' DAY PRECEDING AND INTERVAL '2' DAY FOLLOWING) AVERAGE
FROM EMP
GROUP BY TRUNC(HIREDATE);
同时检查窗口功能-2
From: https://bytes.com/topic/oracle/insights/740683-windowing-function-1-a