CUBE与ROLLUP比较

ROLLUP与CUBE详解

最新推荐文章于 2025-08-12 19:51:03 发布

转载最新推荐文章于 2025-08-12 19:51:03 发布 · 136 阅读

本文详细对比了SQL中的ROLLUP和CUBE功能，并通过具体示例展示了它们在统计数据汇总时的区别。ROLLUP用于生成一系列子集的汇总，而CUBE则提供了所有可能的子集组合。

1.先显示一下ROLLUP的效果
sec@ora10g> 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

16 rows selected.

sec@ora10g> 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.

2.再看一下CUBE的效果
sec@ora10g> 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.

3.仔细观察一下，这儿两个的细微差别是什么？
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

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

转载于:http://blog.itpub.net/165278/viewspace-611086/