Oracle分析函数小结(over()、over(order by ...)、over(partition by...))
上一篇 / 下一篇 2016-05-27 17:30:36 / 个人分类:orcale
以前测试的时候需要验证些报表取数是否正确,会有些统计要用到分析函数,所以就学习了一下,下面先给大家讲讲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_ID | S_ID | S_NAME | SCORE |
1 | 01 | 0101 | 李四 | 80.0 |
2 | 01 | 0102 | 陆小凤 | 78.0 |
3 | 01 | 0103 | 王五 | 92.0 |
4 | 01 | 0104 | 杨丹 | 83.0 |
5 | 01 | 0105 | 张三 | 65.0 |
6 | 02 | 0201 | 杨艳 | 98.0 |
7 | 02 | 0202 | 黄明 | 94.0 |
8 | 02 | 0203 | 刘晓 | 87.0 |
9 | 02 | 0204 | 陈诚 | 77.0 |
10 | 02 | 0205 | 唐煌 | 88.0 |
11 | 03 | 0301 | 苏三 | 91.0 |
12 | 03 | 0302 | 戴二 | 67.0 |
13 | 03 | 0303 | 林森 | 56.0 |
14 | 03 | 0304 | 袁奎 | 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_NO | EMP_NO | NAME | SAL |
1 | 10 | 1001 | 张三 | 4578.11000 |
2 | 10 | 1002 | 李四 | 11455.47000 |
3 | 10 | 1003 | 王五 | 15987.32000 |
4 | 20 | 2001 | 赵六 | 8913.29000 |
5 | 20 | 2002 | 唐铭 | 4977.19000 |
6 | 20 | 2003 | 宋清 | 14932.81000 |
7 | 30 | 3001 | 元凯 | 6837.28000 |
8 | 30 | 3002 | 陈冬 | 7691.56000 |
9 | 30 | 3003 | 林梅 | 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
TAG: