Oracle 高级分组用法详解
2017-09-29 本文已影响62人
Bobby0322
创建表及测试数据
CREATE TABLE STUDENTS
( ID NUMBER(15,0),
AREA VARCHAR2(10),
STU_TYPE VARCHAR2(2),
SCORE NUMBER(20,2));
INSERT INTO STUDENTS VALUES(1, '111', 'G', 80 );
INSERT INTO STUDENTS VALUES(1, '111', 'J', 80 );
INSERT INTO STUDENTS VALUES(1, '222', 'G', 89 );
INSERT INTO STUDENTS VALUES(1, '222', 'G', 68 );
INSERT INTO STUDENTS VALUES(2, '111', 'G', 80 );
INSERT INTO STUDENTS VALUES(2, '111', 'J', 70 );
INSERT INTO STUDENTS VALUES(2, '222', 'G', 60 );
INSERT INTO STUDENTS VALUES(2, '222', 'J', 65 );
INSERT INTO STUDENTS VALUES(3, '111', 'G', 75 );
INSERT INTO STUDENTS VALUES(3, '111', 'J', 58 );
INSERT INTO STUDENTS VALUES(3, '222', 'G', 58 );
INSERT INTO STUDENTS VALUES(3, '222', 'J', 90 );
INSERT INTO STUDENTS VALUES(4, '111', 'G', 89 );
INSERT INTO STUDENTS VALUES(4, '111', 'J', 90 );
INSERT INTO STUDENTS VALUES(4, '222', 'G', 90 );
INSERT INTO STUDENTS VALUES(4, '222', 'J', 89 );
COMMIT;
SELECT * FROM STUDENTS;
GROUPING SETS
类似于ROLLUP和CUBE
SELECT ID
,AREA
,STU_TYPE
,SUM(SCORE) SCORE
FROM STUDENTS
GROUP BY GROUPING SETS((ID,AREA,STU_TYPE),(ID,AREA),ID)
ORDER BY ID,AREA,STU_TYPE;
/*
ID AREA STU_TYPE SCORE
1 1 111 G 80
2 1 111 J 80
3 1 111 160
4 1 222 G 157
5 1 222 157
6 1 317
7 2 111 G 80
8 2 111 J 70
9 2 111 150
10 2 222 G 60
11 2 222 J 65
12 2 222 125
13 2 275
14 3 111 G 75
15 3 111 J 58
16 3 111 133
17 3 222 G 58
18 3 222 J 90
19 3 222 148
20 3 281
21 4 111 G 89
22 4 111 J 90
23 4 111 179
24 4 222 G 90
25 4 222 J 89
26 4 222 179
27 4 358*/
理解GROUPING SETS
/*理解GROUPING SETS
SELECT A, B, C, SUM( D ) FROM T
GROUP BY GROUPING SETS ( A, B, C )
等效于
SELECT * FROM (
SELECT A, NULL, NULL, SUM( D ) FROM T GROUP BY A
UNION ALL
SELECT NULL, B, NULL, SUM( D ) FROM T GROUP BY B
UNION ALL
SELECT NULL, NULL, C, SUM( D ) FROM T GROUP BY C
*/
ROLLUP
ROLLUP 是GROUP BY 的扩展，它只能出现在GROUP BY后面。主要是为了解决多层分组的问题。
SELECT ID,AREA,STU_TYPE,SUM(SCORE) SCORE
FROM STUDENTS
GROUP BY ROLLUP(ID,AREA,STU_TYPE)
ORDER BY ID,AREA,STU_TYPE;
/*
ID AREA STU_TYPE SCORE
1 1 111 G 80
2 1 111 J 80
3 1 111 160
4 1 222 G 157
5 1 222 157
6 1 317
7 2 111 G 80
8 2 111 J 70
9 2 111 150
10 2 222 G 60
11 2 222 J 65
12 2 222 125
13 2 275
14 3 111 G 75
15 3 111 J 58
16 3 111 133
17 3 222 G 58
18 3 222 J 90
19 3 222 148
20 3 281
21 4 111 G 89
22 4 111 J 90
23 4 111 179
24 4 222 G 90
25 4 222 J 89
26 4 222 179
27 4 358
28 1231*/
理解ROLLUP
/*理解ROLLUP
SELECT A, B, C, SUM( D )
FROM T
GROUP BY ROLLUP(A, B, C);
等效于
SELECT * FROM (
SELECT A, B, C, SUM( D ) FROM T GROUP BY A, B, C
UNION ALL
SELECT A, B, NULL, SUM( D ) FROM T GROUP BY A, B
UNION ALL
SELECT A, NULL, NULL, SUM( D ) FROM T GROUP BY A
UNION ALL
SELECT NULL, NULL, NULL, SUM( D ) FROM T
)
*/
CUBE
CUBE类似于ROLLUP,但产生的分组是分组类表的排列组合
SELECT ID,AREA,STU_TYPE,SUM(SCORE) SCORE
FROM STUDENTS
GROUP BY CUBE(ID,AREA,STU_TYPE)
ORDER BY ID,AREA,STU_TYPE;
/*1 111 G 80
1 111 J 80
1 111 160
1 222 G 157
1 222 157
1 G 237
1 J 80
1 317
2 111 G 80
2 111 J 70
2 111 150
2 222 G 60
2 222 J 65
2 222 125
2 G 140
2 J 135
2 275
3 111 G 75
3 111 J 58
3 111 133
3 222 G 58
3 222 J 90
3 222 148
3 G 133
3 J 148
3 281
4 111 G 89
4 111 J 90
4 111 179
4 222 G 90
4 222 J 89
4 222 179
4 G 179
4 J 179
4 358
111 G 324
111 J 298
111 622
222 G 365
222 J 244
222 609
G 689
J 542
1231*/
理解CUBE
/*理解CUBE
SELECT A, B, C, SUM( D ) FROM T
GROUP BY CUBE( A, B, C)
等效于
SELECT A, B, C, SUM( D ) FROM T
GROUP BY GROUPING SETS(
( A, B, C ),
( A, B ), ( A ), ( B, C ),
( B ), ( A, C ), ( C ), () )*/
GROUPING
从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现NULL,
如何来区分到底是根据那个字段做的汇总呢,GROUPING函数判断是否合计列!
SELECT
DECODE(GROUPING(ID),1,'ALL ID',ID) ID
,DECODE(GROUPING(AREA),1,'ALL AREA',TO_CHAR(AREA)) AREA
,DECODE(GROUPING(STU_TYPE),1,'ALL_STU_TYPE',STU_TYPE) STU_TYPE
,SUM(SCORE) SCORE
FROM STUDENTS
GROUP BY CUBE(ID,AREA,STU_TYPE)
ORDER BY ID,AREA,STU_TYPE;
/*1 111 ALL_STU_TYPE 160
1 111 G 80
1 111 J 80
1 222 ALL_STU_TYPE 157
1 222 G 157
1 ALL AREA ALL_STU_TYPE 317
1 ALL AREA G 237
1 ALL AREA J 80
2 111 ALL_STU_TYPE 150
2 111 G 80
2 111 J 70
2 222 ALL_STU_TYPE 125
2 222 G 60
2 222 J 65
2 ALL AREA ALL_STU_TYPE 275
2 ALL AREA G 140
2 ALL AREA J 135
3 111 ALL_STU_TYPE 133
3 111 G 75
3 111 J 58
3 222 ALL_STU_TYPE 148
3 222 G 58*/
综合示例代码
CREATE TABLE T_DIST
(
TYPE_CD NUMBER,
BUYER_ID VARCHAR2(50),
ORDER_DT DATE,
SO_ID VARCHAR2(50) NOT NULL,
STOCK_ID VARCHAR2(50) NOT NULL,
UNIT_PRICE NUMBER,
DISCOUNT NUMBER,
QTY NUMBER
);
TRUNCATE TABLE T_DIST;
INSERT INTO T_DIST VALUES(1,'CN1001',TO_DATE('2008-04-01','YYYY-MM-DD'),'S9001','29110311',50,10,8);
INSERT INTO T_DIST VALUES(1,'CN1001',TO_DATE('2008-04-02','YYYY-MM-DD'),'S9002','29110312',60,20,2);
INSERT INTO T_DIST VALUES(1,'CN1001',TO_DATE('2008-04-03','YYYY-MM-DD'),'S9003','29110313',70,15,3);
INSERT INTO T_DIST VALUES(2,'CN1001',TO_DATE('2008-04-04','YYYY-MM-DD'),'S9004','29110312',60,15,5);
INSERT INTO T_DIST VALUES(2,'CN1001',TO_DATE('2008-04-05','YYYY-MM-DD'),'S9005','29110311',70,10,6);
INSERT INTO T_DIST VALUES(3,'CN1001',TO_DATE('2008-04-06','YYYY-MM-DD'),'S9006','29110313',55,20,4);
INSERT INTO T_DIST VALUES(3,'CN1001',TO_DATE('2008-04-06','YYYY-MM-DD'),'S9007','29110311',40,10,3);
INSERT INTO T_DIST VALUES(3,'CN1001',TO_DATE('2008-04-07','YYYY-MM-DD'),'S9008','29110312',50,50,5);
INSERT INTO T_DIST VALUES(3,'CN1001',TO_DATE('2008-04-07','YYYY-MM-DD'),'S9009','29110313',80,10,2);
INSERT INTO T_DIST VALUES(1,'CN1001',TO_DATE('2008-04-08','YYYY-MM-DD'),'S9010','29110311',65,10,1);
COMMIT;
SELECT * FROM T_DIST;
/*请问:如何实现如下结果,谢谢!
即计算按STOCK_ID,TYPE_CD,DISTOUNT分组,计算每个产品的销售额(QTY*UNIT_PRICE)及销售百分比,并有小计
STOCK_ID TYPE_CD DISCOUNT AVG_PRICE SUM_TOT PCT
-------------------------------------------------------------------------------------------------
29110311 1 10 57.50 465 46.27%
29110311 2 10 70.00 420 41.79%
29110311 3 10 40.00 120 11.94%
小计 55.83 1005 100.00%
29110312 1 20 60.00 120 17.91%
29110312 2 15 60.00 300 44.78%
29110312 3 50 50.00 250 37.31%
小计 56.67 670 100.00%
29110313 1 15 70.00 210 35.59%
29110313 3 10 80.00 160 27.12%
29110313 3 20 55.00 220 37.29%
小计 68.33 590 100.00%
*/
SELECT DECODE(RN, 1, '小计', STOCK_ID) STOCK_ID
,TYPE_CD
,DISCOUNT
,AVG_PRICE
,SUM_TOT
,ROUND(NVL(RATIO_TO_REPORT(DECODE(RN, 0, SUM_TOT, NULL)) OVER(PARTITION BY STOCK_ID), 1) * 100, 2) || '%' PCT
FROM
( SELECT STOCK_ID
,TYPE_CD
,DISCOUNT
,ROUND(AVG(UNIT_PRICE), 2) AVG_PRICE
,SUM(QTY * UNIT_PRICE) SUM_TOT
,GROUPING(TYPE_CD) RN -- GROUPING函数返回的是0或者1
,GROUP_ID() GROUP_ID -- GROUP_ID 分组可以识别哪些是重复的分组,唯一的分组返回0
FROM T_DIST
GROUP BY GROUPING SETS((STOCK_ID), (STOCK_ID, TYPE_CD, DISCOUNT)) );
