CUBE与ROLLUP分析函数
2012-01-05 16:32:13
分类: Oracle
CUBE与ROLLUP分析函数:
--建立测试表
CREATE TABLE GROUP_TEST(ID NUMBER,NAME VARCHAR2(10),VALUE NUMBER,REMARKS VARCHAR2(255));
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 '备注';
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;
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');
INSERT INTO GROUP_TEST VALUES(SEQ_GROUP_TEST.NEXTVAL,'&NAME',&VALUE,'&REMARKS');
--查看插入的数据
SELECT * FROM GROUP_TEST;
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 学习用品
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;
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
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);
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;
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;
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;
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;
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);
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;
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;
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;
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;
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就是那么一回事。
结束。