关于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)no from test_4;

ID DEPT        SAL         NO
-- ---- ---------- ----------
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()  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,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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13387766/viewspace-402564/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13387766/viewspace-402564/