一:分析函数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)
相关推荐
### Oracle分析函数OVER及开窗函数详解 #### 一、概述 在Oracle数据库中,分析函数(Analytic Functions)是一种非常强大的工具,用于处理复杂的查询需求。这些函数可以在一组相关的行上执行计算,并且每行返回一个...
### Oracle的分析函数OVER及开窗函数 #### 一、分析函数OVER ##### 1. 概念介绍 从Oracle 8.1.6版本开始,Oracle引入了分析函数,这些函数可以对分组的数据执行复杂的操作,如计算累积总和、排名等。与聚合函数...
### Oracle分析函数及开窗函数详解 #### 一、Oracle分析函数概述 Oracle自8.1.6版本开始引入了分析函数,这类函数主要用于计算基于组的聚合值,并且与传统的聚合函数不同的是,分析函数可以针对每个组返回多行结果...
`OVER`函数是Oracle开窗函数的核心组成部分,它定义了一个计算上下文或窗口,在这个窗口内可以执行各种类型的聚合计算。`OVER`函数的基本语法如下: ``` function_name(...) OVER (window_specification) ``` 其中...
开窗函数定义了分析函数作用的数据范围,通常包括以下几种类型: 1. **ORDER BY**:根据指定列的值对数据进行排序。 2. **PARTITION BY**:将数据划分成不同的分区,每个分区独立进行计算。 3. **RANGE BETWEEN** ...
开窗函数是定义分析函数作用范围的关键部分,它通过指定特定的条件来界定分析函数的操作区间。下面是一些常见的开窗函数及其含义: 1. **`OVER (ORDER BY salary)`**:此函数按薪资排序进行累计计算,其中`ORDER BY...
### Oracle 语法之 OVER (PARTITION BY ..) 及开窗函数详解 #### 一、OVER (PARTITION BY ..) 概述 在Oracle数据库中,`OVER (PARTITION BY ...)` 是一种非常强大的功能,它允许用户在数据集上进行窗口操作。这在...
通过上述介绍,我们不仅了解了分析函数的基本用法,还探讨了如何利用开窗函数来定义不同类型的计算窗口,从而实现更高级的数据分析需求。掌握了这些技巧后,您可以更加高效地进行数据探索和洞察发现。
3. 制表(Reporting)函数:作用于一个分区或一组上的所有列,主要用于报告目的,与开窗函数类似,但`OVER`子句中缺少`ORDER BY`子句。 4. LAG, LEAD函数:允许在结果集中向前或向后检索数据值,常用于在没有自连接...
- `OVER`:关键字,用于区分分析函数和聚合函数。 - `<Partition-Clause>`:可选分区子句,指定数据集被分割成多少个分区。 - `<Order-by-Clause>`:可选排序子句,指定每个分区内部数据的排序方式。 - `...
Oracle分析函数是数据库查询中的强大工具,它允许用户在数据集上执行复杂的分析操作,而不仅仅是简单的聚合。分析函数的特点在于它们能够对数据进行排序、筛选,并且可以在不同的分区、窗口和排序规则下运行,这使得...
开窗函数用于指定分析函数执行计算的数据范围或窗口。这些窗口可以基于行数、数据范围或者是混合定义。常见的开窗函数包括`ORDER BY`、`PARTITION BY`等。 - **ORDER BY**:按照指定的列对数据进行排序。 - **...
3. **报表(Reporting)函数**:这些函数与开窗函数类似,但它们针对整个分区或一组行进行操作,而不是仅限于一个窗口。常见的报表函数包括`SUM`, `AVG`, `COUNT`, `MIN`, `MAX`。 - 示例:`SUM(salary) OVER ...
### Oracle分析函数详解 #### 一、Oracle分析函数简介 Oracle分析函数是在Oracle数据库中用于处理复杂的查询需求的一组强大的工具。与传统的SQL聚合函数(如`SUM`, `AVG`, `COUNT`等)不同,分析函数能够在同一...
开窗函数是分析函数中用于定义数据窗口范围的关键组成部分。它允许用户指定一个动态或静态的窗口范围,从而可以对窗口内的数据进行计算。开窗函数主要包括 `OVER` 子句以及其中的 `PARTITION BY` 和 `ORDER BY` 子句...
自 Oracle 8.1.6 版本起,Oracle 数据库引入了分析函数(Analytic Functions),这是一种非常强大的工具,主要用于处理复杂的查询需求,尤其是在计算基于组的聚合值方面表现出色。与传统的聚合函数(如 SUM、AVG 等...
通过本文的学习,我们了解了Oracle中的分析函数及其应用场景,特别是开窗函数的概念以及如何使用`AVG`和`CORR`这两个实用的分析函数。希望这些知识能够帮助你在实际开发工作中更加高效地处理数据,提升项目的完成...
开窗语句用于定义窗口的具体范围,常见的有ROWS和RANGE类型。 ##### 1. Rows窗口 ROWS窗口适用于任何类型的数据,并且可以按多列进行排序。 - **Rows N Preceding**:将当前行及其前面N行定义为一个窗口。 ```sql...