ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 转 oracle中rollup、cube、grouping函数

转 oracle中rollup、cube、grouping函数

Oracle 作者：110goukao 时间：2014-03-05 16:37:30 0 删除 编辑

该文章转自：http://xls.iteye.com/blog/1096711

Oracle的group by除了基本用法以外，还有3种扩展用法，分别是rollup、cube、grouping sets。

SQL> create table t(a number,b number,c number,d number);

表已创建。

SQL> commit;

提交完成。

SQL> select * from t;

         A          B          C          D
---------- ---------- ---------- ----------
         1          2          3          4
         2          3          4          5
         3          4          5          6
         4          5          6          7

1 rollup

假设有一个表test，有A、B、C、D、E5列。

如 果使用group by rollup(A,B,C)，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。roll up的意思是“卷起”，这也可以帮助我们理解group by rollup就是对选择的列从右到左以一次少一列的方式进行grouping直到所有列都去掉后的grouping(也就是全表grouping)，对于 n个参数的rollup，有n+1次的grouping。以下2个sql的结果集是一样的：

Select A,B,C,sum(E) from test group by rollup(A,B,C)

与

Select A,B,C,sum(E) from test group by A,B,C

union all

Select A,B,null,sum(E) from test group by A,B

union all

Select A,null,null,sum(E) from test group by A

union all

Select null,null,null,sum(E) from test

example1:

SQL> select a,b from t group by rollup(a,b);-----rollup(a,b)

         A          B
---------- ----------
         1          2
         1
         2          3
         2
         3          4
         3
         4          5
         4


已选择9行。

SQL> select a,b from t group by a,b;-----group by a,b

         A          B
---------- ----------
         4          5
         2          3
         1          2
         3          4


SQL> select a,null as b from t group by a;------group by a

         A B
---------- -
         1
         2
         4
         3
SQL> select a,b from t group by a,b
  2  union all
  3  select a,null as b from t group by a; ------group by a,b union all group  by a

         A          B
---------- ----------
         4          5
         2          3
         1          2
         3          4
         1
         2
         4
         3

已选择8行。

2 cube

cube 的意思是立方，对cube的每个参数，都可以理解为取值为参与grouping和不参与grouping两个值的一个维度，然后所有维度取值组合的集合就 是grouping的集合，对于n个参数的cube，有2^n次的grouping。如果使用group by cube(A,B,C),，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作，一共是2^3=8次grouping。同rollup一样，也可以用基本的group by加上结果集的union all写出一个与group by cube结果集相同的sql：

Select A,B,C,sum(E) from test group by cube(A,B,C);

与

Select A,B,C,sum(E) from test group by A,B,C

union all

Select A,B,null,sum(E) from test group by A,B

union all

Select A,null,C,sum(E) from test group by A,C

union all

Select A,null,null,sum(E) from test group by A

union all

Select null,B,C,sum(E) from test group by B,C

union all

Select null,B,null,sum(E) from test group by B

union all

Select null,null,C,sum(E) from test group by C

union all

Select null,null,null,sum(E) from test;

example 2：

SQL> select a,b,c from t group by cube(a,b,c);

         A          B          C
---------- ---------- ----------

                               3
                               4
                               5
                               6
                    2
                    2          3
                    3
                    3          4
                    4
                    4          5

         A          B          C
---------- ---------- ----------
                    5
                    5          6
         1
         1                     3
         1          2
         1          2          3
         2
         2                     4
         2          3
         2          3          4
         3

         A          B          C
---------- ---------- ----------
         3                     5
         3          4
         3          4          5
         4
         4                     6
         4          5
         4          5          6

已选择29行。

SQL> select a,b,c from t group by a,b,c;－－－－group by a,b,c

         A          B          C
---------- ---------- ----------
         2          3          4
         3          4          5
         4          5          6
         1          2          3

SQL> select a,b,null as c from t group by a,b;－－－－group by a,b

         A          B C
---------- ---------- -
         4          5
         2          3
         1          2
         3          4

