Oracle的分析函数over

 Oracle分析函数over的应用很广泛。 

 用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。

 经常与sum,rank等函数同事出现。

  例子：

  soctt的emp表为例：需要获得每个部门的总的sal。则

  SELECT empno, ename, deptno,SUM(sal) OVER(PARTITION BY deptno) AS  deptpn_sal FROM emp

   结果如图：

1 7782 CLARK 10 8750
2 7839 KING 10 8750
3 7934 MILLER 10 8750
4 7566 JONES 20 10875
5 7902 FORD 20 10875
6 7876 ADAMS 20 10875
7 7369 SMITH 20 10875
8 7788 SCOTT 20 10875
9 7521 WARD 30 9400
10 7844 TURNER 30 9400
11 7499 ALLEN 30 9400
12 7900 JAMES 30 9400
13 7698 BLAKE 30 9400
14 7654 MARTIN 30 9400

 

   如果需要按人员id进行累计，则

  SELECT empno, ename, deptno,SUM(sal) OVER(PARTITION BY deptno ORDER BY empno) AS  deptpn_sal FROM emp

  结果如下：

1 7782 CLARK 10 2450
2 7839 KING 10 7450
3 7934 MILLER 10 8750
4 7369 SMITH 20 800
5 7566 JONES 20 3775
6 7788 SCOTT 20 6775
7 7876 ADAMS 20 7875
8 7902 FORD 20 10875
9 7499 ALLEN 30 1600
10 7521 WARD 30 2850
11 7654 MARTIN 30 4100
12 7698 BLAKE 30 6950
13 7844 TURNER 30 8450
14 7900 JAMES 30 9400

 

rank 示例如下

SELECT empno, ename, deptno, sal,RANK() OVER(PARTITION BY deptno ORDER BY sal) AS  deptpn_sal FROM emp

结果

1 7934 MILLER 10 1300.00 1
2 7782 CLARK 10 2450.00 2
3 7839 KING 10 5000.00 3
4 7369 SMITH 20 800.00 1
5 7876 ADAMS 20 1100.00 2
6 7566 JONES 20 2975.00 3
7 7788 SCOTT 20 3000.00 4
8 7902 FORD 20 3000.00 4
9 7900 JAMES 30 950.00 1
10 7654 MARTIN 30 1250.00 2
11 7521 WARD 30 1250.00 2
12 7844 TURNER 30 1500.00 4
13 7499 ALLEN 30 1600.00 5
14 7698 BLAKE 30 2850.00 6

 

这里在提一下rank与row_number、dense_rank的区别

①ROW_NUMBER：

Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。 

②DENSE_RANK：
Dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。 

③RANK：
Rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。