oracle分组统计 over,Oracle分析函数 OVER(PARTITION ORDER WINDOWING)（四）—— 多维度分组统计函数用法...

简单说明

多维度的分组统计函数包括：ROLLUP和CUBE，用在普通的分组SQL的group by子句中：

GROUP BY (a,b,c)：

先根据a分组，然后根据b分组，最后根据c分组，分组维度是(a,b,c)

GROUP BY ROLLUP (a,b,c)：

分组维度1：(a,b,c)

分组维度2：(a,b)

分组维度3：(a)

分组维度4：最后将全表作为一个组

GROUP BY CUBE (a,b,c)：

分组维度1：(a,b,c)

分组维度2：(a,b)

分组维度3：(a,c)

分组维度4：(a)

分组维度5：(b,c)

分组维度6：(b)

分组维度7：(c)

分组维度8：最后将全表作为一个组

ROLLUP和CUBE关系有点类似于排列和组合之间的关系

ROLLUP相当于排列，分组维度有顺序要求

CUBE相当于组合，分组维度没有顺序要求

测试数据准备

CREATE TABLE studentscore

( student_name varchar2(20),

subjects varchar2(20),

score number);

INSERT INTO studentscore VALUES('WBQ','ENGLISH',90);

INSERT INTO studentscore VALUES('WBQ','MATHS',95);

INSERT INTO studentscore VALUES('WBQ','CHINESE',88);

INSERT INTO studentscore VALUES('CZH','ENGLISH',80);

INSERT INTO studentscore VALUES('CZH','MATHS',90);

INSERT INTO studentscore VALUES('CZH','HISTORY',92);

INSERT INTO studentscore VALUES('CB','POLITICS',70);

INSERT INTO studentscore VALUES('CB','HISTORY',75);

INSERT INTO studentscore VALUES('LDH','POLITICS',80);

INSERT INTO studentscore VALUES('LDH','CHINESE',90);

INSERT INTO studentscore VALUES('LDH','HISTORY',95);

commit;

-- 一张简单的成绩表，包括 学生姓名、科目和成绩

需求一

列转行展示数据，展示某个学生的所有科目成绩，一个较为普通的需求：

select s.student_name,

max(decode(s.subjects, 'ENGLISH', s.score, 0)) ENGLISH,

max(decode(s.subjects, 'MATHS', s.score, 0)) MATHS,

max(decode(s.subjects, 'CHINESE', s.score, 0)) CHINESE,

max(decode(s.subjects, 'HISTORY', s.score, 0)) HISTORY,

max(decode(s.subjects, 'POLITICS', s.score, 0)) POLITICS

from studentscore s

group by s.student_name;

-- 使用decode做列转行，即case行列转换相关的需求

需求二

使用CUBE多维度分组分析，分别根据学生、科目、学生和科目、整个表四个维度分析：

SELECT student_name, subjects, sum(score)

FROM studentscore

GROUP BY CUBE(student_name, subjects)

order by student_name, subjects nulls last;

-- 分组维度为：

-- (student_name,subjects)

-- (student_name)

-- (subjects)

-- 全表

SELECT student_name, subjects, SUM(score)

FROM studentscore

GROUP BY student_name, subjects

UNION

SELECT student_name, NULL, SUM(score)

FROM studentscore

GROUP BY student_name

UNION

SELECT NULL, subjects, SUM(score)

FROM studentscore

GROUP BY subjects

UNION

SELECT NULL, NULL, SUM(score) FROM studentscore;

--CUBE等价SQL

需求三

使用ROLLUP多维度分组分析，分别根据学生和科目、学生、整个表三个维度分析：

SELECT student_name, subjects, sum(score)

FROM studentscore

GROUP BY ROLLUP(student_name, subjects);

SELECT student_name, subjects, SUM(score)

FROM studentscore

GROUP BY student_name, subjects

UNION

SELECT student_name, NULL, SUM(score)

FROM studentscore

GROUP BY student_name

UNION

SELECT NULL, NULL, SUM(score) FROM studentscore;

--ROLLUP等价SQL

需求四

使用函数grouping和grouping_id继续对上方的查询进行优化展示，

grouping 可以对列是否是分析生成的进行判断，分析生成的则为1，反之为0，

grouping_id 可以标识出不同的分组规则，生成一个从0开始的数列，

以上两个函数出现在select子句中，且以上两个函数只能修饰被分组的列。

SELECT grouping(student_name),

grouping(subjects),

student_name,

subjects,

sum(score)

FROM studentscore

GROUP BY CUBE(student_name, subjects)

ORDER BY 1, 2;

SELECT grouping(student_name),

grouping(subjects),

student_name,

subjects,

sum(score)

FROM studentscore

GROUP BY ROLLUP(student_name, subjects)

ORDER BY 1, 2;

-- 使用函数grouping，看出规律，空列一般是分析维度造成的，函数返回值是1

SELECT grouping_id(student_name, subjects),

student_name, subjects, sum(score)

FROM studentscore

GROUP BY CUBE(student_name, subjects)

ORDER BY 1;

SELECT grouping_id(student_name, subjects),

student_name, subjects, sum(score)

FROM studentscore

GROUP BY ROLLUP(student_name, subjects)

ORDER BY 1;

-- 使用函数grouping_id对分组维度进行标识，生成一个从0开始的数列来标识不同的分组维度

从grouping_id的结果可以分析猜测，ROLLUP实际上是对CUBE分组维度过滤之后生成的。

需求五

使用上方的两个函数，借助其他函数，进一步做规范化展示：

SELECT grouping(student_name), grouping(subjects),

decode(grouping(student_name) || grouping(subjects),

'01', '学生成绩合计',

'10', '课目成绩合计',

'11', '总计',

null) SUMMARY,

student_name,

subjects,

sum(score)

FROM studentscore

GROUP BY CUBE(student_name, subjects)

ORDER BY 1, 2;

-- 使用decode对列进行判断，添加对应的描述信息

SELECT grouping(student_name), grouping(subjects),

CASE

WHEN grouping(student_name) = 0 AND grouping(subjects) = 1 THEN

'学生成绩合计'

WHEN grouping(student_name) = 1 AND grouping(subjects) = 0 THEN

'课目成绩合计'

WHEN grouping(student_name) = 1 AND grouping(subjects) = 1 THEN

'总计'

ELSE null

END SUMMARY,

student_name, subjects, sum(score)

FROM studentscore

GROUP BY CUBE(student_name, subjects)

ORDER BY 1, 2;

-- 使用CASE的写法

SELECT decode(grouping(student_name),

0, decode(grouping(subjects), 1, '学生成绩合计', '学生单科成绩'),

1, decode(grouping(subjects), 0, '课目成绩合计', '总计')) SUMMARY,

student_name, subjects, sum(score)

FROM studentscore

GROUP BY CUBE(student_name, subjects)

ORDER BY grouping_id(student_name, subjects), 2;

-- 使用decode嵌套对case多条件的改写

SELECT decode(grouping(student_name),

0, decode(grouping(subjects), 1, '学生成绩合计', '学生单科成绩'),

1, decode(grouping(subjects), 0, '课目成绩合计', '总计')) SUMMARY,

decode(student_name,null,decode(subjects,null,'全部总计','该科目总成绩：'),student_name),

decode(subjects,null,decode(student_name,null,'全部总计','该学生总成绩：'),subjects),

sum(score)

FROM studentscore

GROUP BY CUBE(student_name, subjects)

ORDER BY grouping_id(student_name, subjects), 2;

-- 既然用了decode嵌套，那么送佛送到西

[TOC]