- 浏览: 271077 次
- 性别:
- 来自: 福建
文章分类
- 全部博客 (98)
- jQuery (13)
- Ext (1)
- javascript (30)
- SSH项目 (5)
- webservice (1)
- struts2 (1)
- 其它 (7)
- hibernate (7)
- Oracle (19)
- 常用资料 (7)
- 全屏浏览器 (2)
- Linux (1)
- weblogic (3)
- java web打包安装程序 (1)
- 解决讨厌的Oracle死锁 (1)
- jxl 实现根据sql语句导出excel文件 (1)
- Java中使用的路径 (1)
- 存储过程锁问题 存储过程编译锁问题---解锁办法 (0)
- 存储过程锁问题、锁表问题 存储过程编译锁问题---解锁办法 (1)
- Oracle、导出序列 (1)
- java 抓取网页图片 (1)
最新评论
-
guoshuai_27:
...
24种页面切换效果详解 -
nucleus:
第一个例子少了sql demo1:统计某商店的营业额能补上去么 ...
超级牛皮的oracle的分析函数over(Partition by...) 及开窗函数 -
agan112:
xili
oracle数组例子 -
ye361571404:
谢谢了。。。。
weblogic92启动时的问题:Unmarshaller failed . -
bianxiaoxm:
感觉不错,值得学习!
js禁止页面刷新后退
超级牛皮的oracle的分析函数over(Partition by...) 及开窗函数 over(Partition by...) 一个超级牛皮的ORACLE特有函数。 最近工作中才接触到这个功能强大而灵活的函数。 oracle的分析函数over 及开窗函数 一:分析函数over Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是 对于每个组返回多行,而聚合函数对于每个组只返回一行。 下面通过几个例子来说明其应用。 1:统计某商店的营业额。 date sale 1 20 2 15 3 14 4 18 5 30 规则:按天统计:每天都统计前面几天的总额 得到的结果: DATE SALE SUM ----- -------- ------ 1 20 20 --1天 2 15 35 --1天+2天 3 14 49 --1天+2天+3天 4 18 67 . 5 30 97 . 2:统计各班成绩第一名的同学信息 NAME CLASS S ----- ----- ---------------------- fda 1 80 ffd 1 78 dss 1 95 cfe 2 74 gds 2 92 gf 3 99 ddd 3 99 adf 3 45 asdf 3 55 3dd 3 78 通过: -- select * from ( select name,class,s,rank()over(partition by class order by s desc) mm from t2 ) where mm=1 -- 得到结果: NAME CLASS S MM ----- ----- ---------------------- ---------------------- dss 1 95 1 gds 2 92 1 gf 3 99 1 ddd 3 99 1 注意: 1.在求第一名成绩的时候,不能用row_number(),因为如果同班有两个并列第一,row_number()只返回一个结果 2.rank()和dense_rank()的区别是: --rank()是跳跃排序,有两个第二名时接下来就是第四名 --dense_rank()l是连续排序,有两个第二名时仍然跟着第三名 3.分类统计 (并显示信息) A B C -- -- ---------------------- m a 2 n a 3 m a 2 n b 2 n b 1 x b 3 x b 2 x b 4 h b 3 select a,c,sum(c)over(partition by a) from t2 得到结果: A B C SUM(C)OVER(PARTITIONBYA) -- -- ------- ------------------------ h b 3 3 m a 2 4 m a 2 4 n a 3 6 n b 2 6 n b 1 6 x b 3 9 x b 2 9 x b 4 9 如果用sum,group by 则只能得到 A SUM(C) -- ---------------------- h 3 m 4 n 6 x 9 无法得到B列值 ===== select * from test 数据: A B C 1 1 1 1 2 2 1 3 3 2 2 5 3 4 6 ---将B栏位值相同的对应的C 栏位值加总 select a,b,c, SUM(C) OVER (PARTITION BY B) C_Sum from test A B C C_SUM 1 1 1 1 1 2 2 7 2 2 5 7 1 3 3 3 3 4 6 6 ---如果不需要已某个栏位的值分割,那就要用 null eg: 就是将C的栏位值summary 放在每行后面 select a,b,c, SUM(C) OVER (PARTITION BY null) C_Sum from test A B C C_SUM 1 1 1 17 1 2 2 17 1 3 3 17 2 2 5 17 3 4 6 17 求个人工资占部门工资的百分比 SQL> select * from salary; NAME DEPT SAL ---------- ---- ----- a 10 2000 b 10 3000 c 10 5000 d 20 4000 SQL> select name,dept,sal,sal*100/sum(sal) over(partition by dept) percent from salary; NAME DEPT SAL PERCENT ---------- ---- ----- ---------- a 10 2000 20 b 10 3000 30 c 10 5000 50 d 20 4000 100 二:开窗函数 开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化,举例如下: 1: over(order by salary) 按照salary排序进行累计,order by是个默认的开窗函数 over(partition by deptno)按照部门分区 2: over(order by salary range between 5 preceding and 5 following) 每行对应的数据窗口是之前行幅度值不超过5,之后行幅度值不超过5 例如:对于以下列 aa 1 2 2 2 3 4 5 6 7 9 sum(aa)over(order by aa range between 2 preceding and 2 following) 得出的结果是 AA SUM ---------------------- ------------------------------------------------------- 1 10 2 14 2 14 2 14 3 18 4 18 5 22 6 18 7 22 9 9 就是说,对于aa=5的一行 ,sum为 5-1<=aa<=5+2 的和 对于aa=2来说 ,sum=1+2+2+2+3+4=14 ; 又如 对于aa=9 ,9-1<=aa<=9+2 只有9一个数,所以sum=9 ; 3:其它: over(order by salary rows between 2 preceding and 4 following) 每行对应的数据窗口是之前2行,之后4行 4:下面三条语句等效: over(order by salary rows between unbounded preceding and unbounded following) 每行对应的数据窗口是从第一行到最后一行,等效: over(order by salary range between unbounded preceding and unbounded following) 等效 over(partition by null) 常用的分析函数如下所列: 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 ...) 示例 SQL> select type,qty from test; TYPE QTY ---------- ---------- 1 6 2 9 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 ---------- ---------- ------------ 3 1/2 1 6 2/2 2 5 1/3 7 2/3 2 9 3/3 SQL> select * from test; ---------- ------------------------------------------------- 1 11111 2 22222 3 33333 4 44444 SQL> select t.id,mc,to_char(b.rn)||'/'||t.id)e 2 from test t, (select rownum rn from (select max(to_number(id)) mid from test) connect by rownum <=mid ))L 4 where b.rn<=to_number(t.id) order by id 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 44444 1/4 44444 2/4 4 44444 3/4CNOUG4 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. select deptno, sal,sum(sal) over(partition by deptno) from emp;--每行记录后都有总计值 select deptno, sum(sal) from emp group by deptno; 7. 求每个部门的平均工资以及每个人与所在部门的工资差额 select deptno,ename,sal , round(avg(sal) over(partition by deptno)) as dept_avg_sal, round(sal-avg(sal) over(partition by deptno)) as dept_sal_diff from emp; 本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/hoho_lolo/archive/2010/03/16/5386185.aspx
发表评论
-
oracle 表delete 通过闪回功能恢复数据
2012-12-24 15:29 1229第一步:确定删除时间点,找出SCN,后面需要根据SCN让 ... -
获取ORACLE 表字段,表名,以及主键之类等等的信息。
2012-09-29 09:27 4347获取表名: Oracle的user_talbes用于记录了 ... -
存储过程锁问题、锁表问题 存储过程编译锁问题---解锁办法,Oracle
2012-01-10 17:09 6174存储过程锁问题 存储过程编译锁问题---解锁办法 /* ... -
解决讨厌的Oracle死锁
2011-11-14 17:27 1269--第一步:查看是否有死锁存在,查出有数据则代表有死锁 ... -
Oracle左右全连接总结
2011-05-10 09:10 1050--建立测试数据 create table a(id ... -
平时积累的一些SQL语句(转) 2
2011-02-22 09:11 1476FORM中获取光标所在的TAB页面 1. 在Form级触发器 ... -
平时积累的一些SQL语句(转) 1
2011-02-22 09:08 1343/* Formatted on 2011-1-12 10:48 ... -
plsql异常处理
2010-12-08 10:30 1367一、异常 (1) 程序错误 - 编译时 ... -
plsql事务处理(二)
2010-12-08 10:25 1382自治事务 看看在oracle中一个事务调用另外一个事务的情况 ... -
plsql事务处理(一)
2010-12-08 10:23 58414,事务处理 Oracle是基于事务的,oracle以用户事 ... -
oracle自动备份、Oracle远程备份
2009-11-12 15:42 16931.在tnsnames.ora添加要远程备份的主机 如: ... -
Oracle临时表学习
2009-09-18 10:20 985CREATE GLOBAL TEMPORARY TABLE ... -
Oracle临时表的使用2
2009-09-17 16:06 1382Temp Table 的特点: (1) 多用户操作的独立性: ... -
Oracle临时表的使用1
2009-09-17 16:00 19171。会话特有的临时表 ... -
exists 和 in 小结
2009-09-17 15:12 11871.用EXISTS替代IN 在许多 ... -
oracle数组例子
2009-09-16 14:52 18004--固定数组 declare type type_ar ... -
Oracle数组学习--01
2009-09-11 16:37 1920COUNT 返回集合中元素的个数 ... -
pl/sql一些常用小例子
2009-09-10 10:22 1246游标: 隐式游标:sql%found,sql%notfoun ...
相关推荐
Oracle 语法之 OVER (PARTITION BY ..) 及开窗函数(转载),这个命令很实用,对于分析人员经常用到。
主要介绍了Oracle查询中OVER (PARTITION BY ..)用法,内容和代码大家参考一下。
第六篇 著名函数之分析函数 1、AVG([DISTINCT|ALL] expr) OVER(analytic_clause) 计算平均值。 例如: --聚合函数 SELECT col, AVG(value) FROM tmp1 GROUP BY col ORDER BY col; --分析函数 SELECT col, AVG(value)...
1、 结构:聚合函数()over( partition by 字段1,字段2,字段3 order by 字段 desc/asc range between 数值/date preceding/flowing and 数值/date preceding/flowing) 聚合函数可以是:sum,count,avg,max,min,...
ROW_NUMBER () OVER([partition_clause] order_by_clause) dense_rank在做排序时如果遇到列有重复值,则重复值所在行的序列值相同,而其后的序列值依旧递增,rank则是重复值所在行的序列值相同,但其后的序列值从...
Oracle 8i Partition.rar
Oracle Partition分区详细总结.pdf
ORACLE数据库中PARTITION的用法[文].pdf
SqlServer的over partition by开窗语句
3、PERCENT_RANK() OVER([partition_clause] order_by_clause) 与CUME_DIST类似,本函数返回分组序列中各行在分组序列的相对位置。其返回值也是介于0到1之间,不过其起始值始终为0而终结值始终为1。 例如:SELECT ...
第五篇 分析函数简述 分析函数的语法结构比较复杂,但多数函数都具有相同的语法结构,所以先在之前进行统一介绍,后续单个函数介绍时就不过多说明函数语法结构了。 基本上所有的分析函数均是这种格式: 函数...
sum(sal) over (partition by deptno order by ename) 按部门“连续”求总和 sum(sal) over (partition by deptno) 按部门求总和 sum(sal) over (order by deptno,ename) 不按部门“连续”求总和 sum(sal) over () ...
本文主要介绍Oracle中rank,over partition函数的用法,希望对大家有所帮助。
好东西硬盘修复工具 硬盘备份工具 硬盘分区工具
A40-T3的sys_partition.fex分区表说明
分区软件分区软件分区软件分区软件分区软件分区软件分区软件分区软件分区软件分区软件分区软件分区软件分区软件分区软件分区软件分区软件分区软件分区软件分区软件分区软件分区软件分区软件分区软件分区软件分区软件...
BOOT.INI is corrected automatically (if needed) to keep the system bootable, as well as damaged Volume Boot Sectors are corrected to mantain integrity of the partition. Software allows also to fix ...
分区表PARTITIONtable.rar
1.2、分析函数中的分组/排序/窗口 分析函数包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows)