- 浏览: 622873 次
- 性别:
- 来自: 上海
文章分类
最新评论
-
zhunengfei:
THS!
window.open()打开一个子页面,如何在子页面关闭时刷新父页面? -
kevin_liao:
非常感谢,,各种尝试,用了之后就OK了。。世界突然就安静了
android javax.net.ssl.SSLPeerUnverifiedException: No peer certificate -
上官轩:
XML document structures must start and end within the same entity. -
aaron_brothers:
...
C3P0连接池jar,proxool连接池jar 下载 -
单证员:
...
XML document structures must start and end within the same entity.
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
求个人工资占部门工资的百分比
二:开窗函数
开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化,举例如下:
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)
一:分析函数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
求个人工资占部门工资的百分比
二:开窗函数
开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化,举例如下:
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)
发表评论
-
mybatis sql模糊搜索
2018-01-03 10:57 491<if test="creativeName! ... -
ORACLE树(查询所有子节点)
2017-07-06 10:37 1432根据父节点查询所有子节点: SELECT t.object_ ... -
oracle 排序指定某个数据排在第一位
2017-06-23 17:27 2284select * from f_area ar where a ... -
Oracle中IN参数个数超过1000的解决方案
2016-08-31 10:33 1122把这 2000 个值写到一个临时表中,直接 in 一个子查询 ... -
Oracle 树操作(select…start with…connect by…prior)
2016-07-29 10:06 788oracle树查询的最重要的就是select…start wi ... -
SQL按时间段统计(5分钟统计一次访问量为例,oracle统计)
2016-07-22 16:19 1903需求:统计当天的访问量,每五分钟采集一次 表结构中有创建日期 ... -
Oracle查询性能优化
2016-07-20 14:55 603原则一:注意WHERE子句 ... -
oracle中有三种比较常用的类型:varchar2(byte)、varchar2(char)、nvarchar2()
2016-07-20 14:14 1094首先,我们要时刻记清:无论是varchar2还是nvar ... -
ORACLE in与exists语句的区别
2016-07-20 11:49 447select * from A where id in(sel ... -
复合索引生效
2015-04-14 21:24 744SQL Server的复合索引学习 ... -
Mybatis批量增加,删除,更新oracle
2015-03-31 11:23 994最近需要用到Mybatis批量新增oracle数据库,刚开始在 ... -
oracle 11 ora-28000:the account is locked
2015-01-12 17:22 700进入cmd后分别执行以下 sqlplus /nolog c ... -
oracle死锁解决方法
2014-07-30 16:53 738select p.spid,c.object_name,b. ... -
oracle批量更新一个表中的一个字段
2014-07-10 10:59 1249最近遇到一个sql问题。老大们让我把一个表中的一列数据做更新。 ... -
oracle创建数据库表空间,用户及表空间及分配权限
2013-11-20 10:03 711-- Create the tablespace //创建临 ... -
如何用PLSQL developer导入导出表,ORACLE数据库
2013-06-20 15:14 1063一、导出表 步骤一:左击菜单栏中的工具,并左击导出表的选项; ... -
ORA-28001: the password has expired
2013-06-07 11:18 930由于Oracle11G的新特性所致,经常会遇到使用sqlplu ... -
oracle更新
2013-06-03 10:11 855UPDATE temp A SET a.cola = (SE ... -
连接Oracle 10g用localhost不行 解决方案
2013-05-23 10:50 1144安装完oracle 10g 连接数据库用localhost访问 ... -
ora-01536:超出表空间“ssp"的空间限额
2013-05-10 14:48 881GRANT UNLIMITED TABLESPACE TO s ...
相关推荐
### Oracle分析函数OVER及开窗函数详解 #### 一、概述 在Oracle数据库中,分析函数(Analytic Functions)是一种非常强大的工具,用于处理复杂的查询需求。这些函数可以在一组相关的行上执行计算,并且每行返回一个...
### Oracle 语法之 OVER (PARTITION BY ..) 及开窗函数详解 #### 一、OVER (PARTITION BY ..) 概述 在Oracle数据库中,`OVER (PARTITION BY ...)` 是一种非常强大的功能,它允许用户在数据集上进行窗口操作。这在...
### Oracle分析函数及开窗函数详解 #### 一、Oracle分析函数概述 Oracle自8.1.6版本开始引入了分析函数,这类函数主要用于计算基于组的聚合值,并且与传统的聚合函数不同的是,分析函数可以针对每个组返回多行结果...
### Oracle的分析函数OVER及开窗函数 #### 一、分析函数OVER ##### 1. 概念介绍 从Oracle 8.1.6版本开始,Oracle引入了分析函数,这些函数可以对分组的数据执行复杂的操作,如计算累积总和、排名等。与聚合函数...
3. **制表(Reporting)函数**:与开窗函数同名,但作用于整个分区或结果集中的所有行,不依赖于特定的顺序。常见的制表函数有`SUM()`, `COUNT()`, `AVG()`, `MIN()`, `MAX()`等。 ```sql -- 示例:计算所有员工的...
在Oracle数据库中,开窗函数是一种非常强大的功能,它能够帮助我们对数据进行更复杂的分析和处理。开窗函数允许我们在查询结果集的一个窗口内执行聚合操作,而无需对数据进行物理排序或分组。本文将重点介绍`OVER`...
### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具,它允许用户对分组数据执行复杂的计算,并且结果可以根据特定条件进行动态调整。这种灵活性使得Oracle分析函数在处理复杂的数据集时非常...
1. **`OVER (ORDER BY salary)`**:此函数按薪资排序进行累计计算,其中`ORDER BY`可以被视为一种默认的开窗函数。 2. **`OVER (PARTITION BY deptno)`**:根据部门进行分组(分区),即在每个部门内部执行相应的...
- **制表(reporting)函数**:与开窗函数同名,但作用于一个分区或一组上的所有列,不依赖于排序。 - **LAG、LEAD函数**:这类函数允许在结果集中向前或向后检索值,避免数据的自连接。 - **VAR_POP、VAR_SAMP、...
### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具,它允许用户对分组数据执行复杂的计算,并且能够返回多个结果行。这与传统的聚合函数(如`SUM`、`COUNT`等)形成鲜明对比,后者通常只针对...
3. 制表(Reporting)函数:作用于一个分区或一组上的所有列,主要用于报告目的,与开窗函数类似,但`OVER`子句中缺少`ORDER BY`子句。 4. LAG, LEAD函数:允许在结果集中向前或向后检索数据值,常用于在没有自连接...
开窗函数是分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化。开窗函数可以指定数据窗口的大小和范围,例如按照 salary 排序进行累计、按照部门分区等。 下面是 Oracle 中一些常用的分析函数...
#### 四、Oracle分析函数分类及示例 根据功能的不同,Oracle分析函数可以分为以下几类: 1. **等级(Ranking)函数**:用于确定数据在特定集合中的位置,如`RANK()`、`DENSE_RANK()`、`ROW_NUMBER()`等。 - 示例...
Oracle分析函数是数据库查询中的强大工具,它允许用户在数据集上执行复杂的分析操作,而不仅仅是简单的聚合。分析函数的特点在于它们能够对数据进行排序、筛选,并且可以在不同的分区、窗口和排序规则下运行,这使得...
- **报表(Reporting)函数**: 与开窗函数类似名称,但作用于整个分区或一组数据,而不是一个特定的窗口。例如,`SUM`或`COUNT`函数不指定`ORDER BY`子句时即为报表函数。 - **LAG`, `LEAD`函数**: 这些函数允许向前...
以下是一些常见的Oracle分析函数及其用法: 1. **开窗函数(Over)**: 开窗函数允许在一组行上执行计算,这组行被称为“窗口”,窗口可以基于特定的排序或分区条件。例如: - `OVER (ORDER BY salary)` 按薪资排序...
2. **按区域查找上一年度订单总额占区域订单总额20%以上的客户**:使用`SUM`开窗函数结合`OVER`子句和条件筛选完成。 3. **查找上一年度销售最差的部门所在的区域**:通过`MIN`或`RANK`函数结合`OVER`子句找到最低...
### Oracle分析函数详解 #### 一、Oracle分析函数概述 Oracle分析函数是在Oracle数据库中用于处理复杂的查询需求的一组强大工具。与传统的SQL聚合函数(如`SUM`, `AVG`, `COUNT`等)不同,分析函数能够在同一查询...
开窗函数主要包括 `OVER` 子句以及其中的 `PARTITION BY` 和 `ORDER BY` 子句。 #### `OVER` 子句 `OVER` 子句用于定义一个分析函数的操作窗口,它可以包含 `PARTITION BY` 和/或 `ORDER BY` 子句。 ##### `...