oracle 分析函数

Question

15:00:29 SQL> select col2,col1 from tb;COL2                           COL1
------------------------------ ----------
aaa                            1
aaa                            2
aaa                            3
bba                            4
bbb                            5
bbb                            8已选择6行。已用时间:  00: 00: 00.19
15:00:50 SQL> select col2,col1,rank() over(partition by col2 order by col1 desc) rk from tb;COL2                           COL1               RK
------------------------------ ---------- ----------
aaa                            3                   1
aaa                            2                   2
aaa                            1                   3
bba                            4                   1
bbb                            8                   1
bbb                            5                   2已选择6行。已用时间:  00: 00: 00.63
15:01:23 SQL> 可以看出，结果的rk列是根据col2进行分组，根据col1降序排列后 的顺序号。

Answer 1

如果不加partition by col2，则是对整个记录集 按照col2排序后的 顺序号。15:01:23 SQL> select col2,col1,rank() over(order by col1 desc) rk from tb;COL2                           COL1               RK
------------------------------ ---------- ----------
bbb                            8                   1
bbb                            5                   2
bba                            4                   3
aaa                            3                   4
aaa                            2                   5
aaa                            1                   6已选择6行。已用时间:  00: 00: 00.63
15:03:04 SQL>

Answer 2

partition by可理解为group by，就是分组