Oracle之CUBE与ROLLUP分析函数

1.建表并插入数据

--建立测试表
CREATE TABLE GROUP_TEST(
    ID NUMBER,
    NAME VARCHAR2(10),
    VALUE NUMBER,
    REMARKS VARCHAR2(255)
) SEGMENT CREATION IMMEDIATE;
 
--表注释（加注释是因为我对加注释的语句不熟悉。练习一下）
COMMENT ON TABLE GROUP_TEST IS '为了测试CUBE,ROLLUP,GROUPING,GROUPING_ID的区别';
COMMENT ON COLUMN GROUP_TEST.ID IS '序号';
COMMENT ON COLUMN GROUP_TEST.NAME IS '名字';
COMMENT ON COLUMN GROUP_TEST.VALUE IS '消费金额';
COMMENT ON COLUMN GROUP_TEST.REMARKS IS '备注';
 
--创建序列（序列之前都是用别人的。自己也建一个试试）
CREATE SEQUENCE SEQ_GROUP_TEST
INCREMENT BY 1
START WITH 1
MAXVALUE 999999999;
 
--插入数据[记得COMMIT哦;]
INSERT INTO GROUP_TEST VALUES(SEQ_GROUP_TEST.NEXTVAL,'tom',8,'生活用品');
INSERT INTO GROUP_TEST VALUES(SEQ_GROUP_TEST.NEXTVAL,'marry',9,'生活用品');
INSERT INTO GROUP_TEST VALUES(SEQ_GROUP_TEST.NEXTVAL,'lee',8,'生活用品');
INSERT INTO GROUP_TEST VALUES(SEQ_GROUP_TEST.NEXTVAL,'tom',9,'学习用品');
INSERT INTO GROUP_TEST VALUES(SEQ_GROUP_TEST.NEXTVAL,'marry',10,'学习用品');
INSERT INTO GROUP_TEST VALUES(SEQ_GROUP_TEST.NEXTVAL,'lee',15,'学习用品');
INSERT INTO GROUP_TEST VALUES(SEQ_GROUP_TEST.NEXTVAL,'tom',16,'生活用品');
INSERT INTO GROUP_TEST VALUES(SEQ_GROUP_TEST.NEXTVAL,'tom',8,'生活用品');
INSERT INTO GROUP_TEST VALUES(SEQ_GROUP_TEST.NEXTVAL,'tom',8,'生活用品');
INSERT INTO GROUP_TEST VALUES(SEQ_GROUP_TEST.NEXTVAL,'tom',8,'生活用品');
INSERT INTO GROUP_TEST VALUES(SEQ_GROUP_TEST.NEXTVAL,'tom',8,'生活用品');
INSERT INTO GROUP_TEST VALUES(SEQ_GROUP_TEST.NEXTVAL,'tom',8,'生活用品');
commit;
--查看插入的数据
SELECT * FROM GROUP_TEST;

--truncate table GROUP_TEST;
--drop SEQUENCE SEQ_GROUP_TEST;
--drop TABLE GROUP_TEST;

查询结果

**注意：**创建序列要在创建表时加（SEGMENT CREATION IMMEDIATE）具体原因

2.普通分组查看结果

--没有ROLLUP,CUBE是分组。
SELECT 
    NAME,
    REMARKS,
    SUM(VALUE) 
FROM GROUP_TEST 
GROUP BY NAME,REMARKS;

3.ROLLUP分组

如果是ROLLUP(A, B)的话，首先会对(A、B)进行GROUP BY，然后对(A)进行GROUP BY,最后对全表进行GROUP BY操作。

--ROLLUP分组。[如果是ROLLUP(A, B)的话，首先会对(A、B)进行GROUP BY，然后对(A)进行GROUP BY,最后对全表进行GROUP BY操作。]
SELECT 
    NAME,
    REMARKS,
    SUM(VALUE) 
FROM GROUP_TEST 
GROUP BY ROLLUP(NAME,REMARKS);
/*
lee	    学习用品	15  
marry	学习用品	10
tom	    学习用品	9
tom	    生活用品	64
marry	生活用品	9
lee	    生活用品	8  --对(A、B)进行GROUP BY

tom		           73
lee		           23
marry		       19 --对(A)进行GROUP BY
		
                   115 --对全表进行GROUP BY
*/

3.1ROLLUP+GROUPING

结果不是很直观。现在加上GROUPING再看一下效果。
[0代表按他分组。1代表没有他分组。]

