oracle分析函数之windowing_clause--rows - 数据库编程

--- ---------- ---------- ---------- ---------- 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 PERCENTILE_CONT PERCENTILE_DISC RANK RATIO_TO_REPORT REGR_ (Linear Regression) Functions * ROW_NUMBER STDDEV * STDDEV_POP * STDDEV_SAMP * SUM * VAR_POP * VAR_SAMP * VARIANCE *

oracle分析函数之windowing_clause--rows(二)