开窗函数

over在聚合函数中的使用：

一般格式：

聚合函数名(列) over(选项)

over必须与聚合函数或排序函数一起使用，聚合函数为：

sum(),max(),min(),count(),avg()

排序函数为：

rank(),row_number(),dense_rank(),ntile()

over表示把函数当成开窗函数而不是聚合函数，SQL标准允许将所有聚合函数用做开窗函数，使用over关键字来区分这两种用法。

开窗函数不需要使用group by就可以对数据进行分组，就可以同时返回基础行的列和聚合列。

开窗函数sum(*) over()，对于查询结果的每一行都返回所有符合条件的行的条数，over关键字后的括号中还经常添加选项来改变进行聚合运算的窗口范围，如果over关键字后的括号中选项为空，则开窗函数会对结果集中的所有行进行聚合运算。

常用格式：

sum(*) over(partition by A order by B)

partition by：进行分组，得到对应组内的所有求和值

order by：按照B进行排序，得到对应组内的累计求和值(如果B为id,两个id相同，则这两个id返回的sum那一列是相同的聚合值，是累计到最后一个id对应值的和--下面的例子会详细说明)

order by 字段名 rows|range between 边界规则1 and 边界规则2

rows：表示按照行的范围进行范围的定位

range：表示按照取值的范围进行范围的定位

这两种不同的定位方式主要用来处理并列排序的情况（见下面的例子）

边界规则的可取值为：

current row--当前行

n preceding--前n行

unbounded preceding--一直到第一条记录

n following--后n行

unbounded following--一直到最后一条记录

'range/rows between 边界规则1 and 边界规则2'：用来定位聚合计算范围，被称为定位框架。

eg:

1、建表

2、插入数据

3、关于partition by

（1）所属城市的人员数-按城市进行分组聚合

（2）显示每一个人员的信息、所属城市的人员数以及同龄人的人数

在同一个SELECT语句中可以同时使用多个开窗函数，而且这些开窗函数并不会相互干扰。

4、关于order by的详解：

（1）查询从第一行到当前行的的工资总和

（2）将上面的row换成range

结果和(1)的区别体现在红框和黄框部分，按照FSalary进行排序，row-按照行的范围进行范围定位，所以每一行后面对应的‘到当前行工资求和’都不一样，都严格的是第一行到当前行的累计和；range-按照取值的范围进行范围定位，虽然定位框架的语法仍然是从第一行到当前行的累计和，但是由于取值的范围：等于2000元的工资有3人，所以计算的累计为从第一条到2000元工资的最后一个人，写在每个2000元工资的人的后面都是7000。

（3）将(2)中的定位框架省略

上述框架是开窗函数中最常用的定位框架，如果是这种框架的话，可以省略上述定位框架部分

得到的结果和(2)的结果一样。

（4）将上面的sum()换成count()，计算工资排名

按照salary进行排序，然后计算从第一行（unbounded preceding）到当前行（current row）的人员的个数，相当于计算人员的的工资水平排名。

Question:

怎么让工资为2000元的排名都为2？--见后面排序函数的rank()和dence_rank()

5、关于over(partition by A order by B)

over在排序函数中的使用：

一般格式：

排序函数(列) over(选项)

排序函数为：

rank(),dense_rank(),row_number(),ntile(),lead(),lag()

1、rank(),dense_rank(),row_number()的区别

rank()与dense_rank()的区别：

两者都是计算一组数值中的排序值，

但是在有并列关系时，dence_rank中相关等级不会跳过，rank则跳过。

rank() 是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）

dense_rank()是连续排序，有两个第二名时仍然跟着第三名。

row_number()：

row_number over(partition by A order by B)

根据A分组，在分组内根据B排序，且得出来的值是每组内部排序后的顺序编号（组内连续的唯一的）

其主要是‘行’的信息，并没有排名。row_number()必须与order by一起使用，

多用于分页查询，比如查询10-100个学生。

2、ntile(x)--平均分区函数

3、lag() over(partition by A order by B)

lead() over(partition by A order by B)

lag和lead中有三个参数，lag('列名',offset,'超出记录窗口时的默认值')

lag和lead可以获取，按一定顺序B排列的当前行的上下相邻若干offset的莫隔行的某个列。

lag()是向前，lead()是向后。

参考 https://www.cnblogs.com/lihaoyang/p/6756956.html

开窗函数（OVER子句）用于为行定义一个窗口（这里的窗口是指运算将要 *** 作的行的集合），它对一组值进行 *** 作，不需要使用GROUP BY子句对数据进行分组，能够在同一行

中同时返回基础行的列和聚合列。举例来说，如果要得到一个年级所有班级所有学生的平均

分，按照传统的写法，肯定是通过AVG聚合函数来实现求平均分。由于聚合函数是以GROUP BY 查询作为 *** 作的上下文对一组值进行聚合，GROUP BY *** 作对数据进行分组后，查询

