1. 首页
  2. IT资讯

over(partition by..)的排序

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
over(partition by..)的排序

(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
over(partition by..)的排序

不按部门分开来排(则是按整体结果集排序,把整体看成一个大组),则是:

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
over(partition by..)的排序

(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
over(partition by..)的排序

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
over(partition by..)的排序

(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
over(partition by..)的排序

如果跨越两行拿值,则是:

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
over(partition by..)的排序

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

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
over(partition by..)的排序

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

主题测试文章,只做测试使用。发布者:布吉卡,转转请注明出处:http://www.cxybcw.com/195915.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code