SQL> select a,null as b,c as c from t group by a,c;－－－－group by a,c

         A B          C
---------- - ----------
         2            4
         4            6
         1            3
         3            5

SQL> select null as a,b,c as c from t group by b,c;－－－－group by b,c

A          B          C
- ---------- ----------
           4          5
           2          3
           5          6
           3          4

SQL> select a,null as b,null as c from t group by a;－－－－group by a

         A B C
---------- - -
         1
         2
         4
         3

SQL> select null as a,b,null as c from t group by b;－－－－group by b

A          B C
- ---------- -
           2
           4
           5
           3

SQL> select null as a,null as b,c from t group by c;－－－－group by c

A B          C
- - ----------
             6
             4
             5
             3

SQL> select null as a,null as b,null as c from t group by null; －－－－－group by null(没有group)

A B C
- - -


SQL> select a,b,c from t group by a,b,c
  2  union all
  3  select a,b,null as c from t group by a,b
  4  union all
  5  select a,null as b,c as c from t group by a,c
  6  union all
  7  select null as a,b,c as c from t group by b,c
  8  union all
  9  select a,null as b,null as c from t group by a
 10  union all
 11  select null as a,b,null as c from t group by b
 12  union all
 13  select null as a,null as b,c from t group by c
 14  union all
 15  select null as a,null as b,null as c from t group by null
 16  ;－－－－group by a,b,c union all group by a,b union all group by a,c union all group by b,c union by group by a union all group by b union all group by c union all group by null

         A          B          C
---------- ---------- ----------
         2          3          4
         3          4          5
         4          5          6
         1          2          3
         4          5
         2          3
         1          2
         3          4
         2                     4
         4                     6
         1                     3

         A          B          C
---------- ---------- ----------
         3                     5
                    4          5
                    2          3
                    5          6
                    3          4
         1
         2
         4
         3
                    2
                    4

         A          B          C
---------- ---------- ----------
                    5
                    3
                               6
                               4
                               5
                               3


3 grouping

GROUPING函数可以接受一列，返回0或者1。如果列值为空，那么GROUPING()返回1；如果列值非空，那么返回0。当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。

SQL> select a,b,c,grouping(a) from t group by cube(a,b,c); －－－a列有值则grouping(a)＝1，没有值则grouping（a）＝0

         A          B          C GROUPING(A)
---------- ---------- ---------- -----------
                                           1
                               3           1
                               4           1
                               5           1
                               6           1
                    2                      1
                    2          3           1
                    3                      1
                    3          4           1
                    4                      1
                    4          5           1

         A          B          C GROUPING(A)
---------- ---------- ---------- -----------
                    5                      1
                    5          6           1
         1                                 0
         1                     3           0
         1          2                      0
         1          2          3           0
         2                                 0
         2                     4           0
         2          3                      0
         2          3          4           0
         3                                 0

         A          B          C GROUPING(A)
---------- ---------- ---------- -----------
         3                     5           0
         3          4                      0
         3          4          5           0
         4                                 0
         4                     6           0
         4          5                      0
         4          5          6           0

已选择29行。

GROUPING只能在使用ROLLUP或CUBE的查询中使用。但是实际只要有group by子句就可以使用，11gr2中

SQL> select a,b,c,grouping(a),grouping(b) from t group by a,b,c;

         A          B          C GROUPING(A) GROUPING(B)
---------- ---------- ---------- ----------- -----------
         2          3          4           0           0
         3          4          5           0           0
         4          5          6           0           0
         1          2          3           0           0

4 总结

rollup        (N+1个分组方案)

cube         (2^N个分组方案)

5 注意点

5.1 机制不同

在 rollup和cube的说明中分别给出了用基本group by加结果集union all给出了结果集相同的sql，但这只是为了理解的方便而给出的sql，并不说明rollup和cube与基本group by加结果集union all等价。实际上两者的内部机制是安全不一样的，前者除了写法简洁以外，运行时不需多次扫描表，效率远比后者高。

5.2 集合可运算

3种扩展用法的参数可以是源表中的某一个具体的列，也可以是若干列经过计算而形成的一个新列（比如说A+B，A||B），也可以是这两种列的一个集合（例如（A+B，C）），对于grouping set更是特殊，可以是空集合()，表示对全表进行group by。

5.3 group by 与 rollup, cube组合使用

3）Group by的基本用法以及这3种扩展用法可以组合使用，也就是说可以出现group by A,rollup(A,B)这样的用法，oracle将对出现在group by中的每种用法的grouping列集合做笛卡尔积然后对其中的每一个元素做group by。这话说起来挺绕口，举例说明吧，group by A, rollup(A,B)，基本用法的grouping集合是(A),rollup(A,B)的grouping集合是((A,B),(A),()),两个 集合的笛卡尔积集合是((A,A,B),(A,A),(A))，所以会首先对(A,A,B)做group by，然后对(A,A)做group by，最后对(A)做group by。实际上对(A,A,B)做group by和对(A,B)做group by两者是完全等价的(group by A,A,B结果和group by A,B完全一样)，同理对(A,A)做group by和对(A)做group by也是等价的。简化后的结果就是首先对(A,B)做group by，然后对(A)做group by，最后再对(A)做group by。下面给出两个等价的sql以便理解：

Select A,B,sum(E) from test1 group by A, rollup(A,B);

与

Select A,B,sum(E) from test1 group by A,B

Union all

Select A,null,sum(E) from test1 group by A

Union all

Select A,null,sum(E) from test1 group by A;

6 grouping()、grouping_id()、group_id()

6.1 grouping()

参数只有一个，而且必须为group by中出现的某一列，表示结果集的一行是否对该列做了grouping。对于对该列做了grouping的行而言，grouping()=0，反之为1；

6.2 grouping_id()

可以使用GROUPING_ID函数借助HAVING子句对记录进行过滤，将不包含小计或者总计的记录除去。GROUPING_ID()函数可以接受一列 或多列，返回GROUPING位向量的十进制值。GROUPING位向量的计算方法是将按照顺序对每一列调用GROUPING函数的结果组合起来。
GROUPING_ID用法实例
SQL> select a,b,c,grouping(a),grouping_id(a) from t group by cube(a,b,c); －－－仅对a列做grouping_id则grouping_id仅可能为1或者0，因为只有一位。

         A          B          C GROUPING(A) GROUPING_ID(A)
---------- ---------- ---------- ----------- --------------
                                           1              1
                               3           1              1
                               4           1              1
                               5           1              1
                               6           1              1
                    2                      1              1
                    2          3           1              1
                    3                      1              1
                    3          4           1              1
                    4                      1              1
                    4          5           1              1

         A          B          C GROUPING(A) GROUPING_ID(A)
---------- ---------- ---------- ----------- --------------
                    5                      1              1
                    5          6           1              1
         1                                 0              0
         1                     3           0              0
         1          2                      0              0
         1          2          3           0              0
         2                                 0              0
         2                     4           0              0
         2          3                      0              0
         2          3          4           0              0
         3                                 0              0

         A          B          C GROUPING(A) GROUPING_ID(A)
---------- ---------- ---------- ----------- --------------
         3                     5           0              0
         3          4                      0              0
         3          4          5           0              0
         4                                 0              0
         4                     6           0              0
         4          5                      0              0
         4          5          6           0              0

已选择29行。



SQL> select a,b,c,grouping(a),grouping(b),grouping_id(a,b) from t group by cube(
a,b,c); －－－对a，b两列做grouping_id则grouping_id的值有grouping（a） grouping（b）的值的二进制排列的十进制决定。二进制值的顺序组合由grouping_id里面的参数位置决定，grouping_id(a,b)和grouping_id(b,a)是不同的
比如：
grouping（a） grouping_id(b)   grouping_id(a,b)
 0                0                 0
 0                1                 1
 1                0                 2
 1                1                 3

         A          B          C GROUPING(A) GROUPING(B) GROUPING_ID(A,B)
