以前测试的时候需要验证些报表取数是否正确,会有些统计要用到分析函数,所以就学习了一下,下面先给大家讲讲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
2010102陆小凤78.0
3010103王五92.0
4010104杨丹83.0
5010105张三65.0
6020201杨艳98.0
7020202黄明94.0
8020203刘晓87.0
9020204陈诚77.0
10020205唐煌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
4202001赵六8913.29000
5202002唐铭4977.19000
6202003宋清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