Oracle 高级分组用法详解

创建表及测试数据

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

最后编辑于：2017.12.10 20:44:07

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,772评论 6赞 477
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,458评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,610评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,640评论 1赞 276
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,657评论 5赞 365
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,590评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,962评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,631评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,870评论 1赞 297
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,611评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,704评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,386评论 4赞 319
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,969评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,944评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,179评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 44,742评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,440评论 2赞 342

Oracle 高级分组用法详解

创建表及测试数据

GROUPING SETS

理解GROUPING SETS

ROLLUP

理解ROLLUP

CUBE

理解CUBE

GROUPING

综合示例代码

推荐阅读更多精彩内容