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

 

许多分析函数同时也是聚合函数，比如sum()函数，这样使用就是聚合函数。

 

SQL> select deptno,sum(sal) sum_sal from emp  group by deptno;

 

而这样使用就是分析函数。

 

SQL> select distinct deptno,sum(sal) over(partition by deptno) sum_sal from employees ;

 

它们得出的结果是相同的，都是：

 

 

DEPTNO	SUM_SAL
30	12800
20	9700
10	4600

 

 

请注意，这里我们用到了distinct 关键字，如果不用distinct，第2个查询将返回14行数据，即emp表的每行记录都将返回一行sum_sal，因为不用distinct的含义是：针对每个雇员计算他/她所在的部门的薪金总数。

 

在这个例子中，聚合函数是更好的选择，但在另外一些情形下，我们更应该使用分析函数。

 

下面通过几个实例来介绍部分分析函数的用途。

 

问题1：求出每个部门工资最高的前3名。

 

利用我们传统的聚合函数max可以方便地取出工资最高的一个员工，但是取出多个就无能为力了，同样，如果不分组我们可以通过排序取出工资最高的前3名，但无法实现对多个部门的分组。而采用rank聚合函数，可以方便地实现我们的要求。

 

完整的语句如下:

select  * from (

select deptno,sal,rank() over(partition by deptno order by sal desc) pm from emp)

where pm<=3

结果为：

 

DEPTNO	SAL	PM
10	1600	1
10	1600	1
10	1400	3
20	3500	1
20	2600	2
20	1500	3
30	2800	1
30	2400	2
30	2300	3

 

 

我们在开窗函数中使用deptno(部门编号)作为分组标志,并按照sal(工资)倒序排列。

 

 

注意:RANK()函数有3组,分别是rank, dense_rank, row_number,它们的区别是：

 

rank如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,比如：当第2名和第3名的利润相同时,rank的结果是1,2,2,4；而dense_rank则不会跳过这个排名,结果是1,2,2,3；而row_number哪怕是两个数据完全相同,排名也会不一样,结果是1,2,3,4

 

 dense_rank()

完整的语句如下：

select ename,deptno,sal,dense_rank() over(partition by deptno order by sal desc) pm from emp

//按部门编号根据工资由高到低排序

结果：

 

ENAME	DEPTNO	SAL	PM
CLARK	10	1600	1
KING	10	1600	1
MILLER	10	1400	2
FORD	20	3500	1
SCOTT	20	2600	2
ADAMS	20	1500	3
SMITH	20	1300	4
JONES	20	800	5
TURNER	30	2800	1
BLAKE	30	2400	2
JAMES	30	2300	3
ALLEN	30	2000	4
MARTIN	30	1800	5
WARD	30	1500	6

 

 

Rank()

完整的语句如下：

select ename,deptno,sal,rank() over(partition by deptno order by sal desc) pm from emp

//按部门编号根据工资由高到低排序

结果：

 

ENAME	DEPTNO	SAL	PM
CLARK	10	1600	1
KING	10	1600	1
MILLER	10	1400	3
FORD	20	3500	1
SCOTT	20	2600	2
ADAMS	20	1500	3
SMITH	20	1300	4
JONES	20	800	5
TURNER	30	2800	1
BLAKE	30	2400	2
JAMES	30	2300	3
ALLEN	30	2000	4
MARTIN	30	1800	5
WARD	30	1500	6

 

 

Row_number()

完整的语句如下：

select ename,deptno,sal,row_number() over(partition by deptno order by sal desc) pm from emp

//按部门编号根据工资由高到底排序

结果：

 

ENAME	DEPTNO	SAL	PM
CLARK	10	1600	1
KING	10	1600	2
MILLER	10	1400	3
FORD	20	3500	1
SCOTT	20	2600	2
ADAMS	20	1500	3
SMITH	20	1300	4
JONES	20	800	5
TURNER	30	2800	1
BLAKE	30	2400	2
JAMES	30	2300	3
ALLEN	30	2000	4
MARTIN	30	1800	5
WARD	30	1500	6

 

 

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

 

许多分析函数同时也是聚合函数，比如sum()函数，这样使用就是聚合函数。

 

SQL> select deptno,sum(sal) sum_sal from emp  group by deptno;

 

而这样使用就是分析函数。

 

SQL> select distinct deptno,sum(sal) over(partition by deptno) sum_sal from employees ;

 

它们得出的结果是相同的，都是：

 

DEPTNO	SUM_SAL
30	12800
20	9700
10	4600

 

请注意，这里我们用到了distinct 关键字，如果不用distinct，第2个查询将返回14行数据，即emp表的每行记录都将返回一行sum_sal，因为不用distinct的含义是：针对每个雇员计算他/她所在的部门的薪金总数。

 

在这个例子中，聚合函数是更好的选择，但在另外一些情形下，我们更应该使用分析函数。

 

下面通过几个实例来介绍部分分析函数的用途。

 

问题1：求出每个部门工资最高的前3名。

 

利用我们传统的聚合函数max可以方便地取出工资最高的一个员工，但是取出多个就无能为力了，同样，如果不分组我们可以通过排序取出工资最高的前3名，但无法实现对多个部门的分组。而采用rank聚合函数，可以方便地实现我们的要求。

 

完整的语句如下:

select  * from (

select deptno,sal,rank() over(partition by deptno order by sal desc) pm from emp)

where pm<=3

结果为：

DEPTNO	SAL	PM
10	1600	1
10	1600	1
10	1400	3
20	3500	1
20	2600	2
20	1500	3
30	2800	1
30	2400	2
30	2300	3

 

我们在开窗函数中使用deptno(部门编号)作为分组标志,并按照sal(工资)倒序排列。