---------- ---------- ---------- ----------- ----------- ----------------
                                           1           1                3
                               3           1           1                3
                               4           1           1                3
                               5           1           1                3
                               6           1           1                3
                    2                      1           0                2
                    2          3           1           0                2
                    3                      1           0                2
                    3          4           1           0                2
                    4                      1           0                2
                    4          5           1           0                2

         A          B          C GROUPING(A) GROUPING(B) GROUPING_ID(A,B)
---------- ---------- ---------- ----------- ----------- ----------------
                    5                      1           0                2
                    5          6           1           0                2
         1                                 0           1                1
         1                     3           0           1                1
         1          2                      0           0                0
         1          2          3           0           0                0
         2                                 0           1                1
         2                     4           0           1                1
         2          3                      0           0                0
         2          3          4           0           0                0
         3                                 0           1                1

         A          B          C GROUPING(A) GROUPING(B) GROUPING_ID(A,B)
---------- ---------- ---------- ----------- ----------- ----------------
         3                     5           0           1                1
         3          4                      0           0                0
         3          4          5           0           0                0
         4                                 0           1                1
         4                     6           0           1                1
         4          5                      0           0                0
         4          5          6           0           0                0

已选择29行。
GROUPING_ID()的用武之地

GROUPING_ID()的一个用武之地在于使用HAVING子句过滤记录。HAVING子句可以将不包含小计或总计的记录除去，这只要通过简单的检查GROUPING_ID()的返回值，看其是否大于零就可以实现。

SQL> select
  2  division_id,job_id,
  3  grouping_id(division_id,job_id) as grp_id,
  4  sum(salary)
  5  from employees2
  6  group by cube(division_id,job_id)
  7  having grouping_id(division_id,job_id) > 0
  8  order by division_id,job_id;

DIV JOB     GRP_ID SUM(SALARY)
--- --- ---------- -----------
BUS              1     1610000
OPE              1     1320000
SAL              1     4936000
SUP              1     1015000
    ENG          2      245000
    MGR          2     6246000
    PRE          2      800000
    TEC          2      115000
    WOR          2     1475000
                 3     8881000

10 rows selected.

7 示例

7.1 建表与数据

SQL> create table test(department_id number, a varchar2(20), b varchar2(20));

Table created

SQL> insert into test values(10, 'A', 'B');

1 row inserted

SQL> commit;

Commit complete

7.2 查询语句

select department_id,

       a,

       b,

       grouping(department_id),

       grouping(a),

       grouping(b)

  from test

 group by rollup(department_id, a, b)

order by 4, 5, 6;

 

select department_id,

       a,

       b,

       grouping(department_id),

       grouping(a),

       grouping(b)

  from test

 group by cube(department_id, a, b)

order by 4, 5, 6;

 

2.  cube(),rollup(),grouping sets()  是oracle提供的分析函数,看看下面的例子就知道他们的作用了.


创建测试表 并准备数据

CREATE TABLE test_sales (
location_name VARCHAR2(20),
month_flag number,
sales NUMBER,
manager VARCHAR2(20),
create_stamp DATE);
/


insert into test_sales values('SH',5,1,'Kevin',sysdate);
/
insert into test_sales values('SH',6,9,'Kevin',sysdate);
/
insert into test_sales values('SH',7,9,'Kevin',sysdate);
/
insert into test_sales values('SH',5,1,'JT',sysdate);
/
insert into test_sales values('GZ',6,9,'JT',sysdate);
/
insert into test_sales values('SH',7,8,'JT',sysdate);
/
insert into test_sales values('GZ',5,1,'Miles',sysdate);
/
insert into test_sales values('GZ',6,9,'Miles',sysdate);
/
insert into test_sales values('GZ',7,8,'Miles',sysdate);
/
insert into test_sales values('SH',5,1,'Collion',sysdate);
/
insert into test_sales values('GZ',6,9,'Collion',sysdate);
/
insert into test_sales values('GZ',7,9,'Collion',sysdate);
/