为每个组只返回一行数据，因此，我们不能同时返回基础列（班级，学生等列），而只能得

到聚合列。

我也是用oracle数据库的，在实际开发中用到开窗函数和分析函数的机会还是很少的，用聚合函数的时候非常多，请LZ多关注聚合函数，下面是我上各大网站收集的，希望对楼主有所帮助。

分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是

对于每个组返回多行，而聚合函数对于每个组只返回一行。

下面通过几个例子来说明其应用。

1：统计某商店的营业额。

date sale

1 20

2 15

3 14

4 18

5 30

规则：按天统计：每天都统计前面几天的总额

得到的结果：

DATE SALE SUM

----- -------- ------

1 2020 --1天

2 1535 --1天＋2天

3 1449 --1天＋2天＋3天

4 1867.

5 3097.

2:统计各班成绩第一名的同学信息

NAME CLASS S

----- ----- ----------------------

fda1 80

ffd1 78

dss1 95

cfe2 74

gds2 92

gf 3 99

ddd3 99

adf3 45

asdf 3 55

3dd3 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

----- ----- ---------------------- ----------------------

dss1 95 1

gds2 92 1

gf 3 99 1

ddd3 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 CSUM(C)OVER(PARTITIONBYA)

-- -- ------- ------------------------

h b 33

m a 24

m a 24

n a 36

n b 26

n b 16

x b 39

x b 29

x b 49

如果用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)

other（

数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。

一、 over函数

over函数指定了分析函数工作的数据窗口的大小，这个数据窗口大小可能会随着行的变化而变化，例如：

over(order by salary)按照salary排序进行累计，order by是个默认的开窗函数

over(partition by deptno) 按照部门分区

over(order by salary range between 50 preceding and 150 following)每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150的数据记录

over(order by salary rows between 50 perceding and 150 following)前50行，后150行

over（order by salary rows between unbounded preceding and unbounded following）所有行

over（order by salary range between unbounded preceding and unbounded following）所有行

二、 sum函数

功能描述：该函数计算组中表达式的累积和。

SAMPLE：下例计算同一经理下员工的薪水累积值

SELECT manager_id, last_name, salary,

SUM (salary) OVER (PARTITION BY manager_id ORDER BY salary

RANGE UNBOUNDED PRECEDING) l_csum

FROM employees

WHERE manager_id in (101,103,108)

三、 应用实例

1， 测试环境设置

设有销售表t_sales (subcompany,branch,region,customer,sale_qty)存储客户的销售明细，记录如下所示。

Subcompany Branch Region Customer Sale_qty

北京分公司 北京经营部 片区1 客户1 1

北京分公司 北京经营部 片区1 客户1 1

北京分公司 北京经营部 片区1 客户2 1

北京分公司 北京经营部 片区1 客户2 1

北京分公司 北京经营部 片区2 客户1 1

北京分公司 北京经营部 片区2 客户1 1

北京分公司 北京经营部 片区2 客户2 1

北京分公司 北京经营部 片区2 客户2 1

北京分公司 其他经营部 片区1 客户1 1

北京分公司 其他经营部 片区1 客户1 1

北京分公司 其他经营部 片区1 客户2 1

北京分公司 其他经营部 片区1 客户2 1

北京分公司 其他经营部 片区2 客户1 1

北京分公司 其他经营部 片区2 客户1 1

北京分公司 其他经营部 片区2 客户2 1

北京分公司 其他经营部 片区2 客户2 1

create table t_sales(

subcompany varchar2(40),

branch varchar2(40),

region varchar2(40),

customer varchar2(40),

sale_qty numeric(18,4)

)

comment on table t_sales is '销售表，分析函数测试'

comment on column t_sales.subcompany is '分公司'

comment on column t_sales.branch is '经营部'

comment on column t_sales.region is '片区'

comment on column t_sales.customer is '客户'

comment on column t_sales.sale_qty is '销售数量'

2，问题提出

现在要求给出销售汇总报表，报表中需要提供的数据包括客户汇总，和客户在其上级机构中的销售比例。

Subcompany Branch Region Customer Sale_qty Rate

北京分公司 北京经营部 片区1 客户1 2 50％

北京分公司 北京经营部 片区1 客户2 2 50％

北京分公司 北京经营部 片区1 小计 4 50％

北京分公司 北京经营部 片区2 客户1 2 50％

北京分公司 北京经营部 片区2 客户2 2 50％

北京分公司 北京经营部 片区2 小计 4 50％

北京分公司 北京经营部 小计 小计 8 50％

北京分公司 北京经营部 片区1 客户1 2 50％

北京分公司 北京经营部 片区1 客户2 2 50％

北京分公司 北京经营部 片区1 小计 4 50％

北京分公司 北京经营部 片区2 客户1 2 50％

北京分公司 北京经营部 片区2 客户2 2 50％

北京分公司 北京经营部 片区2 小计 4 50％

北京分公司 北京经营部 小计 小计 8 50％

北京分公司 小计 小计 小计 16 100％

