over(Partition by...) 详细用法
来源:互联网 发布:精算师 考试 知乎 编辑:程序博客网 时间:2024/05/20 16:37
row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)
示例:
14:33:29 SQL> select type,qty from test;
TYPE QTY
---------- ----------
1 3
1 6
2 5
2 9
2 7
14:33:36 SQL> select type,qty,to_char(row_number() over(partition by type order by qty))||'/'||to_char(count(*) over(partition by type)) as cnt2 from test;
TYPE QTY CNT2
---------- ---------- ------------
1 3 1/2
1 6 2/2
2 5 1/3
2 7 2/3
2 9 3/3
SQL> select * from test;
ID MC
---------- --------------------------------------------------
1 11111
2 22222
3 33333
4 44444
SQL>
SQL> select t.id,mc,to_char(b.rn)||'/'||t.id
2 from test t,
3 (select rownum rn from (select max(to_number(id)) mid from test) connect by rownum <=mid ) b
4 where b.rn<=to_number(t.id)
5 order by id,3
6 /
ID MC TO_CHAR(B.RN)||'/'||T.ID
---------- -------------------------------------------------- ---------------------------------------------------
1 11111 1/1
2 22222 1/2
2 22222 2/2
3 33333 1/3
3 33333 2/3
3 33333 3/3
4 44444 1/4
4 44444 2/4
4 44444 3/4
4 44444 4/4
10 rows selected
关于partition by
这些都是分析函数,好像是8.0以后才有的 row_number()和rownum差不多,功能更强一点(可以在各个分组内从1开时排序) rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内) dense_rank()l是连续排序,有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 lag(arg1,arg2,arg3): arg1是从其他行返回的表达式 arg2是希望检索的当前行分区的偏移量。是一个正的偏移量,时一个往回检索以前的行的数目。 arg3是在arg2表示的数目超出了分组的范围时返回的值。
1.
select deptno,row_number() over(partition by deptno order by sal) from emp order by deptno;
2.
select deptno,rank() over (partition by deptno order by sal) from emp order by deptno;
3.
select deptno,dense_rank() over(partition by deptno order by sal) from emp order by deptno;
4.
select deptno,ename,sal,lag(ename,1,null) over(partition by deptno order by ename) from emp ord er by deptno;
5.
select deptno,ename,sal,lag(ename,2,'example') over(partition by deptno order by ename) from em p
order by deptno;
6.
7. 求每个部门的平均工资以及每个人与所在部门的工资差额
select deptno,ename,sal ,
- over(Partition by...) 详细用法
- over(Partition by...) 详细用法
- over(Partition by...) 详细用法
- over partition by 用法
- OVER(PARTITION BY)函数用法
- OVER(PARTITION BY)函数用法
- OVER(PARTITION BY)函数用法
- OVER(PARTITION BY)函数用法
- OVER(PARTITION BY)函数用法
- oracle over partition by用法
- OVER(PARTITION BY)函数用法
- OVER(PARTITION BY)函数用法
- OVER(PARTITION BY)函数用法
- OVER(PARTITION BY)函数用法
- OVER(PARTITION BY)函数用法
- OVER(PARTITION BY)函数用法
- row_number() over (partition by....order by...)用法
- row_number() over (partition by....order by...)用法
- ListView加载速度/性能优化方案分
- Android过度绘制优化心得
- 玩转Bootstrap(基础) (2.表单操作)
- ecshop增加一个后台菜单
- WWCD 2015: Swift 里的Value Type 和面向协议
- over(Partition by...) 详细用法
- Maven构建真正的J2EE项目
- 关于在本地对多个ssh key的管理
- 微信公众平台开发书籍推荐
- 名字的漂亮度
- 分享一些免费的接口
- self和下划线的用法区别
- 同步与异步的区别
- IntrospectorCleanupListener 监听器