1.查询总销售额，地区销售额和各经理销售情况

solution 1 -- cube()
select manager,location_name,sum(sales) from test_sales
group by cube(manager ,location_name)

结果如下：

MANAGER LOCATION_NAME SUM(SALES)
                                                             74                     -- 各地区总销售额
                                            GZ            45                     -- GZ 地区销售额
                                            SH            29                     -- SH 地区销售额
     JT                                                     18                    -- 经理JT销售额
     JT                                   GZ             9
     JT                                   SH             9
     Kevin                                                19                    -- 经理Kevin总销售额
     Kevin                              SH            19
     Miles                                                18                    -- 经理Miles总销售额
     Miles                               GZ            18
     Collion                                              19                    -- 经理Collion总销售额
     Collion                            GZ             18
     Collion                             SH            1


solution 2 -- rollup()

select manager,location_name,sum(sales) from test_sales
group by rollup(manager ,location_name)

结果同上，只不过是汇总信息的现实顺序不同：

MANAGER LOCATION_NAME SUM(SALES)
         JT                       GZ                      9
         JT                       SH                      9
         JT                                                   18
         Kevin                  SH                      19
         Kevin                                             19
         Miles                   GZ                      18
         Miles                                              18
         Collion                GZ                     18
         Collion                SH                     1
         Collion                                           19
                                                                  74

2. 查询各经理销售情况 即指定分组

solution 1-- grouping sets()

select manager,location_name,sum(sales) from test_sales
group by grouping sets((manager ,location_name),manager)

结果如下:

MANAGER LOCATION_NAME SUM(SALES)
       JT                      GZ                       9     
       JT                      SH                       9
       JT                                                   18
        Kevin               SH                        19
        Kevin                                             19
        Miles               GZ                         18
        Miles                                             18
       Collion             GZ                        18
       Collion             SH                        1
       Collion                                           19

自己看看下面script的运行结果

select manager,location_name,sum(sales) from test_sales
group by grouping sets((manager ,location_name),manager
 ,location_name,())

 

3.  Oracle的group by聚合函数扩展cube rollup和grouping sets

 

聚合函数是oracle数据仓库的基础。为了提高距合的性能，oracle提供了group by条款的扩展。

ü         cube

ü         rollup

ü         grouping

ü         grouping sets

这几个对sql的扩展使得查询和报告都变得简单和迅速。用户通过使用这几个扩展功能，可以1，简化代码编程；2，快速有效的查询处理；3，减少客户端和网络负载。本文以实例的方式深入解析这几种扩展的具体含义和使用环境。

考虑如下关系表。

create table mytest(

subcompany_id varchar2(10),

subcompany_name varchar2(40),

branch_id varchar2(10),

branch_name varchar2(40),

region_id varchar2(10),

region_name varchar2(40),

customer_id varchar2(10),

customer_name varchar2(40),

market_id varchar2(10),

market_name varchar2(49),

sales_count numeric(10,3)

);

 

comment on table mytest is '测试表';

comment on column mytest.subcompany_id is '分公司编号';

comment on column mytest.subcompany_name is '分公司名称';

comment on column mytest.branch_id is '经营部编号';

comment on column mytest.branch_name is '经营部名称';

comment on column mytest.region_id is '片区编号';

comment on column mytest.region_name is '片区名称';

comment on column mytest.customer_id is '客户编号';

comment on column mytest.customer_name is '客户名称';

comment on column mytest.market_id is '所属市场级别';

comment on column mytest.market_name is '市场级别名称';

comment on column mytest.sales_count is '销售数量';

insert into mytest(subcompany_id,subcompany_name,branch_id,branch_name,region_id,region_name,customer_id,customer_name,market_id,market_name,sales_count)

values('01','分公司1','0101','经营部1','010101','片区1','01010101','客户1','01','片区1',1);

