oracle分析函数之windowing_clause--rows
oracle分析函数之windowing_clause--rows
Some 易做图ytic functions allow the windowing_clause. In the listing of 易做图ytic 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上一个：oracle监听配置讲解
下一个：关于ORACLE DUAL表
- 更多Oracle疑问解答：
- 运行exp备份oracle数据库提示oracle-12154错误
- 有没有，生产Oracle Rman 备份脚本的工具啊！
- 初学orcle，希望有大大帮忙解说一下详细步骤，从登录oracle到创建表的过程
- oracle语句问题：一张user表，三个字段，id,name,time，插入记录比如：张三2007，李四2008，张三2011
- 如何写一个ORACLE触发器同步两个表中的数据？
- oracle 如何查看一个服务器上有多少个数据库.
- oracle 创建包的时候错误 求解
- oracle 重复列的问题
- oracle 中如何查处2星期前的数据
- 请教oracle数据库安装中的问题
- 请问谁能提供给我标准的oracle ERP的数据库表结构并详细说明各表主要的作用?
- 安装oracle遇到的问题 invalid entry CRC (expected 0x3e12e795 but got 0x9db0e9fd)
- 我的是ORACLE 10G，在RMAN中如何按指定的时间恢复数据文件啊？
- oracle为什么没有自动增长列
- oracle快捷键都有哪些啊？