分析函数rank,dense_rank,row_number

一.RANK聚合函数
1.语法


RANK(cntexpr1[,cntexpr2,...,cntexprn]) WITHIN GROUP (ORDER BY expr1[DESC|ASC][,expr2[DESC|ASC],...,exprn[DESC|ASC])
2.说明


计算给定组合,在一个排序集合中的所处的等级位置.
①cntexpr系列要求是常量
②相同的数值所处等级值相同
③ 下一等级值是前面所有等级记录数量加1
3.例子

SQL> select * from test;

F1 F2
---------- ----------
1 1
1 2
2 1
2 2
2 3
3 1
3 2
3 3
4 1
4 2
5 1

已选择11行。

SQL> select rank(0) within group(order by f1) r0,
2 rank(1) within group(order by f1) r1,
3 rank(2) within group(order by f1) r2,
4 rank(3) within group(order by f1) r3,
5 rank(4) within group(order by f1) r4,
6 rank(5) within group(order by f1) r5
7 from test;

R0 R1 R2 R3 R4 R5
---------- ---------- ---------- ---------- ---------- ----------
1 1 3 6 9 11


SQL> select rank(2,3) within group(order by f1,f2) r22,
2 rank(4,1) within group(order by f1,f2) r41 from test;

R22 R41
---------- ----------
5 9


SQL> select rank(2,3) within group(order by f1,f2 desc) r22,
2 rank(4,1) within group(order by f1,f2 desc) r41 from test;

R22 R41
---------- ----------
3 10


二.RANK分析函数
1.语法


RANK() OVER([PARTITION BY expr1[,expr2,..,exprn]] ORDER BY expr1[,expr2,..,exprn])
2.目的


先将记录按PARTITION分组,组内再以ORDER BY排序,算出当前记录在组内所处的级别.
PRATITION条件省略时,表明不分组,或将全录记录作为一组
3.例子

SQL> select f1,f2,
2 rank() over(order by f1) r1,
3 rank() over(order by f1,f2) r12 from test;

F1 F2 R1 R12
---------- ---------- ---------- ----------
1 1 1 1
1 2 1 2
2 1 3 3
2 2 3 4
2 3 3 5
3 1 6 6
3 2 6 7
3 3 6 8
4 1 9 9
4 2 9 10
5 1 11 11

SQL> select f1,f2,rank() over(partition by f1 order by f2) r_pf1_f2 from test;

F1 F2 R_PF1_F2
---------- ---------- ----------
1 1 1
1 2 2
2 1 1
2 2 2
2 3 3
3 1 1
3 2 2
3 3 3
4 1 1
4 2 2
5 1 1


SQL> select f1,f2,rank() over(partition by f2 order by f1) r_pf2_f1 from test;

F1 F2 R_PF2_F1
---------- ---------- ----------
1 1 1
2 1 2
3 1 3
4 1 4
5 1 5
1 2 1
2 2 2
3 2 3
4 2 4
2 3 1
3 3 2

三.ROW_NUMBER函数
1.语法


ROW_NUMBER() OVER([PARTITION BY expr1[,expr2,..,exprn]] ORDER BY expr1[,expr2,..,exprn])
2.说明


相同的记录也会有不同的ROW_NUMBER值,ROW_NUMBER值是连续的
3.例子

SQL> select f1,f2,
2 row_number() over(order by f1) r1,
3 row_number() over(order by f1,f2) r12 from test;

F1 F2 R1 R12
---------- ---------- ---------- ----------
1 1 1 1
1 2 2 2
2 1 3 3
2 2 4 4
2 3 5 5
3 1 6 6
3 2 7 7
3 3 8 8
4 1 9 9
4 2 10 10
5 1 11 11

已选择11行。

SQL> select f1,f2,row_number() over(partition by f1 order by f2) r_pf1_f2 from test;

F1 F2 R_PF1_F2
---------- ---------- ----------
1 1 1
1 2 2
2 1 1
2 2 2
2 3 3
3 1 1
3 2 2
3 3 3
4 1 1
4 2 2
5 1 1

已选择11行。

SQL> select f1,f2,row_number() over(partition by f2 order by f1) r_pf2_f1 from test;

F1 F2 R_PF2_F1
---------- ---------- ----------
1 1 1
2 1 2
3 1 3
4 1 4
5 1 5
1 2 1
2 2 2
3 2 3
4 2 4
2 3 1
3 3 2

已选择11行。

四.DENSE_RANK聚合函数
1.语法


DENSE_RANK(cntexpr1[,cntexpr2,...,cntexprn]) WITHIN GROUP (ORDER BY expr1[DESC|ASC][,expr2[DESC|ASC],...,exprn[DESC|ASC])
2.说明


计算给定组合,在一个排序集合中的所处的等级位置.
①cntexpr系列要求是常量
②相同的数值所处等级值相同
③ 等级值是连续的,从1开始.
3.例子

SQL> select dense_rank(0) within group(order by f1) r0,
2 dense_rank(1) within group(order by f1) r1,
3 dense_rank(2) within group(order by f1) r2,
4 dense_rank(3) within group(order by f1) r3,
5 dense_rank(4) within group(order by f1) r4,
6 dense_rank(5) within group(order by f1) r5
7 from test;

R0 R1 R2 R3 R4 R5
---------- ---------- ---------- ---------- ---------- ----------
1 1 2 3 4 5

五.DENSE_RANK分析函数
1.语法


DENSE_RANK() OVER([PARTITION BY expr1[,expr2,..,exprn]] ORDER BY expr1[,expr2,..,exprn])
2.目的


先将记录按PARTITION分组,组内再以ORDER BY排序,算出当前记录在组内所处的级别.
PRATITION条件省略时,表明不分组,或将全录记录作为一组
3.例子

SQL> select f1,f2,
2 dense_rank() over(order by f1) r1,
3 dense_rank() over(order by f1,f2) r12 from test;

F1 F2 R1 R12
---------- ---------- ---------- ----------
1 1 1 1
1 2 1 2
2 1 2 3
2 2 2 4
2 3 2 5
3 1 3 6
3 2 3 7
3 3 3 8
4 1 4 9
4 2 4 10
5 1 5 11

已选择11行。

SQL> select f1,f2,dense_rank() over(partition by f1 order by f2) r_pf1_f2 from t
est;

F1 F2 R_PF1_F2
---------- ---------- ----------
1 1 1
1 2 2
2 1 1
2 2 2
2 3 3
3 1 1
3 2 2
3 3 3
4 1 1
4 2 2
5 1 1

已选择11行。

SQL> select f1,f2,dense_rank() over(partition by f2 order by f1) r_pf2_f1 from t
est;

F1 F2 R_PF2_F1
---------- ---------- ----------
1 1 1
2 1 2
3 1 3
4 1 4
5 1 5
1 2 1
2 2 2
3 2 3
4 2 4
2 3 1
3 3 2

已选择11行。