分析函数学习二:Rank, Dense_rank, row_number

1、介绍Rank, Dense_rank, row_number

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

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

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

2、体会下三种排序方式

select rank() over(order by sal desc) rank,
       dense_rank() over(order by sal desc) dense_rank,
       row_number() over(order by sal desc) row_number,
       a.*
  from emp a;

RANK	DENSE_RANK	ROW_NUMBER	EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	ID
1	1	1	7839	KING	PRESIDENT		1981-11-17 0024:0i:00	5000.00		10	9
2	2	2	7902	FORD	ANALYST	7566	2023-01-03 1124:42i:39 11:42:39	3000.00		20	13
2	2	3	7788	SCOTT	ANALYST	7566	1987-04-19 0024:0i:00	3000.00		20	8
4	3	4	7566	JONES	MANAGER	7839	1981-04-02 0024:0i:00	2975.00		20	4
4	3	5	7782	CLARK	MANAGER	7839	1981-06-09 0024:0i:00	2975.00		10	7
4	3	6	7698	BLAKE	MANAGER	7839	1981-05-01 0024:0i:00	2975.00		30	6
7	4	7	7499	ALLEN	SALESMAN	7698	1981-02-20 0024:0i:00	1600.00	300.00	30	2
8	5	8	7844	TURNER	SALESMAN	7698	1981-09-08 0024:0i:00	1500.00	0.00	30	10
9	6	9	7934	MILLER	CLERK	7782	2023-01-04 1124:37i:48 11:37:48	1300.00		10	14
10	7	10	7521	WARD	SALESMAN	7698	1981-02-22 0024:0i:00	1250.00	500.00	30	3
10	7	11	7654	MARTIN	SALESMAN	7698	1981-09-28 0024:0i:00	1250.00	1400.00	30	5
12	8	12	7876	ADAMS	CLERK	7788	1987-05-23 0024:0i:00	1100.00		20	11
13	9	13	7900	JAMES	CLERK	7698	1981-12-03 0024:0i:00	950.00		30	12
14	10	14	8000	DUWEI	CLERK	7782	2023-01-05 1124:13i:22 11:13:22	800.00		10	15
14	10	15	7369	SMITH	CLERK	7902	1980-12-17 0024:0i:00	800.00		20	1

--加入了常规分组聚合函数，来展示 对所有员工按工资总额进行排名

select deptno,
       job,
       sum(sal) job_sal,
       rank() over(order by sum(sal) desc) rank,
       dense_rank() over(order by sum(sal) desc) dense_rank,
       row_number() over(order by sum(sal) desc) row_number
  from emp
 group by deptno,job;DEPTNO	JOB	JOB_SAL	RANK	DENSE_RANK	ROW_NUMBER
20	ANALYST	6000	　　　　1	1	1
30	SALESMAN	5600	2	2	2
10	PRESIDENT	5000	3	3	3
30	MANAGER	2975	　　　　4	4	4
10	MANAGER	2975	　　　　4	4	5
20	MANAGER	2975	　　　　4	4	6
10	CLERK	2100	　　　　7	5	7
20	CLERK	1900	　　　　8	6	8
30	CLERK	950	　　　　9	7	9

-- 可以看出1-4大家都一样的排名，第五行时 rank 和 dense_rank一致，row_number是继续排名为5，
-- 第六行时 rank 和 dense_rank一致，row_number是继续排名为6
-- 第七行时 rank 为7，dense_rank 为5 ，row_number是继续排名为7

--使用分析函数分组排名，来展示 按部门和员工订单总额进行排名select deptno,

       job,
       sum(sal) job_sal,
       rank() over(partition by deptno order by sum(sal) desc) rank,
       dense_rank() over(partition by deptno order by sum(sal) desc) dense_rank,
       row_number() over(partition by deptno order by sum(sal) desc) row_number
  from emp
 group by deptno,job;
DEPTNO	JOB	JOB_SAL　　　　	RANK	DENSE_RANK	ROW_NUMBER
10	PRESIDENT	5000	1	1	1
10	MANAGER	2975	　　　　2	2	2
10	CLERK	2100	　　　　3	3	3
20	ANALYST	6000	　　　　1	1	1
20	MANAGER	2975	　　　　2	2	2
20	CLERK	1900	　　　　3	3	3
30	SALESMAN	5600	1	1	1
30	MANAGER	2975	　　　　2	2	2
30	CLERK	950	　　　　3	3	3

现在我们看到的排名将是基于各个部门 deptno 的，而非所有部门的了！
Partition by 子句在排列函数中的作用是将一个结果集划分成几个部分，这样排列函数就能够应用于这各个子集。

3、总结结论：

比较上面3种不同的策略，我们在选择的时候就要根据员工的需求来定夺了：
假如员工就只需要指定数目的记录，那么采用row_number是最简单的，但有漏掉的记录的危险
假如员工需要所有达到排名水平的记录，那么采用rank或dense_rank是不错的选择。至于选择哪一种则看用户的需要，选择dense_rank或得到最大的记录