Oracle DB rownum & row_number() & rank() & dense_rank()

最近跟Oracle数据库打了个小小的交道。有个表数据量太大。需要保留每个entry的最近10次结果，其余的历史结果全部删掉。
因为我们的场景很复杂，花了个把小时写query，成功删除了800多万条数据。

删好之后，觉得自己对于rownum, row_number, rank()等的理解并不深刻，于是谷歌了一番，仔细理解了下。现把自己的理解写下来，供以后参考。

先把Oracle官方文档贴出来：
Rownum https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm
Row_number() https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm#i86310
Dense_rank()https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions043.htm
Rank()https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions123.htm

Rownum

Rownum的使用场景其实很有限。最常见就是top-N：
select * from xxtable where rownum < 10
如果想要order by某个列，在子句里order by,在外层主句里加rownum的条件：
SELECT * FROM
(SELECT * FROM employees ORDER BY employee_id)
WHERE ROWNUM < 11;

因为如果直接写SELECT * FROM employees WHERE ROWNUM < 11 ORDER BY last_name; 的话，结果可能会跟预期不一样。根据Oracle官方文档，order by子句可能会唤起index取数，那它的前10条记录，可能跟不走index取数的前10条记录不一样（我不是DB专家，还真是不太明白这块）。总之，为了能够确切取到order by某个列的前N列，就应该用子句order by 外加主句 rownum条件。

如果rownum 条件写 rownum > N，则一条数据都取不到。因为取到的第一条数据rownum = 1, 它不符合 > N的条件，不会被返回。第二条数据就变成了第一条数据，它的rownum = 1,还是不会被返回。以此类推，没有任何一条数据会被返回。

如果想要达到bottom N的效果，还是得用子句的方式来实现，在子句里选出rownum这一列来，在主句里加条件：
select * from(
select rownum as rn, a.* from xxtable a where id = 20004 order by ref_Id desc)
where rn > 100

如果场景较复杂，需要聚合外加Top/Bottom/Inner条数限制，那么我建议，聚合写在子句里，然后在主句里加rownum 限制。我试过将它们写在一层，结果非常稀奇古怪，根本不是自己想要的。
select * from (
select parent_id, count(distinct scheduler_id) as amount from ref_column_mapping group by parent_id having count(distinct scheduler_id) > 5 order by amount desc)
where rownum < 4

Row_Number()

Row_Number()的话高级些，是个方法，可以基于某个partition进行进行编号，官方文档给的例子就很能说明问题。每个partition从1开始编号，并且同一个partition内即便有相同记录，也不会有相同的rownum.比如department id = 10 的partition里，有两个employee id = 199的记录（当然不会发生，只是举个例子），基于谁先取出来，谁的rownum就小一号，另一条大一号。
SELECT department_id, last_name, employee_id, ROW_NUMBER()
OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id
FROM employees;

DEPARTMENT_ID	LAST_NAME	EMPLOYEE_ID	EMP_ID
10	Whalen	200	1
20	Hartstein	201	1
20	Fay	202	2
30	Raphaely	114	1
30	Khoo	115	2
30	Baida	116	3
30	Tobias	117	4
30	Himuro	118	5
30	Colmenares	119	6
40	Mavris	203	1
…
100	Popp	113	6
110	Higgins	205	1
110	Gietz	206	2

Rank() & Dense_Rank()

另外还有两个方法，分别是rank() 和 dense_rank()，它们的作用是，在某个partition内，基于某个列（order by）进行排序，然后返回序号。它们俩的区别是：rank()排出来的序号可能是不连续的，比如1,2,2,2,5,6. Dense_rank()排出来的序号肯定是连续的，比如1,2,2,2,3,4.
Stack over flow上面有个帖子，里面的例子非常的好，一目了然：https://stackoverflow.com/questions/11183572/whats-the-difference-between-rank-and-dense-rank-functions-in-oracle
with q as (
select 10 deptno, ‘rrr’ empname, 10000.00 sal from dual union all
select 11, ‘nnn’, 20000.00 from dual union all
select 11, ‘mmm’, 5000.00 from dual union all
select 12, ‘kkk’, 30000 from dual union all
select 10, ‘fff’, 40000 from dual union all
select 10, ‘ddd’, 40000 from dual union all
select 10, ‘bbb’, 50000 from dual union all
select 10, ‘xxx’, null from dual union all
select 10, ‘ccc’, 50000 from dual)
select empname, deptno, sal
, rank() over (partition by deptno order by sal nulls first) r
, dense_rank() over (partition by deptno order by sal nulls first) dr1
, dense_rank() over (partition by deptno order by sal nulls last) dr2
from q;

EMP	DEPTNO	SAL	Rank	Dr1	Dr2
xxx	10		1	1	4
rrr	10	10000	2	2	1
fff	10	40000	3	3	2
ddd	10	40000	3	3	2
ccc	10	50000	5	4	3
bbb	10	50000	5	4	3
mmm	11	5000	1	1	1
nnn	11	20000	2	2	2
kkk	12	30000	1	1	1

9 rows selected.

Rank() & Dense_rank() 与Row_number()的区别在于： 某个partition内，前者的序号可能重复，后者不可能重复（即便order by 某个列之后有两行记录的这个字段是一样的）。