【转】CUBE与ROLLUP分析函数

gentle.blog.chinaunix.net

CUBE与ROLLUP分析函数：

--建立测试表
CREATE TABLE GROUP_TEST(ID NUMBER,NAME VARCHAR2(10),VALUE NUMBER,REMARKS VARCHAR2(255));

 

--表注释（加注释是因为我对加注释的语句不熟悉。练习一下）
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,'&NAME',&VALUE,'&REMARKS');

 

--查看插入的数据
SELECT * FROM GROUP_TEST;

 

ID    NAME   VALUE   REMARKS
1     tom      8   生活用品
2     marry   9   生活用品
3     lee      8   生活用品
4     tom      9   学习用品
5     marry    10   学习用品
6     lee      8   学习用品
7     tom      15   生活用品
8     marry    16   生活用品
9     lee      16   生活用品
10   lee      15   学习用品
11   marry    15   学习用品
12   tom      16   学习用品

 

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

 

NAME    REMARKS   SUM(VALUE)
marry   生活用品   25
tom     生活用品   23
lee     学习用品   23
lee     生活用品   24
marry   学习用品   25
tom     学习用品   25

 

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

 

--结果不是很直观。现在加上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;

 

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

 

 

--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差异不大【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;

 

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

 

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

 

--GROUPING_ID【CUBE】
SELECT GROUPING_ID(NAME,REMARKS) G_ID,NAME,REMARKS,SUM(VALUE) FROM GROUP_TEST GROUP BY ROLLUP(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也不是太理解。做了这个例子之后觉得CUBE，ROLLUP就是那么一回事。

 

结束。