开窗函数和聚合函数区别

select ename,sal,sum(sal) over (partition by ename order by sal,empno) as running_total
from emp1
order by 2

按ename 汇总sal

over()开窗函数和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。

SQL> select e.empno,e.ename,e.job,e.sal,e.deptno, sum(e.sal) over (partition by e.deptno) as total_sal
2 from emp e;

     EMPNO ENAME      JOB              SAL     DEPTNO TOTAL_SAL
---------- ---------- --------- ---------- ---------- ----------
      7782 CLARK      MANAGER         2450         10       8750
      7839 KING       PRESIDENT       5000         10       8750
      7934 MILLER     CLERK           1300         10       8750
      7566 JONES      MANAGER         2975         20      10875
      7902 FORD       ANALYST         3000         20      10875
      7876 ADAMS      CLERK           1100         20      10875
      7369 SMITH      CLERK            800         20      10875
      7788 SCOTT      ANALYST         3000         20      10875
      7521 WARD       SALESMAN        1250         30       9400
      7844 TURNER     SALESMAN        1500         30       9400
      7499 ALLEN      SALESMAN        1600         30       9400

     EMPNO ENAME      JOB              SAL     DEPTNO TOTAL_SAL
---------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK            950         30       9400
      7698 BLAKE      MANAGER         2850         30       9400
      7654 MARTIN     SALESMAN        1250         30       9400

已选择14行。

聚合函数:
SQL> select sum(sal) ,deptno from emp group by deptno;

SUM(SAL)     DEPTNO
---------- ----------
      9400         30
     10875         20
      8750         10

SQL>