oracle分析函数之windowing_clause--rows

Some analytic functions allow the windowing_clause. In the listing of analytic functions at the end of this section, the functions that allow the windowing_clause are followed by an asterisk (*)。--有些分析函数允许windowing_clause，在附录中，带*号的分析函数可以用windowing_clause，可以看到很多是聚合函数。

rows代表物理行，range代表逻辑偏移，我们来做下试验rows：

--unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录，也就是表中的所有记录 --unbounded：不受控制的，无限的 --preceding：在...之前 --following：在...之后 SELECT E.DEPTNO, E.EMPNO, E.ENAME, E.SAL, LAST_VALUE(E.SAL) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL FROM EMP E; DEPTNO EMPNO ENAME SAL MAX_SAL ---------- ---------- ---------- ---------- ---------- 10 7934 MILLER 1300 5000 10 7782 CLARK 2450 5000 10 7839 KING 5000 5000 20 7369 SMITH 800 3000 20 7876 ADAMS 1100 3000 20 7566 JONES 2975 3000 20 7788 SCOTT 3000 3000 20 7902 FORD 3000 3000 30 7900 JAMES 950 2850 30 7654 MARTIN 1250 2850 30 7521 WARD 1250 2850 30 7844 TURNER 1500 2850 30 7499 ALLEN 1600 2850 30 7698 BLAKE 2850 2850 --ROWS BETWEEN unbounded preceding AND current row 是指第一行至当前行的汇总 SELECT EMPNO, ENAME, DEPTNO, SAL, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_sal FROM EMP; EMPNO ENAME DEPTNO SAL MAX_SAL ---------- ---------- ---------- ---------- ---------- 7782 CLARK 10 2450 2450 7839 KING 10 5000 7450 7934 MILLER 10 1300 8750 7876 ADAMS 20 1100 1100 7902 FORD 20 3000 4100 7566 JONES 20 2975 7075 7788 SCOTT 20 3000 10075 7369 SMITH 20 800 10875 7499 ALLEN 30 1600 1600 7698 BLAKE 30 2850 4450 7900 JAMES 30 950 5400 7654 MARTIN 30 1250 6650 7844 TURNER 30 1500 8150 7521 WARD 30 1250 9400 --ROWS BETWEEN current row AND unbounded following 指当前行到最后一行的汇总 SELECT EMPNO, ENAME, DEPTNO, SAL, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) max_sal FROM EMP; EMPNO ENAME DEPTNO SAL MAX_SAL ------ ---------- ---------- ---------- ---------- 7782 CLARK 10 2450 8750 7839 KING 10 5000 6300 7934 MILLER 10 1300 1300 7876 ADAMS 20 1100 10875 7902 FORD 20 3000 9775 7566 JONES 20 2975 6775 7788 SCOTT 20 3000 3800 7369 SMITH 20 800 800 7499 ALLEN 30 1600 9400 7698 BLAKE 30 2850 7800 7900 JAMES 30 950 4950 7654 MARTIN 30 1250 4000 7844 TURNER 30 1500 2750 7521 WARD 30 1250 1250 --注意ROWS BETWEEN 1 preceding AND current row 是指当前行的上一行(rownum-1)到当前行的汇总 SELECT EMPNO, ENAME, DEPTNO, SAL, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) max_sal FROM EMP; EMPNO ENAME DEPTNO SAL MAX_SAL ------ ---------- ---------- ---------- ---------- 7782 CLARK 10 2450 2450 7839 KING 10 5000 7450 7934 MILLER 10 1300 6300 7876 ADAMS 20 1100 1100 7902 FORD 20 3000 4100 7566 JONES 20 2975 5975 7788 SCOTT 20 3000 5975 7369 SMITH 20 800 3800 7499 ALLEN 30 1600 1600 7698 BLAKE 30 2850 4450 7900 JAMES 30 950 3800 7654 MARTIN 30 1250 2200 7844 TURNER 30 1500 2750 7521 WARD 30 1250 2750 --注意ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行(rownum-1)到当前行的下两行(rownum+2)的汇总 SELECT EMPNO, ENAME, DEPTNO, SAL, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) max_sal FROM EMP; EMPNO ENAME DEPTNO SAL MAX_SAL ------ ---------- ---------- ---------- ---------- 7782 CLARK 10 2450 8750 7839 KING 10 5000 8750 7934 MILLER 10 1300 6300 7876 ADAMS 20 1100 7075 7902 FORD 20 3000 10075 7566 JONES 20 2975 9775 7788 SCOTT 20 3000 6775 7369 SMITH 20 800 3800 7499 ALLEN 30 1600 5400 7698 BLAKE 30 2850 6650 7900 JAMES 30 950 6550 7654 MARTIN 30 1250 4950 7844 TURNER 30 1500 4000 7521 WARD 30 1250 2750 --注意ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行(rownum-1)到当前行的下两行(rownum+1)的汇总 SELECT EMPNO, ENAME, DEPTNO, SAL, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) max_sal FROM EMP; EMPNO ENAME DEPTNO SAL MAX_SAL ------ ---------- ---------- ---------- ---------- 7782 CLARK 10 2450 7450 7839 KING 10 5000 8750 7934 MILLER 10 1300 6300 7876 ADAMS 20 1100 4100 7902 FORD 20 3000 7075 7566 JONES 20 2975 8975 7788 SCOTT 20 3000 6775 7369 SMITH 20 800 3800 7499 ALLEN 30 1600 4450 7698 BLAKE 30 2850 5400 7900 JAMES 30 950 5050 7654 MARTIN 30 1250 3700 7844 TURNER 30 1500 4000 7521 WARD 30 1250 2750 附录： AVG * CORR * COVAR_POP * COVAR_SAMP * COUNT * CUME_DIST DENSE_RANK FIRST FIRST_VALUE * LAG LAST LAST_VALUE * LEAD MAX * MIN * NTILE PERCENT_RANK PERCE

声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did238491

更新时间：2023-05-25 阅读：39次