xiao1227372602
- 浏览: 250342 次
- 性别:
- 来自: 北京
-
文章分类
- 全部博客 (192)
- 技术研究学习 (19)
- 工作总结 (4)
- spring3.x (11)
- mail (2)
- jar (2)
- FCKeditor (1)
- quartz (2)
- json (1)
- jdbc (5)
- struts2 (6)
- java基础 (18)
- jboss (3)
- IT名称解析 (1)
- 测试工具 (2)
- 工作趣谈 (1)
- 数据库 (8)
- js (8)
- jquery (1)
- mysql (20)
- Sql (3)
- Oracle (1)
- easyui (0)
- log4j (1)
- 源码研究 (1)
- Jasper Report (0)
- Jbpm4 (4)
- xml (1)
- ireport (0)
- javavm (1)
- sitemesh (5)
- compass (1)
- jvm (1)
- ext (1)
- lucene (0)
- cxf (1)
- Blazeds (0)
- Resteasy (1)
- jaxb (1)
- tomcat (1)
- Rmi (1)
- BoneCP (1)
- velocity (3)
- OSCache (1)
- EHCache (1)
- 高性能开发 (9)
- 设计模式 (0)
- 网络协议应用 (1)
- Ibatis (1)
- powerdesigner (1)
- 架构师之路 (2)
- memcached (4)
- MapReduce (1)
- 测试组 (1)
- 图像处理 (2)
- LoadRunner (2)
- 报表 (1)
- 负载均衡 (1)
- 分布式 (3)
- c# (1)
- java中一些特殊问题 (3)
- java 8 (1)
- Mogodb (1)
- 项目设计与实现 (2)
- Ubuntu (1)
- eclipse (1)
- gradle (1)
- 私有云 (1)
- redis (1)
- 移动前端 (1)
最新评论
[url]
http://database.51cto.com/art/201104/254414.htm
[/url]
http://database.51cto.com/art/201104/254414.htm
[/url]
Oracle中over函数的使用示例
sum(sal) over (partition by deptno order by ename) 部门连续求和,--各部门的薪水"连续"求和，
环境：windows 2000 server + Oracle8.1.7 + sql*plus
目的：以oracle自带的scott模式为测试环境，主要通过试验体会分析函数的用法。
类似 sum(...) over ... 的使用
1.原表信息：
SQL> break on deptno skip 1 -- 为效果更明显，把不同部门的数据隔段显示。
SQL> select deptno,ename,sal
2 from emp
3 order by deptno;
DEPTNO ENAME SAL
---------- ---------- ----------
10 CLARK 2450
KING 5000
MILLER 1300
20 SMITH 800
ADAMS 1100
FORD 3000
SCOTT 3000
JONES 2975
30 ALLEN 1600
BLAKE 2850
MARTIN 1250
JAMES 950
TURNER 1500
WARD 1250
已选择14行。
2.先来一个简单的，注意over(...)条件的不同，
使用 sum(sal) over (order by ename)... 查询员工的薪水“连续”求和,
注意over (order by ename)如果没有order by 子句，求和就不是“连续”的，
放在一起，体会一下不同之处：
SQL> break on '' -- 取消数据分段显示
SQL> select deptno,ename,sal,
2 sum(sal) over (order by ename) 连续求和,
3 sum(sal) over () 总和, -- 此处sum(sal) over () 等同于sum(sal)
4 100*round(sal/sum(sal) over (),4) "份额(%)"
5 from emp
6 /
DEPTNO ENAME SAL 连续求和 总和 份额(%)
---------- ---------- ---------- ---------- ---------- ----------
20 ADAMS 1100 1100 29025 3.79
30 ALLEN 1600 2700 29025 5.51
30 BLAKE 2850 5550 29025 9.82
10 CLARK 2450 8000 29025 8.44
20 FORD 3000 11000 29025 10.34
30 JAMES 950 11950 29025 3.27
20 JONES 2975 14925 29025 10.25
10 KING 5000 19925 29025 17.23
30 MARTIN 1250 21175 29025 4.31
10 MILLER 1300 22475 29025 4.48
20 SCOTT 3000 25475 29025 10.34
20 SMITH 800 26275 29025 2.76
30 TURNER 1500 27775 29025 5.17
30 WARD 1250 29025 29025 4.31
已选择14行。
3.使用子分区查出各部门薪水连续的总和。注意按部门分区。注意over(...)条件的不同，
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 () 不按部门，求所有员工总和，效果等同于sum(sal)。
SQL> break on deptno skip 1 -- 为效果更明显，把不同部门的数据隔段显示。
SQL> select deptno,ename,sal,
2 sum(sal) over (partition by deptno order by ename) 部门连续求和,--各部门的薪水"连续"求和
3 sum(sal) over (partition by deptno) 部门总和, -- 部门统计的总和，同一部门总和不变
4 100*round(sal/sum(sal) over (partition by deptno),4) "部门份额(%)",
5 sum(sal) over (order by deptno,ename) 连续求和, --所有部门的薪水"连续"求和
6 sum(sal) over () 总和, -- 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和
7 100*round(sal/sum(sal) over (),4) "总份额(%)"
8 from emp
9 /
-----------------------------------
2010年10月26日
---将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
OVER(PARTITION BY)函数介绍
开窗函数
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
1：over后的写法：
over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数
over（partition by deptno）按照部门分区
over（partition by deptno order by salary）
2：开窗的窗口范围：
over（order by salary range between 5 preceding and 5 following）：窗口范围为当前行数据幅度减5加5后的范围内的。
举例：
--sum(s)over(order by s range between 2 preceding and 2 following) 表示加2或2的范围内的求和
select name,class,s, sum(s)over(order by s range between 2 preceding and 2 following) mm from t2
adf 3 45 45 --45加2减2即43到47，但是s在这个范围内只有45
asdf 3 55 55
cfe 2 74 74
3dd 3 78 158 --78在76到80范围内有78，80，求和得158
fda 1 80 158
gds 2 92 92
ffd 1 95 190
dss 1 95 190
ddd 3 99 198
gf 3 99 198
over（order by salary rows between 5 preceding and 5 following）：窗口范围为当前行前后各移动5行。
举例：
--sum(s)over(order by s rows between 2 preceding and 2 following)表示在上下两行之间的范围内
select name,class,s, sum(s)over(order by s rows between 2 preceding and 2 following) mm from t2
adf 3 45 174 （45+55+74=174）
asdf 3 55 252 （45+55+74+78=252）
cfe 2 74 332 （74+55+45+78+80=332）
3dd 3 78 379 （78+74+55+80+92=379）
fda 1 80 419
gds 2 92 440
ffd 1 95 461
dss 1 95 480
ddd 3 99 388
gf 3 99 293
over（order by salary range between unbounded preceding and unbounded following）或者
over（order by salary rows between unbounded preceding and unbounded following）：窗口不做限制
3、与over函数结合的几个函数介绍
row_number()over()、rank()over()和dense_rank()over()函数的使用
下面以班级成绩表t2来说明其应用
t2表信息如下：
cfe 2 74
dss 1 95
ffd 1 95
fda 1 80
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；
得到的结果是:
dss 1 95 1
ffd 1 95 1
gds 2 92 1
gf 3 99 1
ddd 3 99 1
注意：
1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果;
select * from
(
select name,class,s,row_number()over(partition by class order by s desc) mm from t2
)
where mm=1；
1 95 1 --95有两名但是只显示一个
2 92 1
3 99 1 --99有两名但也只显示一个
2.rank()和dense_rank()可以将所有的都查找出来：
如上可以看到采用rank可以将并列第一名的都查找出来；
rank()和dense_rank()区别：
--rank()是跳跃排序，有两个第二名时接下来就是第四名；
select name,class,s,rank()over(partition by class order by s desc) mm from t2
dss 1 95 1
ffd 1 95 1
fda 1 80 3 --直接就跳到了第三
gds 2 92 1
cfe 2 74 2
gf 3 99 1
ddd 3 99 1
3dd 3 78 3
asdf 3 55 4
adf 3 45 5
--dense_rank()l是连续排序，有两个第二名时仍然跟着第三名
select name,class,s,dense_rank()over(partition by class order by s desc) mm from t2
dss 1 95 1
ffd 1 95 1
fda 1 80 2 --连续排序（仍为2）
gds 2 92 1
cfe 2 74 2
gf 3 99 1
ddd 3 99 1
3dd 3 78 2
asdf 3 55 3
adf 3 45 4
--sum()over（）的使用
select name,class,s, sum(s)over(partition by class order by s desc) mm from t2 --根据班级进行分数求和
dss 1 95 190 --由于两个95都是第一名，所以累加时是两个第一名的相加
ffd 1 95 190
fda 1 80 270 --第一名加上第二名的
gds 2 92 92
cfe 2 74 166
gf 3 99 198
ddd 3 99 198
3dd 3 78 276
asdf 3 55 331
adf 3 45 376
first_value() over()和last_value() over()的使用
举例说明，例子表数据如下表所示：
利用该函数查询以时间为排序，查找出all_conduit_len最小值和最大值：
select district,
oper_date,
all_conduit_len,
first_value(all_conduit_len) over(partition by oper_date order by all_conduit_len) low,
last_value(all_conduit_len) over(partition by oper_date order by all_conduit_len rows between unbounded preceding and unbounded following) high
from n9_rpt_checkdata;
结果如下所示：
再利用该函数，判断如果district为空，根据rownum排序取值填充：
select district,
oper_date,
all_conduit_len,
last_value(district ignore nulls) over(order by rownum)
from n9_rpt_checkdata;
结果如下：
--lag() over()函数用法（取出前n行数据）
lag(expresstion,<offset>,<default>)
with a as
(select 1 id,'a' name from dual
union
select 2 id,'b' name from dual
union
select 3 id,'c' name from dual
union
select 4 id,'d' name from dual
union
select 5 id,'e' name from dual
)
select id,name,lag(id,1,'')over(order by name) from a;
--lead() over()函数用法（取出后N行数据）
lead(expresstion,<offset>,<default>)
with a as
(select 1 id,'a' name from dual
union
select 2 id,'b' name from dual
union
select 3 id,'c' name from dual
union
select 4 id,'d' name from dual
union
select 5 id,'e' name from dual
)
select id,name,lead(id,1,'')over(order by name) from a;
--ratio_to_report(a)函数用法 Ratio_to_report() 括号中就是分子，over() 括号中就是分母
with a as (select 1 a from dual
union all
select 1 a from dual
union all
select 1 a from dual
union all
select 2 a from dual
union all
select 3 a from dual
union all
select 4 a from dual
union all
select 4 a from dual
union all
select 5 a from dual
)
select a, ratio_to_report(a)over(partition by a) b from a
order by a;
with a as (select 1 a from dual
union all
select 1 a from dual
union all
select 1 a from dual
union all
select 2 a from dual
union all
select 3 a from dual
union all
select 4 a from dual
union all
select 4 a from dual
union all
select 5 a from dual
)
select a, ratio_to_report(a)over() b from a --分母缺省就是整个占比
order by a;
with a as (select 1 a from dual
union all
select 1 a from dual
union all
select 1 a from dual
union all
select 2 a from dual
union all
select 3 a from dual
union all
select 4 a from dual
union all
select 4 a from dual
union all
select 5 a from dual
)
select a, ratio_to_report(a)over() b from a
group by a order by a;--分组后的占比
SQL> select n1,v1,nid,sum(nid) over(order by nid) as sum
2 from t1;
N1 V1 NID SUM
---------- ---------- ---------- ----------
1 aa 61 61
2 aa 62 123
3 aa 63 186
4 aa 64 250
取nid列的累积和，即下面以emp表为例的按部门“连续”求总和
==================================================================
按v1分组取nid的和
SQL> select v1,sum(nid) over (partition by v1 order by v1) as sum_nid from t1;
V1 SUM_NID
---------- ----------
aa 187
aa 187
aa 187
bb 83
按v1分组取nid的和，并重复行只显示一行
SQL> select distinct * from (select v1,sum(nid) over (partition by v1) as sum_nid from t1);
V1 SUM_NID
---------- ----------
aa 187
bb 83
==================================================================
再以emp为例
使用子分区查出各部门薪水连续的总和。注意按部门分区 over(...)条件的不同
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 () 不按部门，求所有员工总和，效果等同于sum(sal)。
sql> break on deptno skip 1 -- 为效果更明显，把不同部门的数据隔段显示。
SQL> select deptno,ename,sal,
2 sum(sal) over (partition by deptno order by ename) 部门连续求和,
3 sum(sal) over (partition by deptno) 部门总和,
4 100*round(sal/sum(sal) over (partition by deptno),4) 部门份额,
5 sum(sal) over () 总和,
6 sum(sal) over (order by deptno,ename) 连续求和,
7 100*round(sal/sum(sal) over (),4) 总份额
8 from emp;
DEPTNO ENAME SAL 部门连续求和 部门总和 部门份额 总和 连续求和 总份额
---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ----------
10 CLARK 2450 2450 8750 28 29025 2450 8.44
KING 5000 7450 8750 57.14 29025 7450 17.23
MILLER 1300 8750 8750 14.86 29025 8750 4.48
20 ADAMS 1100 1100 10875 10.11 29025 9850 3.79
FORD 3000 4100 10875 27.59 29025 12850 10.34
JONES 2975 7075 10875 27.36 29025 15825 10.25
SCOTT 3000 10075 10875 27.59 29025 18825 10.34
SMITH 800 10875 10875 7.36 29025 19625 2.76
30 ALLEN 1600 1600 9400 17.02 29025 21225 5.51
BLAKE 2850 4450 9400 30.32 29025 24075 9.82
JAMES 950 5400 9400 10.11 29025 25025 3.27
MARTIN 1250 6650 9400 13.3 29025 26275 4.31
TURNER 1500 8150 9400 15.96 29025 27775 5.17
WARD 1250 9400 9400 13.3 29025 29025 4.31
已选择14行。
综合的例子，求和规则有按部门分区的，有不分区的例子
SQL> select deptno,ename,sum(sal) over(partition by deptno order by sal) as sum_sal,
2 sum(sal) over(order by deptno,sal) as sum_dept_sal
3 from emp;
DEPTNO ENAME SUM_SAL SUM_DEPT_SAL
---------- ---------- ---------- ------------
10 MILLER 1300 1300
CLARK 3750 3750
KING 8750 8750
20 SMITH 800 9550
ADAMS 1900 10650
JONES 4875 13625
SCOTT 10875 19625
FORD 10875 19625
30 JAMES 950 20575
WARD 3450 23075
MARTIN 3450 23075
TURNER 4950 24575
ALLEN 6550 26175
BLAKE 9400 29025
已选择14行。
来一个逆序的，即部门从大到小排列，部门里各员工的薪水从高到低排列，累计和的规则不变。
SQL> select deptno,ename,sal,
2 sum(sal) over (partition by deptno order by deptno desc,sal desc) as sum_sal_order,
3 sum(sal) over (order by deptno desc,sal desc) as sum
4 from emp;
DEPTNO ENAME SAL SUM_SAL_ORDER SUM
---------- ---------- ---------- ------------- ----------
30 BLAKE 2850 2850 2850
ALLEN 1600 4450 4450
TURNER 1500 5950 5950
WARD 1250 8450 8450
MARTIN 1250 8450 8450
JAMES 950 9400 9400
20 SCOTT 3000 6000 15400
FORD 3000 6000 15400
JONES 2975 8975 18375
ADAMS 1100 10075 19475
SMITH 800 10875 20275
10 KING 5000 5000 25275
CLARK 2450 7450 27725
MILLER 1300 8750 29025
已选择14行。
分享到：
发表评论
相关推荐
#### 四、OVER函数示例 由于题目提供的部分内容中没有具体示例，以下是一些典型的使用场景示例。 ##### 示例1：使用ROW_NUMBER()计算每组内的行编号 假设我们有一个员工表`employees`，包含字段`employee_id`, `...
### Oracle开发中的OVER函数详解 #### 一、Oracle分析函数简介 在Oracle数据库开发中，分析函数是一类非常强大的工具，主要用于实现复杂的查询需求，尤其是在处理大量数据时，能够提供高级的数据汇总、排序和筛选...
Oracle数据库中的RATIO_TO_REPORT()函数是一个非常有用的分析函数，尤其在进行数据比例分析和比较时。这个函数能够计算一个值相对于所有值总和的比例，返回的结果是一个百分比。配合OVER()子句，它可以用于全局或者...
- 当`OVER`子句中使用`PARTITION BY NULL`时，意味着所有的行都被视为同一个分区，即整个结果集被视为一个整体来进行计算。 #### 六、总结 通过本文的学习，我们可以看到Oracle开窗函数的强大之处。无论是进行复杂...
通过以上示例可以看出，Oracle中的`OVER`子句和开窗函数是非常强大的工具，可以用来解决许多复杂的数据分析问题。正确理解和运用这些函数，能够极大地提高数据处理效率和灵活性。在实际应用中，可以根据具体需求灵活...
通过以上介绍和示例，我们可以看到Oracle数据库中的OVER函数提供了极其灵活和强大的数据处理功能，能够帮助我们轻松地完成复杂的数据分析任务。熟练掌握OVER函数的应用技巧，对于提升SQL技能和数据分析效率具有重要...
Oracle 分析函数（用法+实例） Oracle 分析函数是 Oracle 8.1.6 版本中引入的高级应用，属于 Oracle 的一大亮点。分析函数可以分为四大类：排名函数、聚合函数、行比较函数和统计函数。下面将对分析函数的原理、...
根据提供的文件信息，我们可以深入探讨Oracle分析函数的相关知识点，特别是`SUM()`函数配合`OVER`子句的不同用法，以及`RANK()`, `DENSE_RANK()`, 和 `ROW_NUMBER()` 这三个窗口函数的应用场景。 ### Oracle分析...
分析函数通过使用OVER子句，可以对数据进行分区、排序以及定义窗口大小等功能。它们在数据仓库、报告和在线事务处理等多种场合中非常有用。 以下是对给定文件内容中提到的Oracle分析函数相关知识点的详细介绍： 1....
在Oracle 9i 分析函数参考手册中，你可能会找到详细的函数用法、示例和最佳实践，帮助你更有效地利用这些功能。手册通常会涵盖函数语法、参数、返回类型以及如何解决常见问题。通过深入学习和实践，你可以掌握如何在...
通过本文的学习，我们了解了Oracle中的分析函数及其基本用法，包括常见的分析函数分类、基本语法以及实际应用场景。这些函数的强大之处在于它们能够轻松地处理复杂的数据计算需求，为数据分析提供了极大的便利。掌握...
本文将详细介绍每种类型的函数，并提供具体的使用场景和示例。 #### 二、分析函数 分析函数主要用于执行复杂的窗口运算，如计算移动平均、累计总和等。这类函数通常会结合`OVER`子句一起使用，以便在不同的数据集...
#### 四、Oracle分析函数示例 1. **使用`RANK()`函数** ```sql SELECT employee_id, salary, department_id, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank FROM employees; ``` ...
要计算每个员工的工资在其所在部门工资总额中的占比，可以使用`SUM`函数配合`OVER`子句： ```sql SELECT NAME, DEPT, SAL, SAL * 100 / SUM(SAL) OVER (PARTITION BY DEPT) AS PERCENT FROM salary; ``` 结果如下...
Oracle的分析函数`ROW_NUMBER() OVER()`是一种强大的工具，用于在查询结果集中为每一行分配一个唯一的序列号。这个函数通常用于数据分页、排名或者为特定条件的记录分配顺序。下面我们将详细讨论`ROW_NUMBER() OVER...
这个示例中，我们使用 row_number()over 函数来生成一个部门内的薪水排名列，然后在结果中取出排名在前 2 位的员工。 row_number()over 函数是一个非常有用的分析函数，可以帮助我们快速地生成各种形式的序号和排名...
Oracle分析函数是数据库管理系统Oracle中的一个强大特性，它允许用户在SQL查询中执行复杂的分析操作。分析函数在处理报表和数据迁移任务时尤其有用，因为它们可以基于分组计算聚合值，并为每个分组返回多行，而不...
在提供的内容中，提到了几个重要的分析函数示例： 1. AVG：此函数计算指定窗口内的平均值。例如，在员工表中，如果我们想要计算每个经理的员工平均工资，但要包括当前员工以及相邻的两个员工（如果存在的话），我们...
Oracle分析函数是数据库管理系统Oracle中一组强大的工具，用于处理集合数据，特别是在复杂的报表和数据分析场景中。它们允许用户在单个SQL查询中执行聚合操作，同时保持行的原始顺序，这是传统的GROUP BY函数无法...
`OVER (PARTITION BY ...)` 和开窗函数在Oracle数据库中提供了极其强大的数据处理能力，特别是在需要进行复杂的数据分析或报表制作时。掌握这些技术对于提高数据库查询效率和数据处理能力至关重要。