insert into mytest(subcompany_id,subcompany_name,branch_id,branch_name,region_id,region_name,customer_id,customer_name,market_id,market_name,sales_count)

values('01','分公司1','0101','经营部1','010101','片区1','01010102','客户2','02','片区2',1);

insert into mytest(subcompany_id,subcompany_name,branch_id,branch_name,region_id,region_name,customer_id,customer_name,market_id,market_name,sales_count)

values('01','分公司1','0101','经营部1','010102','片区2','01010201','客户1','01','片区1',1);

insert into mytest(subcompany_id,subcompany_name,branch_id,branch_name,region_id,region_name,customer_id,customer_name,market_id,market_name,sales_count)

values('01','分公司1','0101','经营部1','010102','片区2','01010202','客户2','02','片区2',1);

insert into mytest(subcompany_id,subcompany_name,branch_id,branch_name,region_id,region_name,customer_id,customer_name,market_id,market_name,sales_count)

values('01','分公司1','0102','经营部1','010201','片区1','01020101','客户1','01','片区1',1);

insert into mytest(subcompany_id,subcompany_name,branch_id,branch_name,region_id,region_name,customer_id,customer_name,market_id,market_name,sales_count)

values('01','分公司1','0102','经营部1','010202','片区2','01020202','客户2','02','片区2',1);

 

rollup

rollup的行为非常直接，它根据grouping list的rollup条款创建合计：

首先，它计算grouping条款的标准聚合。

然后，它按照grouping list列从右到左进行更高层的聚合。

最后，创建n＋1层的总计。

例如： group by rollup(A,B,C),则oracle最后得到的聚合结果为(A,B,C), (A,B), (A),()

Rollup对group by 的扩展比较简单，但非常高效，对一个查询增加的开销非常少。

考虑如下查询。

select subcompany_name,branch_name,region_name,customer_name,sum(sales_count)
from mytest
group by rollup(subcompany_name,branch_name,region_name,customer_name);

分公司1 经营部1 片区1     客户1     3  

分公司1 经营部1 片区1     客户2     2

分公司1 经营部1 片区1            5

分公司1 经营部1 片区2     客户1     2

分公司1 经营部1 片区2     客户2     3

分公司1 经营部1 片区2            5

分公司1 经营部1               10

分公司1 经营部2 片区1     客户1     1

分公司1 经营部2 片区1            1

分公司1 经营部2 片区2     客户2     1

分公司1 经营部2 片区2            1

分公司1 经营部2               2

分公司1                       12

                      12

上面的查询返回如下结果行：

1， 基于subcompany_name,branch_name,region_name,customer_name的聚合。

2， 基于subcompany_name,branch_name,region_name的聚合。

3， 基于subcompany_name,branch_name的聚合。

4， 基于subcompany_name的聚合。

5， 总计

用户还可以使用rollup包含有限的几个小计，语法如下：

Group by expr1,rollup(expr2,expr3)这种情况下，group by条款创建2＋1层小计。层次为（expr1，expr2，expr3）（expr1，expr2）（expr1）

cube

    Cube进行grouping 列规定的grouping，创建所有可能的聚合，例如规定了cube（customer_name,market_name）那么返回的结果将是2的2次 方个。分别为（customer_name,market_name）(market_name)(customer_name)()

    Cube扩展在计算交叉报表时非常有用，当然我们可以使用select union all替代rollup或cube，但这需要很多的select union all语句。这样做不仅不够高效，而且加长了sql语句，不方便维护和扩充。

考虑如下查询：

select subcompany_name,branch_name,region_name,
customer_name,market_name,sum(sales_count)
from mytest
group by subcompany_name,branch_name,region_name,cube(customer_name,market_name) ;

分公司1 经营部1 片区1                   5

分公司1 经营部1 片区1            片区1     3

分公司1 经营部1 片区1            片区2     2

分公司1 经营部1 片区1     客户1            3

分公司1 经营部1 片区1     客户1     片区1     3

分公司1 经营部1 片区1     客户2            2

分公司1 经营部1 片区1     客户2     片区2     2

