oracle 分析函数 cube,Oracle分析函数 — sum, rollup, cube, grouping用法

本文通过例子展示sum, rollup, cube, grouping的用法。

//首先建score表

create table score(

class  nvarchar2(20),

course   nvarchar2(20),

stu_no  number(5),

stu_name nvarchar2(20),

score   number(2));

//插入数据

insert into score values ('Class_A','Math',10001,'Tough1',95);

insert into score values ('Class_A','Math',10002,'Tough2',93);

insert into score values ('Class_B','Math',10003,'Tough3',94);

insert into score values ('Class_B','Math',10004,'Tough4',88);

insert into score values ('Class_A','Computer',10001,'Tough1',89);

insert into score values ('Class_A','Computer',10002,'Tough2',98);

insert into score values ('Class_B','Computer',10003,'Tough3',89);

insert into score values ('Class_B','Computer',10004,'Tough4',87);

SELECT * FROM score;

CLASS

COURSE

STU_NO

STU_NAME

SCORE

Class_A

Math

10001

Tough1

95

Class_A

Math

10002

Tough2

93

Class_B

Math

10003

Tough3

94

Class_B

Math

10004

Tough4

88

Class_A

Computer

10001

Tough1

89

Class_A

Computer

10002

Tough2

98

Class_B

Computer

10003

Tough3

89

Class_B

Computer

10004

Tough4

87

sum函数

按class,course分组，然后对score统计总合

select class,course,sum(score)/count(*) "AVG_SCORE"

from score

group by class,course;

CLASS

COURSE

AVG_SCORE

Class_B

Math

91

Class_A

Computer

93.5

Class_A

Math

94

Class_B

Computer

88

rollup函数

先按class,course分组汇总;再按class分组汇总;最后全表分组汇总

select class,course,sum(score)/count(*) "AVG_SCORE"

from score

group by rollup(class,course);

CLASS

COURSE

AVG_SCORE

Class_A

Math

94

Class_A

Computer

93.5

Class_A

93.75

Class_B

Math

91

Class_B

Computer

88

Class_B

89.5

91.625

cube函数

先按class,course分组汇总;再按class分组汇总;再按course分组汇总;最后全表分组汇总

select class,course,sum(score)/count(*) "AVG_SCORE"

from score

group by cube(class,course)

order by class,course nulls last;

CLASS

COURSE

AVG_SCORE

Class_A

Computer

93.5

Class_A

Math

94

Class_A

93.75

Class_B

Computer

88

Class_B

Math

91

Class_B

89.5

Computer

90.75

Math

92.5

91.625

rollup和cube区别：

ROLLUP(A,B,C)的话，GROUP BY顺序

(A,B,C)

(A,B)

(A)

最后对全表进行GROUP BY操作。

GROUP BY CUBE(A, B, C)，GROUP BY顺序

(A,B,C)

(A,B)

(A,C)

(A)，

(B,C)

(B)

(C)，

最后对全表进行GROUP BY操作。

grouping函数

rollup和cube函数都会对结果集产生null，这时候可用grouping函数来确认该记录是由哪个字段得出来的。grouping函数用法，带一个参数，参数为字段名，结果是根据该字段得出来的就返回1，反之返回0

select decode(grouping(class), 1, 'All Class', class) "CLASS",

decode(grouping(course), 1, 'All Course', course) "COURSE",

sum(score) / count(*) "AVG_SCORE"

from   score

group  by cube(class, course)

order  by class, course nulls last;

CLASS

COURSE

AVG_SCORE

All Class

All Course

91.625

All Class

Computer

90.75

All Class

Math

92.5

Class_A

All Course

93.75

Class_A

Computer

93.5

Class_A

Math

94

Class_B

All Course

89.5

Class_B

Computer

88

Class_B

Math

91

[转]详解Oracle高级分组函数(ROLLUP, CUBE, GROUPING SETS)