Oracle ROLLUP和CUBE 用法 | decode | grouping | grouping_id

最新推荐文章于 2021-04-05 02:59:48 发布

学要无止尽

最新推荐文章于 2021-04-05 02:59:48 发布

阅读量1.8k

点赞数

分类专栏： Database

本文链接：https://blog.csdn.net/liuj2511981/article/details/9251877

版权

Database 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

1、理论

Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。

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

如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。 grouping_id()可以美化效果：

也可以 Group by Rollup（A,（B,C）） ,Group by A Rollup（B,C），…… 这样任意按自己想要的形式结合统计数据，非常方便。

拓展：

oracle中decode函数
DECODE函数相当于一条件语句(IF).它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然，如果未能与任何一个实参序偶匹配成功，则函数也有默认的返回值。区别于SQL的其它函数，DECODE函数还能识别和操作空值. 其具体的语法格式如下：
DECODE(input_value,value,result[,value,result…][,default_result]);

其中：
input_value 试图处理的数值。DECODE函数将该数值与一系列的序偶相比较，以决定最后的返回结果 value 是一组成序偶的数值。如果输入数值与之匹配成功，则相应的结果将被返回。对应一个空的返回值，可以使用关键字NULL于之对应 result 是一组成序偶的结果值 default_result 未能与任何一序偶匹配成功时，函数返回的默认值。
下面的例子说明了，如何读取用户CHECKUP表SEAPARK中的BLOOD_TEST_FLAG列下的项目，作为DECODE函数的实参支持值。

 SELECT checkup_type,  
DECODE(blood_test_flag,’Y’,’Yes’,’N’,’No’,NULL,’None’,’Invalid’)  FROM checkup;

Grouping函数:
可以接受一列，返回0或1。如果列值为空，则返回1，非空则返回0。它只能在rollup和或cube函数中使用，因为在统计中显示“全部”的那一项统计值时，那一项的标签通常是空的，这时grouping就非常有用，还可以在grouping的基础上进行decode, case等进行美化。

SQL> select grouping(index_type) g_ind, grouping(status) g_st, index_type, status, count(*)
  2  from t group by rollup(index_type, status) order by 1, 2;

     G_IND       G_ST INDEX_TYPE                  STATUS     COUNT(*)
---------- ---------- --------------------------- -------- ----------
         0          0 LOB                         N/A               1
         0          0 LOB                         VALID           572
         0          0 FUNCTION-BASED NORMAL       VALID            17
         0          0 FUNCTION-BASED DOMAIN       VALID             1
         0          0 IOT - TOP                   VALID           115
         0          0 CLUSTER                     VALID            10
         0          0 NORMAL                      VALID          4557
         0          0 NORMAL                      N/A              56
         0          0 DOMAIN                      VALID             1
         0          0 BITMAP                      VALID             8
         0          0 BITMAP                      N/A               7

Grouping_id函数：
比grouping还强点，可以接收多个列，这几个列都不为空时，返回0，只要有一个为空，则返回1，如果都为空，则返回3。
下面这条查询把grouping_id牛刀杀鸡，当做grouping用了，可以看到结果跟用grouping是完全一样。

SQL> select grouping_id(index_type) g_ind, grouping_id(status) g_st, index_type, status, count(*)
  2  from t group by rollup(index_type, status) order by 1, 2;

     G_IND       G_ST INDEX_TYPE                  STATUS     COUNT(*)
---------- ---------- --------------------------- -------- ----------
         0          0 LOB                         N/A               1
         0          0 LOB                         VALID           572
         0          0 FUNCTION-BASED NORMAL       VALID            17
         0          0 FUNCTION-BASED DOMAIN       VALID             1
         0          0 IOT - TOP                   VALID           115

下面这条查询才体现了grouping_id的作用。

SQL> select grouping_id(index_type, status) g_st, index_type, status, count(*)
  2  from t group by rollup(index_type, status) order by 1, 2;

      G_ST INDEX_TYPE                  STATUS     COUNT(*)
---------- --------------------------- -------- ----------
         0 BITMAP                      VALID             8
         0 BITMAP                      N/A               7
         0 CLUSTER                     VALID            10
         0 DOMAIN                      VALID             1
         0 FUNCTION-BASED DOMAIN       VALID             1
         0 FUNCTION-BASED NORMAL       VALID            17
         0 IOT - TOP                   VALID           115
         0 LOB                         N/A               1
         0 LOB                         VALID           572
         0 NORMAL                      N/A              56
         0 NORMAL                      VALID          4557
         1 BITMAP                                       15
         1 CLUSTER                                      10
         1 DOMAIN                                        1
         1 FUNCTION-BASED DOMAIN                         1
         1 FUNCTION-BASED NORMAL                        17
         1 IOT - TOP                                   115
         1 LOB                                         573
         1 NORMAL                                     4613
         3                                            5345