分公司1 经营部1 片区2                   5

分公司1 经营部1 片区2            片区1     2

分公司1 经营部1 片区2            片区2     3

分公司1 经营部1 片区2     客户1            2

分公司1 经营部1 片区2     客户1     片区1     2

分公司1 经营部1 片区2     客户2            3

分公司1 经营部1 片区2     客户2     片区2     3

分公司1 经营部2 片区1                   1

分公司1 经营部2 片区1            片区1     1

分公司1 经营部2 片区1     客户1            1

分公司1 经营部2 片区1     客户1     片区1     1

分公司1 经营部2 片区2                   1

分公司1 经营部2 片区2            片区2     1

分公司1 经营部2 片区2     客户2            1

分公司1 经营部2 片区2     客户2     片区2     1

 

 

联合使用rollup和cube来解决特殊查询需求

rollup 和cube带来的一个问题是，在返会的结果中如何能准确区分出那些是小计，哪些是汇总数据呢。这点可以使用grouping和grouping_id函数解决。

另外，我们还可以通过having过虑掉我们不希望在结果中出现的数据。

考虑查询：给出所有机构的小计，并在此基础上给出各市场级别的小计。

方案1：

使用cube计算所有的结果，然后用having过虑得出符合条件的结果集合。

select
grouping(subcompany_name),
grouping(branch_name),
grouping(region_name),
grouping(customer_name),
grouping(market_name),
subcompany_name,branch_name,region_name,
customer_name,market_name,sum(sales_count)
from mytest
group by cube(subcompany_name,branch_name,region_name,customer_name,market_name)
having(grouping(subcompany_name) <= grouping(branch_name) and
grouping(branch_name) <= grouping(region_name) and
grouping(region_name) <= grouping(customer_name));

这种方案的缺点在于，首先使用cube计算所有可能的汇总结果需要花费相当长的时间；其次由于结果给出了所有可能的汇总，而我们需要的只是其中很小一部分，这种情况下使用having过虑结果集也是一件很麻烦的事情。

方案2

使用嵌套查询，先得出rollup的结果，然后再利用现有结果跟market进行cube的group by计算。


select subcompany_name,branch_name,region_name,
customer_name,market_name,sum(sales_count)
from (
select
subcompany_name,branch_name,region_name,
customer_name,market_name,sum(sales_count) sales_count
from mytest
group by rollup(subcompany_name,branch_name,region_name,customer_name) ,market_name
)
group by subcompany_name,branch_name,region_name,customer_name,cube(market_name)
这种方式多执行了一次查询，代码长度增加，可读性也不够强。另外还要主意过虑掉在里层查询中已经汇总的结果。

   方案3

    联合rollup和cube。

  
select
subcompany_name,branch_name,region_name,
customer_name,market_name,sum(sales_count) sales_count
from mytest
group by rollup(subcompany_name,branch_name,region_name,customer_name) , cube(market_name)
order by grouping(subcompany_name),
grouping(branch_name),
grouping(region_name),
grouping(customer_name),
grouping(market_name);

grouping sets

grouping sets提供了指定汇总集合条件的功能。例如在上面的查询中，我们可以通过为select group by语句制定汇总条件（）

select
subcompany_name,branch_name,region_name,
customer_name,market_name,sum(sales_count) sales_count
from mytest
group by grouping sets((subcompany_name,branch_name,region_name,customer_name) ,
                       (subcompany_name,branch_name,region_name) ,
                       (subcompany_name,branch_name) ,
                       (subcompany_name) ,
                       (market_name) ,
                       (subcompany_name,branch_name,region_name,customer_name,market_name) ,
                       (subcompany_name,branch_name,region_name,market_name) ,
                       (subcompany_name,branch_name,market_name) ,
                       (subcompany_name,market_name) ,
                       () )

<!-- 正文结束 -->

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7855969/viewspace-1115200/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 没有了~
下一篇： 没有了~
请登录后发表评论 登录
全部评论
110goukao

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593