ITPub博客

首页 > Linux操作系统 > Linux操作系统 > over(partition by..)的排序

over(partition by..)的排序

原创 Linux操作系统 作者:perfychi 时间:2013-08-20 14:21:05 0 删除 编辑

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

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

请登录后发表评论 登录
全部评论

注册时间:2012-06-06

  • 博文量
    486
  • 访问量
    2715637