Oracle分析函数八——CUBE，ROLLUP

python与大数据分析

发布于 2022-03-11 16:56:17

6100

文章被收录于专栏：python与大数据分析python与大数据分析

CUBE

功能描述：

注意：

ROLLUP

功能描述：

注意：

如果是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操作。

代码如下：

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);

常用代码如下：

SELECT
  student_name,
  subjects,
  sum(score) 
  FROM studentscore 
 GROUP BY CUBE(student_name,subjects);
等同于以下标准SQL
SELECT NULL,subjects,SUM(score) 
  FROM studentscore 
 GROUP BY subjects
 UNION
SELECT student_name,NULL,SUM(score) 
  FROM studentscore 
 GROUP BY student_name
 UNION
SELECT NULL,NULL,SUM(score)
  FROM studentscore 
 UNION
SELECT student_name,subjects,SUM(score)
  FROM studentscore 
 GROUP BY student_name,subjects

SELECT
  student_name,
  subjects,
  sum(score) 
  FROM studentscore 
 GROUP BY ROLLUP(student_name,subjects);

SELECT student_name,NULL,SUM(score) 
  FROM studentscore 
 GROUP BY student_name
 UNION
SELECT NULL,NULL,SUM(score)
  FROM studentscore 
 UNION
SELECT student_name,subjects,SUM(score)
  FROM studentscore 
 GROUP BY student_name,subjects


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;

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;

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 ''
  END SUMMARY,
  student_name,
  subjects,
  sum(score) 
  FROM studentscore 
 GROUP BY CUBE(student_name,subjects)
 ORDER BY 1,2;

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2021-09-11，如有侵权请联系 cloudcommunity@tencent.com 删除

javascript