oracle分析函数之windowing_clause--rows

来源：互联网发布：golang面试题编辑：程序博客网时间：2024/04/30 10:53

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
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 *