20 rows selected

Join 另一种写法

where a.t_id = b.t_id(+) + 号在右边。代表的是左连接，+ 号在左边，代表提是右连接

 left join on左外连接
        
         select a.t_name, b.salary
           from test1 a
         left join test2 b
              on a.t_id = b.t_id
 
       与
       select a.t_name, b.salary
            from test1 a, test2 b
          where a.t_id = b.t_id(+)相同

2、实例

样例表：

SQL> select grade,id,num from a;
GRADE ID NUM
---------- ---------- ----------
a 1 1
a 2 2
b 3 4
b 4 4

对grade字段进行rollup:

SQL> select grade,sum（num） from a group by rollup（grade）；

GRADE SUM（NUM）
---------- ----------
a 3
b 8
11

同时对grade和id字段进行rollup

SQL> SELECT decode（grouping_id（grade,ID），2,'小计',3,'合计',grade） grade,
 decode（grouping_id（grade,ID），1,'小计',3,'合计',ID） ID,
 SUM（num）
 FROM a GROUP BY ROLLUP（grade,ID）


GRADE ID SUM（NUM）
---------- ---------- ----------
a 1 1
a 2 2
a 小计 3

b 3 4
b 4 4
b 小计 8

合计 合计 11

再看看先对grade分组，再对id进行rollup的情况：

SQL> SELECT grade,
 decode（GROUPING（ID），1,'合计',ID） ID,
 SUM（num）
 FROM a GROUP BY grade,rollup（ID）
 
GRADE ID SUM（NUM）
---------- ---------- ----------
a 1 1
a 2 2
a 合计 3

b 3 4
b 4 4
b 合计 8

6 rows selected

这里GROUP BY grade,rollup（ID）跟你的理解应该很相近了，而且可以看出GROUP BY grade,rollup（ID）结果跟ROLLUP（grade,ID）很类似，只是少了最后1行总合计，但是也可以就看出rollup多个字段时并不是只有1个字段起作用的
可以认为你理解的是只对第一个字段的累计，跟GROUP BY grade,rollup（ID）的结果很接近，再看rollup3个字段的情况：

SQL> select part,grade,id,num from a;

PART GRADE ID NUM
---- ---------- ---------- ----------
p1 a 1 1
p1 a 2 2

p1 b 3 3
p1 b 4 4

p2 c 5 5
p2 d 6 6

6 rows selected

SQL>
SQL> SELECT decode（grouping_id（part,grade,ID），7,'总计',part） part,
 decode（grouping_id（part,grade,ID），3,'小计',7,'总计',grade） grade,
 decode（grouping_id（part,grade,ID），1,'小计',3,'小计',7,'总计',ID） ID,
 SUM（num）
 FROM a GROUP BY ROLLUP（part,grade,ID）

PART GRADE ID SUM（NUM）
---- ---------- ---------- ----------
p1 a 1 1
p1 a 2 2
p1 a 小计 3

p1 b 3 3
p1 b 4 4
p1 b 小计 7

p1 小计 小计 10

p2 c 5 5
p2 c 小计 5

p2 d 6 6
p2 d 小计 6

p2 小计 小计 11
总计 总计 总计 21

13 rows selected

这里不光只对第一个字段做了累计，先按（part,grade,ID）分组累计，然后按（part,grade）分组累计，再按（part）分组累计，最后累计全部
再看看rollup 和 cube的区别：
对于ROLLUP（part,grade,ID），grouping_id（part,grade,ID）的值范围在（0,1,3,7）间即
part,grade,ID（作为合计时计为1）

0,0,0
0,0,1
0,1,1
1,1,1

而对于cube（part,grade,ID），grouping_id（part,grade,ID）的值范围在0-7之间即
part,grade,ID（作为合计时计为1）

0,0,0
0,0,1
0,1,0
0,1,1
1,0,0
1,0,1
1,1,0

学要无止尽

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
Oracle ROLLUP和CUBE 用法 | decode | grouping | grouping_id

1、理论Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。如果是ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，
复制链接

扫一扫