3，解决方案（方案1）

首先我们可以使用oracle对group by 的扩展功能rollup得到如下的聚合汇总结果。

select

subcompany,

branch,

region,

customer,

sum(sale_qty) sale_qty

from t_sales

group by rollup(subcompany,branch,region,customer)

Subcompany Branch Region Customer Sale_qty

北京分公司 北京经营部 片区1 客户1 2

北京分公司 北京经营部 片区1 客户2 2

北京分公司 北京经营部 片区1 4

北京分公司 北京经营部 片区2 客户1 2

北京分公司 北京经营部 片区2 客户2 2

北京分公司 北京经营部 片区2 4

北京分公司 北京经营部 8

北京分公司 其他经营部 片区1 客户1 2

北京分公司 其他经营部 片区1 客户2 2

北京分公司 其他经营部 片区1 4

北京分公司 其他经营部 片区2 客户1 2

北京分公司 其他经营部 片区2 客户2 2

北京分公司 其他经营部 片区2 4

北京分公司 其他经营部 8

北京分公司16

16

分析上面的临时结果，我们看到：

明细到客户的汇总信息，其除数为当前的sum(sale_qty),被除数应该是到片区的小计信息。

明细到片区的汇总信息，其除数为片区的sum(sale_qty),被除数为聚合到经营部的汇总数据。

。。。

考虑到上述因素，我们可以使用oracle的开窗函数over，将数据定位到我们需要定位的记录。如下代码中，我们利用开窗函数over直接将数据定位到其上次的小计位置。

over(partition by decode(f_branch, 1, null, subcompany), decode(f_branch, 1, null, decode(f_region, 1, null, branch)), decode(f_branch, 1, null, decode(f_region, 1, null, decode(f_customer, 1, null, region))), null)

经整理后的查询语句如下。

select subcompany,

decode(f_branch, 1,subcompany||'(С¼Æ)', branch),

decode(f_region,1,branch||'(С¼Æ)',region),

decode(f_customer,1,region||'(С¼Æ)', customer),

sale_qty,

trim(to_char(round(sale_qty/

sum(sale_qty) over(partition by decode(f_branch, 1, null, subcompany), decode(f_branch, 1, null, decode(f_region, 1, null, branch)), decode(f_branch, 1, null, decode(f_region, 1, null, decode(f_customer, 1, null, region))), null),2) *100,99990.99))

from (select grouping(branch) f_branch,

grouping(region) f_region,

grouping(customer) f_customer,

subcompany,

branch,

region,

customer,

sum(sale_qty) sale_qty

from t_sales

group by subcompany, rollup(branch, region, customer))

Subcompany Branch Region Customer Sale_qty Rate

北京分公司 北京经营部 片区1 客户1 2 50.00

北京分公司 北京经营部 片区1 客户2 2 50.00

北京分公司 北京经营部 片区2 客户1 2 50.00

北京分公司 北京经营部 片区2 客户2 2 50.00

北京分公司 北京经营部 片区1 片区1(小计) 4 50.00

北京分公司 北京经营部 片区2 片区2(小计) 4 50.00

北京分公司 其他经营部 片区1 客户1 2 50.00

北京分公司 其他经营部 片区1 客户2 2 50.00

北京分公司 其他经营部 片区2 客户1 2 50.00

北京分公司 其他经营部 片区2 客户2 2 50.00

北京分公司 其他经营部 片区1 片区1(小计) 4 50.00

北京分公司 其他经营部 片区2 片区2(小计) 4 50.00

北京分公司 北京经营部 北京经营部(小计) (小计) 8 50.00

北京分公司 其他经营部 其他经营部(小计) (小计) 8 50.00

北京分公司 北京分公司(小计) (小计) (小计) 16 100.00

北京分公司 北京经营部 片区1 客户1 2 50.00

4，可能的另外一种解决方式（方案2）

select subcompany,

decode(f_branch, 1,subcompany||'(С¼Æ)', branch),

decode(f_region,1,branch||'(С¼Æ)',region),

decode(f_customer,1,region||'(С¼Æ)', customer),

sale_qty,

/* trim(to_char(round(sale_qty/*/

decode(f_branch+f_region+f_customer,

0,

(sum(sale_qty) over(partition by subcompany,branch,region))/2,

1,

(sum(sale_qty) over(partition by subcompany,branch))/3,

2,

(sum(sale_qty) over(partition by subcompany))/4 ,

sum(sale_qty) over()/4

)/*

,2) *100,99990.99))*/

from (select grouping(branch) f_branch,

grouping(region) f_region,

grouping(customer) f_customer,

subcompany,

branch,

region,

customer,

sum(sale_qty) sale_qty

from t_sales

group by subcompany, rollup(branch, region, customer))

在上面的解决方式中，最大的问题在于开窗函数过大。导致每次计算涉及到的行数过多，影响到执行的速度和效率。并且需要额外的计算处理清除多余叠加进去的数值 。

）

---