oracle_over
来源：互联网 发布：thinkphp3.2项目源码 编辑：程序博客网 时间：2024/06/05 08:04
个人理解：over() 函数 是对 分析函数的一种条件解释，直接点就是 给分析函数加条件吧。
在网上看见比较常用的就是 与 sum()、rank() 函数使用。接下来就用分析下两种函数结合over的用法。
以下测试使用的orace默认的 scott用户下的emp表数据。
1.sum()结合over()
Sql代码
select a.empno as 员工编号
,a.ename as 员工姓名
,a.deptno as 部门编号
,a.sal as 薪酬
,sum(sal) over (partition by deptno) 按部门求薪酬总和
from scott.emp a;
此段sql 执行的结果为：
部门编号 员工姓名 员工编码 薪酬 按部门求薪酬总和
7934 MILLER 10 1300 8750
7782 CLARK 10 2450 8750
7839 KING 10 5000 8750
7369 SMITH 20 800 10875
7876 ADAMS 20 1100 10875
7566 JONES 20 2975 10875
7788 SCOTT 20 3000 10875
7902 FORD 20 3000 10875
7900 JAMES 30 950 9400
7654 MARTIN 30 1250 9400
7521 WARD 30 1250 9400
7844 TURNER 30 1500 9400
7499 ALLEN 30 1600 9400
7698 BLAKE 30 2850 9400
可以从结果上看到sum()函数对部门区分进行了求和统计。其中“partition by”官方点的说法叫做"分区"，其实就是统计的范围条件。
下面在给把上面的sql语句改造下 给 over() 函数加上 “order by sal” 会看到一个更过瘾的效果：
Sql代码
select a.empno as 员工编号
,a.ename as 员工姓名
,a.deptno as 部门编号
,a.sal as 薪酬
,sum(sal) over (partition by deptno) 按部门求薪酬总和
,sum(sal) over (partition by deptno order by sal) 按部门累计薪酬
from scott.emp a;
结果为：
部门编号 员工姓名 员工编码 薪酬 按部门求薪酬总和 按部门累计薪酬
7934 MILLER 10 1300 8750 1300
7782 CLARK 10 2450 8750 3750
7839 KING 10 5000 8750 8750
7369 SMITH 20 800 10875 800
7876 ADAMS 20 1100 10875 1900
7566 JONES 20 2975 10875 4875
7788 SCOTT 20 3000 10875 10875
7902 FORD 20 3000 10875 10875
7900 JAMES 30 950 9400 950
7654 MARTIN 30 1250 9400 3450
7521 WARD 30 1250 9400 3450
7844 TURNER 30 1500 9400 4950
7499 ALLEN 30 1600 9400 6550
7698 BLAKE 30 2850 9400 9400
从结果中可以看的 加了 “order by”后对 统计进行一个累加，这里个人理解为对统计范围规定了个统计顺利，一步一步的统计。
注：此sql语句结尾处不要加“order by”，应为使用的分析函数的(partition by deptno order by sal)
里已经有排序的语句了，如果再在句尾添加排序子句，一致倒罢了，不一致，结果就令人费解了。
2.rank()结合over()
rank函数是分级函数，这个函数必须与 over 函数使用，否则会报一个"缺少窗口函数的错"。我测试sql如下：
Sql代码
select a.empno as 员工编号,
a.sal as 薪资,
a.job as 岗位,
rank() OVER(partition by a.job ORDER BY a.sal desc) as 岗位薪资等级
from scott.emp a;
查询结果为:
员工编号 薪资 岗位 等级
7902 3000 ANALYST 1
7788 3000 ANALYST 1
7934 1300 CLERK 1
7876 1100 CLERK 2
7900 950 CLERK 3
7369 800 CLERK 4
7566 2975 MANAGER 1
7698 2850 MANAGER 2
7782 2450 MANAGER 3
7839 5000 PRESIDENT 1
7499 1600 SALESMAN 1
7844 1500 SALESMAN 2
7654 1250 SALESMAN 3
7521 1250 SALESMAN 3
-----------------------------------------------------------------------
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
