Oracle语法之OVER(PARTITION BY)及开窗函数
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)
分享到：
相关推荐
### Oracle 语法之 OVER (PARTITION BY ..) 及开窗函数详解 #### 一、OVER (PARTITION BY ..) 概述 在Oracle数据库中，`OVER (PARTITION BY ...)` 是一种非常强大的功能，它允许用户在数据集上进行窗口操作。这在...
`OVER`函数是Oracle开窗函数的核心组成部分，它定义了一个计算上下文或窗口，在这个窗口内可以执行各种类型的聚合计算。`OVER`函数的基本语法如下： ``` function_name(...) OVER (window_specification) ``` 其中...
### Oracle分析函数及开窗函数详解 #### 一、Oracle分析函数概述 Oracle自8.1.6版本开始引入了分析函数，这类函数主要用于计算基于组的聚合值，并且与传统的聚合函数不同的是，分析函数可以针对每个组返回多行结果...
### Oracle开发中的OVER函数详解 #### 一、Oracle分析函数简介 在Oracle数据库开发中，分析函数是一类非常强大的工具，主要用于实现复杂的查询需求，尤其是在处理大量数据时，能够提供高级的数据汇总、排序和筛选...
#### 二、OVER函数的基本语法及概念 OVER函数通常与窗口函数（如RANK(), DENSE_RANK(), ROW_NUMBER(), SUM(), AVG()等）一起使用。其基本语法形式如下： ```sql <window_function> OVER ( [window_spec] ) ``` ...
row_number()over 函数的基本语法为： ``` row_number() over ([partition by col1] order by col2) as 别名 ``` 其中，`partition by col1` 用于指定分区的列，`order by col2` 用于指定排序的列。 在使用 row_...
### ORACLE OLAP函数语法详解 #### 一、概述 Oracle OLAP（Online Analytical Processing）函数主要用于执行复杂的分析计算，如聚合、排名、趋势分析等。这些函数可以帮助用户更高效地处理大型数据集，特别是在数据...
Oracle的分析函数row_number() over()是数据库查询中一种强大的工具，它用于对数据集进行分组和排序，尤其在处理重复数据或者实现排名时非常有用。在Oracle SQL中，row_number()函数会为每一行生成一个唯一的数字，...
在Oracle中，分析函数的语法结构为： ``` analytic_function([arguments]) OVER ([partition_clause] [order_by_clause] [windowing_clause]) ``` 1. `analytic_function`：这是实际的分析函数，例如AVG, COUNT, ...
Oracle数据库系统是世界上最...在实际工作中，结合PARTITION BY和OVER子句的使用，分析函数可以解决许多复杂的业务问题，提升数据处理能力。因此，对分析函数的深入理解是成为高级Oracle数据库开发人员的关键技能之一。
开窗函数在MSSQL Server、Oracle、DB2等主流数据库系统中得到广泛支持，但遗憾的是，MySQL当前版本还不支持。 首先，我们需要理解什么是窗口。在SQL中，"窗口"是指在执行开窗函数时定义的一组行，这组行是相对于...
### Oracle中OVER函数详解 #### 一、概述 在Oracle数据库中，OVER函数是一个非常强大的工具，主要用于实现复杂的窗口函数操作。它可以帮助我们对数据进行分组、排序、累计等多种处理，大大增强了SQL语言的数据分析...
SQL开窗函数是数据库查询中的一个强大工具，它允许在单个SQL语句中进行复杂的分析操作，如计算累计和、排名、移动平均等。在SQL中，窗口（或称为分区）是根据某些条件对数据集进行分组的一段连续记录。通过使用窗口...
`OVER (PARTITION BY deptno)`计算每个部门的平均工资，而`OVER ()`计算所有员工的平均工资。 Oracle的分析函数还包括`ROW_NUMBER`, `RANK`, `DENSE_RANK`用于行号分配，`LEAD`, `LAG`用于获取前后行数据，`FIRST_...
FUNCTION_NAME(<参数>,…) OVER (<PARTITION BY 表达式,…> <ORDER BY 表达式 <ASC DESC> <NULLS FIRST NULLS LAST>> 子句>) 其中，PARTITION BY 子句用于将数据分区，ORDER BY 子句用于排序数据，而 WINDOWING ...
其基本语法为row_number() over ([partition by column_name] order by column_name)。在这个语法中，partition by是可选的，用于将数据集分为几个部分，然后对每个部分进行排序和编号；order by是必须的，用于指定...
#### 三、分析函数基本语法及特点 分析函数的基本语法如下： ```sql function_name(arg1, arg2, ...) OVER (<partition-clause><order-by-clause>) ``` - **`function_name`**：指定使用的分析函数名称。 - **`...
3. **报表（Reporting）函数**：类似于开窗函数，但作用于整个分区或结果集，不涉及排序。 - 示例：`SUM(sales) OVER (PARTITION BY region_id)`，计算每个销售区域的总销售额。 4. **LAG`、`LEAD`函数**：允许在...