over(partition by..)的排序

over(partition by..) 中 over 是分析函数
over(partition by..)是种功能强大的分组函数

 

作例子的表结构是这样：

SQL> select * from test_4;

ID DEPT        SAL
-- ---- ----------
a IT         2000
b IT         2000
c PUR        1000
d PUR         500
e MA         4000
f MA         4000
g IT         1500
H MA         3000

（1）row_number排序号没有重复。

SQL> select id,dept,sal,row_number()over(partition by dept order by sal desc) rowno from test_4;

ID DEPT        SAL         rowno
-- ---- ---------- ----------
a IT         2000          1
b IT         2000          2
g IT         1500          3
e MA         4000          1
f MA         4000          2
H MA         3000          3
c PUR        1000          1
d PUR         500          2

不按部门分开来排，则是：

SQL> select id,dept,sal,row_number( )over(order by sal desc) no from test_4;

ID DEPT        SAL         NO
-- ---- ---------- ----------
e MA         4000          1
f MA         4000          2
H MA         3000          3
a IT         2000          4
b IT         2000          5
g IT         1500          6
c PUR        1000          7
d PUR         500          8

(2)rank( ) 序号如果有重复的就直接跨越，比如有两个2,之后就是4.

SQL> select id,dept,sal,rank()over(partition by dept order by sal desc)no from test_4;

ID DEPT        SAL         NO
-- ---- ---------- ----------
a IT         2000          1
b IT         2000          1
g IT         1500          3
e MA         4000          1
f MA         4000          1
H MA         3000          3
c PUR        1000          1
d PUR         500          2

3)dense_rank() 序号也一样可以重复，只不过序号有重复的也是接着排序号，没有跨越，比如有两个2,之后依然是3,而不是4.

SQL> select id,dept,sal,dense_rank()over(partition by dept order by sal desc)no from test_4;

ID DEPT        SAL         NO
-- ---- ---------- ----------
a IT         2000          1
b IT         2000          1
g IT         1500          2
e MA         4000          1
f MA         4000          1
H MA         3000          2
c PUR        1000          1
d PUR         500          2

(4) lag(arg1,arg2,arg3)     就是你某个列，比如sal，在排序后想拿前面行的值，就是用这个，arg1就是写列名，arg2就是跨出前面几行拿，比如2就去拿当前行前面2行的对应列的值，arg3就是所找的行超出范围后给的一个默认值。

SQL> select id,dept,sal,lag(sal,1,0)over(order by sal desc) pre_sal from test_4;

ID DEPT        SAL    PRE_SAL
-- ---- ---------- ----------
e MA         4000          0
f MA         4000       4000
H MA         3000       4000
a IT         2000       3000
b IT         2000       2000
g IT         1500       2000
c PUR        1000       1500
d PUR         500       1000

如果跨越两行拿值，则是：

SQL> select id,dept,sal,lag(sal,2,0) over (order by sal desc)pre_sal from test_4;

ID DEPT        SAL    PRE_SAL
-- ---- ---------- ----------
e MA         4000          0
f MA         4000          0
H MA         3000       4000
a IT         2000       4000
b IT         2000       3000
g IT         1500       2000
c PUR        1000       2000
d PUR         500       1500

如果想看排序后每相仿两个人的工资之差，则用：

SQL> select id,dept,sal,lag(sal,1,0) over (order by sal ) pre_sal,sal-lag(sal,1,0) over (order by sal) balance from test_4;

ID DEPT        SAL    PRE_SAL    BALANCE
-- ---- ---------- ---------- ----------
d PUR         500          0        500
c PUR        1000        500        500
g IT         1500       1000        500
a IT         2000       1500        500
b IT         2000       2000          0
H MA         3000       2000       1000
e MA         4000       3000       1000
f MA         4000       4000          0