已归录
分析函数为分析数据提供了一种简单高效的处理方式，之前只能使用自连接、子查询等方式实现的复杂查询功能利用分析函数只需用简单一条 SQL 就可以搞定，且效率也会比普通查询有很大提高。
over 关键字标记这是一个分析函数，因为聚集函数和分析函数是重复的，比如 SUM、AVG 等，默认 oracle 认为是聚集函数，必须用 over 指明这是分析函数。
over 里的 partition by 用于分组，类似 group by，order by 用于组内排序。
下面通过几个例子来掌握分析函数的使用方法。
例1：要求打印每个员工的姓名，工资，部门编号及所在部门工资总和。
SQL> select deptno,
ename,
sal,
sum(sal) over(partition by deptno) sum_sal
from emp;
DEPTNO ENAME SAL SUM_SAL
---------- ---------- ---------- ----------
10 CLARK 2450 8750
10 KING 5000 8750
10 MILLER 1300 8750
20 JONES 2975 10875
20 FORD 3000 10875
20 ADAMS 1100 10875
20 SMITH 800 10875
20 SCOTT 3000 10875
30 WARD 1250 9400
30 TURNER 1500 9400
30 ALLEN 1600 9400
30 JAMES 950 9400
30 BLAKE 2850 9400
30 MARTIN 1250 9400
14 rows selected.同理，可以打印出每个员工的姓名，工资，部门编号及所在部门工资总和、平均工资、员工人数。
SQL> select deptno,
ename,
sal,
sum(sal) over(partition by deptno) sum_sal,
round(avg(sal) over(partition by deptno)) avg_sal,
count(*) over(partition by deptno) count_emp
from emp;例2：要求打印每个员工的姓名，工资，部门编号及所在部门工资总和，部门内按照工资排序。
SQL> select deptno,
ename,
sal,
sum(sal) over(partition by deptno order by sal) sum_sal
from emp;
DEPTNO ENAME SAL SUM_SAL
---------- ---------- ---------- ----------
10 MILLER 1300 1300
10 CLARK 2450 3750
10 KING 5000 8750
20 SMITH 800 800
20 ADAMS 1100 1900
20 JONES 2975 4875
20 SCOTT 3000 10875
20 FORD 3000 10875
30 JAMES 950 950
30 MARTIN 1250 3450
30 WARD 1250 3450
30 TURNER 1500 4950
30 ALLEN 1600 6550
30 BLAKE 2850 9400
14 rows selected.注意 SUM_SAL 列，它是组内累积求和的。如果不想累积求和呢？两种方法：
方法一：分组求和后，对总结果进行多列排序
select deptno,
ename,
sal,
sum(sal) over(partition by deptno) sum_sal
from emp
order by deptno, sal;方法二：分组求和后，在外面嵌套一层，在外面进行组内排序：
select deptno,
ename,
sal,
sum_sal,
row_number() over(partition by deptno order by sal desc) as drn
from (
select deptno,
ename,
sal,
sum(sal) over(partition by deptno) sum_sal
from emp);例3：取出每个部门的最高工资的前两名员工。
分析函数 row_number 可以对分组后的每个组内记录产生行号，即每个组都是一套全新的行号。
SQL> select deptno,
ename,
sal
from (select deptno,
ename,
sal,
row_number() over(partition by deptno order by sal desc) drn
from emp)
where drn <=2;
DEPTNO ENAME SAL
---------- ---------- ----------
10 KING 5000
10 CLARK 2450
20 SCOTT 3000
20 FORD 3000
30 BLAKE 2850
30 ALLEN 1600
6 rows selected.row_number() 就是将值直接按照1，2，3，4……往后面排，不管是否有重复值，有时根据需要，我们还要使用到 rank 和 dense_rank。
rank 形如：1, 2, 2, 4
dense_rank 形如：1, 2, 2, 3
即 rank 和 dense_rank 的区别:
他们遇到相同值不会递增，都显示当前序列值
遇到相同值 rank 会跳号，dense_rank 不跳号
下面查询会对员工按进行部门内部排序(rownumber、ransk、dense_rank)：
SQL> select deptno,
ename,
sal,
row_number() over(partition by deptno order by sal desc) rownumber_sal,
rank() over(partition by deptno order by sal desc) rank_sal,
dense_rank() over(partition by deptno order by sal desc) dense_rank
from emp;
DEPTNO ENAME SAL ROWNUMBER_SAL RANK_SAL DENSE_RANK
---------- ---------- ---------- ------------- ---------- ----------
10 KING 5000 1 1 1
10 CLARK 2450 2 2 2
10 MILLER 1300 3 3 3
20 SCOTT 3000 1 1 1
20 FORD 3000 2 1 1
20 JONES 2975 3 3 2
20 ADAMS 1100 4 4 3
20 SMITH 800 5 5 4
30 BLAKE 2850 1 1 1
30 ALLEN 1600 2 2 2
30 TURNER 1500 3 3 3
30 MARTIN 1250 4 4 4
30 WARD 1250 5 4 4
30 JAMES 950 6 6 5
14 rows selected.巩固练习：计算部门各员工工资跟部分平均工资的差值，找出拖部门工资后腿最严重的那个人。
select * from (
select deptno,
ename,
sal,
dept_avg_sal,
diff,
row_number() over(partition by deptno order by diff) drn
from(
select deptno,
ename,
sal,
avg(sal) over(partition by deptno) dept_avg_sal,
sal - avg(sal) over(partition by deptno) diff
from emp))
where drn=1;