以前测试的时候需要验证些报表取数是否正确，会有些统计要用到分析函数，所以就学习了一下，下面先给大家讲讲over()、over(order by ...)、over(partition by...)
首先准备工作：先安装oracle服务端，然后建用户、建表空间、建表、造数据
如我建的建单一点的成绩表score，主要存储了班级编号、学生编号、学生姓名、成绩
-- Create table
create table SCORE
(
class_id VARCHAR2(28),
s_id VARCHAR2(4),
s_name VARCHAR2(6),
score NUMBER(4,1)
)
tablespace ASPROD
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table SCORE
is '成绩';
-- Add comments to the columns
comment on column SCORE.class_id
is '班级编号';
comment on column SCORE.s_id
is '学生编号';
comment on column SCORE.s_name
is '学生姓名';
comment on column SCORE.score
is '成绩';
然后插入数据
CLASS_IDS_IDS_NAMESCORE
1010101李四80.0
102陆小凤78.0
3010103王五92.0
4010104杨丹83.0
5010105张三65.0
601杨艳98.0
702黄明94.0
803刘晓87.0
904陈诚77.0
1005唐煌88.0
11030301苏三91.0
12030302戴二67.0
13030303林森56.0
14030304袁奎98.0
一、
下面来说一下分析函数：分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。
Exp：
Sql代码
select t.s_id,t.s_name,sum(t.score) from score t;
执行上面的sql会报：ORA-00937：不是单组分组函数
结论：聚合函数只能返回1行值。
Sql代码：
select t.s_id, t.s_name, sum(t.score) over() sum from score t;
sum的结果都是总的sum(score)的值
返回的结果：
结论：这就是每个组返回多行。
二、
Over()、over(order by...)与over(partition by ...)之前的区别分析函数over()用在聚合函数(max(),sum()....)后面，可返回多行所有结果的聚合值。
如上sql：select t.s_id,t.s_name,sum(t.score) over() sum from score t;over(order by...)用在聚合函数(max(),sum()....)后面，可返回根据排序结果进行统计到当前行的聚合值(即“连续”统计)
exp：
sql代码：
select t.class_id,
t.s_id,
t.s_name,
t.score,
sum(t.score) over(order by t.s_name) sum
from score t;
返回结果：
分析：第二行的sum=第一行里的score+第二行里的score；
第三行的sum=第一行里的score+第二行里的score+第三行里的score；
……
结论：返回值是根据排序后的结果，当前所在的行进行统计的。over(partition by ...)用在聚合函数(max(),sum()....)后面，可根据partition by里制定的某一列来统计聚合值。
Exp：
Sql‘代码
select t.class_id,
t.s_id,
t.s_name,
t.score,
sum(t.score) over(partition by t.class_id) sum
from score t;
返回的结果：
分析：每个sum的值都是把class_id相同的score值进行求和。
结论，根据partition by里指定的某一列来统计聚合值。
三、例子
问：按班级“连续”求总和
答：
Sql代码
select t.class_id,
t.s_name,
t.score,
sum(t.score) over(partition by t.class_id order by t.s_name) sum
from score t;
返回的结果：
分析：先根据partition by进行分组，然后再根据order by进行排序“连续”统计(相当于按班级进行累计值)
大家也可以造些部门、员工、工资的数据来体验一下，或者结合项目里的数据，试一下去统计，例如销售的，不同地区不同业务员的销售记录等等，大家都可以试一下
除了sum还可以max、min、avg等等
---sum(a) over()
---sum(a) over(order by ...)
---sum(a) over(partition by...)
---sum(a) over(partition by... order by ...)
---max(a) over()
---max(a) over(order by...)
---max(a) over(partition by...)
---max(a) over(partition by... order by ...)
---min(a) over()
---min (a) over(order by...)
---min (a) over(partition by...)
---min (a) over(partition by... order by ...)
---avg(a) over()
--- avg (a) over(order by...)
--- avg (a) over(partition by...)
--- avg (a) over(partition by... order by ...)
补充：
建表：
-- Create table
create table EMP
(
DEP_NO VARCHAR2(255),
EMP_NO VARCHAR2(255),
NAME VARCHAR2(255),
SAL NUMBER(22,5)
)
tablespace TEST
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 16
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column EMP.DEP_NO
is '部门编号';
comment on column EMP.EMP_NO
is '员工编号';
comment on column EMP.NAME
is '员工姓名';
comment on column EMP.SAL
is '工资';
插入数据：
DEP_NOEMP_NONAMESAL
1101001张三4578.11000
2101002李四11455.47000
3101003王五15987.32000
401赵六8913.29000
502唐铭4977.19000
620宋清14932.81000
7303001元凯6837.28000
8303002陈冬7691.56000
9303003林梅5678.12000
--查询各部门中的最低工资通常写法
select dep_no, min(sal) min, max(sal) max from emp group by dep_no;
查询结果：
--假如想要把本部门所有员工的工资，与本部门的最低，最高工资对比时显然上述查询是做不到的，则我们可以用以下语句：
select dep_no,
name,
sal,
avg(sal) over(partition by dep_no) avg,
min(sal) over(partition by dep_no) min,
max(sal) over(partition by dep_no) max
from emp
group by dep_no, name, sal;
查询结果：
--用于查找记录的上一条，及下一条，以作为对比：
select name,
sal,
lead(sal, 1, null) over(partition by dep_no order by sal) next_sal,
lag(sal, 1, null) over(partition by dep_no order by sal) up_sal
from emp;
注：lead函数是先按dep_no分组，按sal排序，并把当前行sal的下一个值放到next_sal中，比如：
sal=4578.11000的下一个值是11455.47000,sal=11455.47000 的下一个值是15987.32000。
函数中的null是当没有下一个值时用null代替，当然也可以用其他值替换NULL.
lag与lead相反，是取上一个值的意思
查询结果：
当然也可以不要partition,lag的默认参数是1
select name,
sal,
lead(sal, 1, null) over(order by sal) next_sal,
lag(sal, 1, null) over(order by sal) up_sal