--结果不是很直观。现在加上GROUPING再看一下效果。[0代表按他分组。1代表没有他分组。]【ROLLUP】
SELECT 
    GROUPING(NAME) G_NAME, 
    GROUPING(REMARKS) G_REMARKS,
    NAME,
    REMARKS,
    SUM(VALUE) 
FROM GROUP_TEST 
GROUP BY ROLLUP(NAME,REMARKS) ORDER BY 1,2;
/*
0	0	lee	      生活用品	8
0	0	le        学习用品	15
0	0	tom	      学习用品	9
0	0	marry	  学习用品	10
0	0	marry	  生活用品	9
0	0	tom	      生活用品	64
0	1	tom		            73
0	1	marry		        19
0	1	lee		            23
1	1			            115
*/

• GROUPING可以是合计列更清晰，简单【ROLLUP】

--GROUPING可以是合计列更清晰，简单【ROLLUP】
SELECT 
      DECODE(GROUPING(NAME),1,'合计',NAME), 
      DECODE(GROUPING(REMARKS),1,'小计',REMARKS) ,
      SUM(VALUE) 
FROM GROUP_TEST 
GROUP BY ROLLUP(NAME,REMARKS) ORDER BY 1,2;

3.2 ROLLUP+GROUPING_ID

GROUPING_ID效果与GROUPING大同小异。
[0代表按他分组，
1代表按第一个分组，
2代表按第二个分组，
【3】代表不分组（此处的3。也可以是4.你懂的）]

--GROUPING_ID效果与GROUPING大同小异。[0代表按他分组，1代表按第一个分组，2代表按第二个分组，【3】代表不分组（此处的3。也可以是4.你懂的）]【ROLLUP】
SELECT 
      GROUPING_ID(NAME,REMARKS) G_ID,
      NAME,
      REMARKS,
      SUM(VALUE) 
FROM GROUP_TEST 
GROUP BY ROLLUP(NAME,REMARKS) ORDER BY 1;

• GROUPING_ID可以是合计列更清晰，简单。与GROUPING差异不大

--GROUPING_ID可以是合计列更清晰，简单。与GROUPING差异不大【ROLLUP】
SELECT 
    DECODE(GROUPING_ID(NAME),1,'合计',NAME),
    DECODE(GROUPING_ID(REMARKS),1,'小计',REMARKS),
    SUM(VALUE)
    FROM GROUP_TEST 
GROUP BY ROLLUP(NAME,REMARKS) ORDER BY 1 ,2;

4.CUBE分组

如果是CUBE(A,B)的话，则首先会对(A、B)进行GROUP BY，然后依次是(A)，(B)进行GROUP BY最后对全表进行GROUP BY操作。

--CUBE分组.[如果是CUBE(A,B)的话，则首先会对(A、B)进行GROUP BY，然后依次是(A)，(B)进行GROUP BY最后对全表进行GROUP BY操作。]
SELECT 
    NAME,
    REMARKS,
    SUM(VALUE) 
FROM GROUP_TEST 
GROUP BY CUBE(NAME,REMARKS);

4.1CUBE+GROUPING

--GROUPING【CUBE】
SELECT 
      GROUPING(NAME) G_NAME,
      GROUPING(REMARKS) G_REMARKS,
      NAME,REMARKS,
      SUM(VALUE) 
FROM GROUP_TEST 
GROUP BY CUBE(NAME,REMARKS) ORDER BY 1,2;

• 简洁写法：

--GROUPING合计，小计【CUBE】
SELECT 
    DECODE(GROUPING(NAME),1,'合计',NAME), 
    DECODE(GROUPING(REMARKS),1,'小计',REMARKS) ,
    SUM(VALUE) 
FROM GROUP_TEST 
GROUP BY CUBE(NAME,REMARKS) ORDER BY 1,2;

4.2CUBE+GROUPING_ID

--GROUPING_ID【CUBE】
SELECT 
    GROUPING_ID(NAME,REMARKS) G_ID,
    NAME,
    REMARKS,
    SUM(VALUE) 
FROM GROUP_TEST 
GROUP BY cube(NAME,REMARKS) ORDER BY 1;

• 简洁写法

--GROUPING_ID合计,小计【CUBE】
SELECT 
   DECODE(GROUPING_ID(NAME),1,'合计',NAME),
   DECODE(GROUPING_ID(REMARKS),1,'小计',REMARKS),
   SUM(VALUE)
FROM GROUP_TEST 
GROUP BY CUBE(NAME,REMARKS) ORDER BY 1 ,2;

以上是对CUBE，ROLLUP的示例以及讲解。