oracle——分析函数OVER ()

分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是

对于每个组返回多行，而聚合函数对于每个组只返回一行。

 

SQL> select t.empno,t.ename,sum(t.sal) 2 from emp t; select t.empno,t.ename,sum(t.sal) * ERROR at line 1: ORA-00937: not a single-group group function SQL> select t.empno,t.ename,sum(t.sal) over() sum from emp t; EMPNO ENAME SUM ---------- ---------- ---------- 7369 SMITH 31025 7499 ALLEN 31025 7521 WARD 31025 7566 JONES 31025 7654 MARTIN 31025 7698 BLAKE 31025 7782 CLARK 31025 7788 SCOTT 31025 7839 KING 31025 7844 TURNER 31025 7876 ADAMS 31025 7900 JAMES 31025 7902 FORD 31025 7934 MILLER 31025 14 rows selected.

 

over()、over(order by...)与over(partition by...)之间的区别

 

over(order by...)用在聚合函数（max(),sun()....）后面，可返回根据排序结果进行统计到当前行的聚合值(即“连续”统计)；

分析：第二行的sum=第一行里的sal+第二行里的sal；
      第二行的sum=第一行里的sal+第二行里的sal+第三行里的sal；

 

1 select t.empno, 2 t.deptno, 3 t.ename, 4 t.sal, 5 sum(t.sal) over(order by t.ename) sum 6* from emp t SQL> / EMPNO DEPTNO ENAME SAL SUM ---------- ---------- ---------- ---------- ---------- 7876 20 ADAMS 1100 1100 7499 30 ALLEN 1600 2700 7698 30 BLAKE 2850 5550 7782 10 CLARK 2450 8000 7902 20 FORD 3000 11000 7900 30 JAMES 950 11950 7566 20 JONES 2975 14925 7839 10 KING 5000 19925 7654 30 MARTIN 1250 21175 7934 10 MILLER 1300 22475 7788 20 SCOTT 5000 27475 7369 20 SMITH 800 28275 7844 30 TURNER 1500 29775 7521 30 WARD 1250 31025 14 rows selected.

 

 

 3.over(partition by...)用在聚合函数（max(),sun()....）后面，可根据pratition by里指定的某一列来统计聚合值

    先通过partiton by 指定的列名进行排序，然后把排序后相同的最为一个组进行计算聚合值

 

SQL> select t.empno, 2 t.deptno, 3 t.ename, 4 t.sal, 5 sum(t.sal) over(partition by t.deptno) sum 6 from emp t; EMPNO DEPTNO ENAME SAL SUM ---------- ---------- ---------- ---------- ---------- 7782 10 CLARK 2450 8750 7839 10 KING 5000 8750 7934 10 MILLER 1300 8750 7566 20 JONES 2975 12875 7902 20 FORD 3000 12875 7876 20 ADAMS 1100 12875 7369 20 SMITH 800 12875 7788 20 SCOTT 5000 12875 7521 30 WARD 1250 9400 7844 30 TURNER 1500 9400 7499 30 ALLEN 1600 9400 7900 30 JAMES 950 9400 7698 30 BLAKE 2850 9400 7654 30 MARTIN 1250 9400 14 rows selected.

 

 

三.一个综合的例子：
exp:
question:
按部门“连续”求总和;
answer:

 

SQL> select t.empno, 2 t.deptno, 3 t.ename, 4 t.sal, 5 sum(t.sal) over(partition by deptno order by ename) sum 6 from emp t 7 / EMPNO DEPTNO ENAME SAL SUM ---------- ---------- ---------- ---------- ---------- 7782 10 CLARK 2450 2450 7839 10 KING 5000 7450 7934 10 MILLER 1300 8750 7876 20 ADAMS 1100 1100 7902 20 FORD 3000 4100 7566 20 JONES 2975 7075 7788 20 SCOTT 5000 12075 7369 20 SMITH 800 12875 7499 30 ALLEN 1600 1600 7698 30 BLAKE 2850 4450 7900 30 JAMES 950 5400 7654 30 MARTIN 1250 6650 7844 30 TURNER 1500 8150 7521 30 WARD 1250 9400 14 rows selected.