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 ...)之前的区别

  1. 分析函数over()用在聚合函数(max(),sum()....)后面,可返回多行所有结果的聚合值。

如上sql:select t.s_id,t.s_name,sum(t.score) over() sum from score t;

  1. 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;

……

结论:返回值是根据排序后的结果,当前所在的行进行统计的。

  1. 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) overpartition by...

---sum(a) overpartition 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.

 

laglead相反,是取上一个值的意思

查询结果:

 

 

当然也可以不要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:

 

评分:0

我来说两句

日历

« 2024-03-26  
     12
3456789
10111213141516
17181920212223
24252627282930
31      

数据统计

  • 访问量: 65282
  • 日志数: 24
  • 建立时间: 2016-05-27
  • 更新时间: 2016-07-29

RSS订阅

Open Toolbar