over(partition by..)的排序
over(partition by..) 中 over 是分析函数
over(partition by..)是种功能强大的分组函数
作例子的表结构是这样:
SQL> select * from test_4;
-- ---- ----------
a IT 2000
b IT 2000
c PUR 1000
d PUR 500
e MA 4000
f MA 4000
g IT 1500
H MA 3000
![ff4574a1-3413-4e10-ac43-98dc1afa9316.png?resizeSmall&width=786](https://www.evernote.com/shard/s125/res/ff4574a1-3413-4e10-ac43-98dc1afa9316.png?resizeSmall&width=786)
(1)row_number排序号没有重复。
SQL> select id,dept,sal, row_number()over(partition by dept order by sal desc) rowno from test_4;
-- ---- ---------- ----------
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
![0a4f51b8-aba6-4dd6-8783-6370a133c8c7.png?resizeSmall&width=786](https://www.evernote.com/shard/s125/res/0a4f51b8-aba6-4dd6-8783-6370a133c8c7.png?resizeSmall&width=786)
不按部门分开来排(则是按整体结果集排序,把整体看成一个大组),则是:
SQL> select id,dept,sal, row_number( ) over(order by sal desc) no from test_4;
-- ---- ---------- ----------
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
![8e7c928c-0864-4f03-a7e0-f0aff434e62d.png?resizeSmall&width=786](https://www.evernote.com/shard/s125/res/8e7c928c-0864-4f03-a7e0-f0aff434e62d.png?resizeSmall&width=786)
(2)rank( ) 序号如果有重复的就直接跨越,比如有两个2,之后就是4.
SQL> select id,dept,sal, rank() over(partition by dept order by sal desc) no from test_4;
-- ---- ---------- ----------
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
![94e6d45c-b3ae-4e8f-a28e-40b29435ccdb.png?resizeSmall&width=786](https://www.evernote.com/shard/s125/res/94e6d45c-b3ae-4e8f-a28e-40b29435ccdb.png?resizeSmall&width=786)
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;
-- ---- ---------- ----------
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
![cd2abb62-f737-443c-827a-effee75e8593.png?resizeSmall&width=786](https://www.evernote.com/shard/s125/res/cd2abb62-f737-443c-827a-effee75e8593.png?resizeSmall&width=786)
(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;
-- ---- ---------- ----------
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
![10ccc48d-35d3-4a51-9796-47b0ed5d0974.png?resizeSmall&width=786](https://www.evernote.com/shard/s125/res/10ccc48d-35d3-4a51-9796-47b0ed5d0974.png?resizeSmall&width=786)
如果跨越两行拿值,则是:
SQL> select id,dept,sal, lag(sal,2,0) over (order by sal desc) pre_sal from test_4;
-- ---- ---------- ----------
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
![a8eb905a-bbee-4a30-8073-d50ae347dd25.png?resizeSmall&width=786](https://www.evernote.com/shard/s125/res/a8eb905a-bbee-4a30-8073-d50ae347dd25.png?resizeSmall&width=786)
如果想看排序后每相仿两个人的工资之差,则用:
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;
-- ---- ---------- ---------- ----------
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
![1bfe2eda-968c-475a-a273-98f1a32e2342.png?resizeSmall&width=786](https://www.evernote.com/shard/s125/res/1bfe2eda-968c-475a-a273-98f1a32e2342.png?resizeSmall&width=786)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27042095/viewspace-768880/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27042095/viewspace-768880/