【转】ROLLUP和CUBE

ROLLUP分组函数的用法

select * from group_test

  GROUP_ID JOB        NAME           SALARY
---------- ---------- ---------- ----------
        10 Coding     Bruce            1000
        10 Programmer Clair            1000
        10 Architect  Gideon           1000
        10 Director   Hill             1000
        20 Coding     Jason            2000
        20 Programmer Joey             2000
        20 Architect  Martin           2000
        20 Director   Michael          2000
        30 Coding     Rebecca          3000
        30 Programmer Rex              3000
        30 Architect  Richard          3000
        30 Director   Sabrina          3000
        40 Coding     Samuel           4000
        40 Programmer Susy             4000
        40 Architect  Tina             4000
        40 Director   Wendy            4000

3.对group_id进行普通的roolup操作---按照小组进行分组，同时求总计
SECOOLER@ora11g> select group_id,sum(salary) from group_test group by rollup(group_id);

  GROUP_ID SUM(SALARY)
---------- -----------
        10        4000
        20        8000
        30       12000
        40       16000
                 40000

select group_id,job,sum(salary) from group_test group by rollup(group_id, job);

  GROUP_ID JOB        SUM(SALARY)
---------- ---------- -----------
        10 Coding            1000
        10 Director          1000
        10 Architect         1000
        10 Programmer        1000
        10                   4000
        20 Coding            2000
        20 Director          2000
        20 Architect         2000
        20 Programmer        2000
        20                   8000
        30 Coding            3000
        30 Director          3000
        30 Architect         3000
        30 Programmer        3000
        30                  12000
        40 Coding            4000
        40 Director          4000
        40 Architect         4000
        40 Programmer        4000
        40                  16000
                            40000

21 rows selected.

select group_id,job,grouping(GROUP_ID),grouping(JOB),sum(salary) from group_test group by rollup(group_id, job);

  GROUP_ID JOB        GROUPING(GROUP_ID) GROUPING(JOB) SUM(SALARY)
---------- ---------- ------------------ ------------- -----------
        10 Coding                      0             0        1000
        10 Director                    0             0        1000
        10 Architect                   0             0        1000
        10 Programmer                  0             0        1000
        10                             0             1        4000
        20 Coding                      0             0        2000
        20 Director                    0             0        2000
        20 Architect                   0             0        2000
        20 Programmer                  0             0        2000
        20                             0             1        8000
        30 Coding                      0             0        3000
        30 Director                    0             0        3000
        30 Architect                   0             0        3000
        30 Programmer                  0             0        3000
        30                             0             1       12000
        40 Coding                      0             0        4000
        40 Director                    0             0        4000
        40 Architect                   0             0        4000
        40 Programmer                  0             0        4000
        40                             0             1       16000
                                       1             1       40000

21 rows selected.

  看出来什么效果了么？
  有的同学还是没有看出来，小小的解释一下：
  如果显示“1”表示GROUPING函数对应的列（例如JOB字段）是由于ROLLUP函数所产生的空值对应的信息，即对此列进行汇总计算后的结果。
  如果显示“0”表示此行对应的这列参未与ROLLUP函数分组汇总活动。
  如果还是没有理解清楚，请参见Oracle官方文档中的描述内容：“Using a single column as its argument, GROUPING returns 1 when it encounters a NULL value created by a ROLLUP or CUBE operation. That is, if the NULL indicates the row is a subtotal,GROUPING returns a 1. Any other type of value, including a stored NULL, returns a 0.”

select group_id,job,grouping(GROUP_ID),grouping(JOB),sum(salary) from group_test group by cube(group_id, job) order by 1;

  GROUP_ID JOB        GROUPING(GROUP_ID) GROUPING(JOB) SUM(SALARY)
---------- ---------- ------------------ ------------- -----------
        10 Architect                   0             0        1000
        10 Coding                      0             0        1000
        10 Director                    0             0        1000
        10 Programmer                  0             0        1000
        10                             0             1        4000
        20 Architect                   0             0        2000
        20 Coding                      0             0        2000
        20 Director                    0             0        2000
        20 Programmer                  0             0        2000
        20                             0             1        8000
        30 Architect                   0             0        3000
        30 Coding                      0             0        3000
        30 Director                    0             0        3000
        30 Programmer                  0             0        3000
        30                             0             1       12000
        40 Architect                   0             0        4000
        40 Coding                      0             0        4000
        40 Director                    0             0        4000
        40 Programmer                  0             0        4000
        40                             0             1       16000
           Architect                   1             0       10000
           Coding                      1             0       10000
           Director                    1             0       10000
           Programmer                  1             0       10000
                                       1             1       40000

25 rows selected.

  解释如上结果中GROUPING函数返回值“0”和“1”的含义。
  如果显示“1”表示CUBE函数对应的列（例如JOB字段）是由于CUBE函数所产生的空值对应的信息，即对此列进行汇总计算后的结果。
  如果显示“0”表示此行对应的这列参未与ROLLUP函数分组汇总活动。
  如果还是没有理解清楚，请参见Oracle官方文档中的描述内容：“Using a single column as its argument,GROUPINGreturns 1 when it encounters aNULLvalue created by aROLLUPorCUBEoperation. That is, if theNULLindicates the row is a subtotal,GROUPINGreturns a 1. Any other type of value, including a storedNULL, returns a 0.”

3.仔细观察一下，CUBE与ROLLUP之间的细微差别
rollup(a,b)   统计列包含：(a,b)、(a)、()
rollup(a,b,c) 统计列包含：(a,b,c)、(a,b)、(a)、()
……以此类推ing……

cube(a,b)     统计列包含：(a,b)、(a)、(b)、()
cube(a,b,c)   统计列包含：(a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、(c)、()
……以此类推ing……

So，上面例子中CUBE的结果比ROLLUP多了下面关于第一列GROUP_ID的统计信息：
           Architect                   1             0       10000
           Coding                      1             0       10000
           Director                    1             0       10000

4.小结
  CUBE在ROLLUP的基础上进一步从各种维度上给出细化的统计汇总结果。
  CUBE与GROUP BY的关系可以参考Oracle官方文档中的例子，链接如下：，链接如下：《CUBE Extension to GROUP BY》http://docs.oracle.com/cd/E11882_01/server.112/e25554/aggreg.htm#DWHSG8614

Good luck.