oracle cube排序,【CUBE】oracle分组函数之CUBE演示及与ROLLUP的比较

CUBE与ROLLUP功能很相似，也是在统计数据时的一把好手。

对ROLLUP的统计效果请参考《【实验】【ROLLUP】oracle分组函数之ROLLUP演示》http://space.itpub.net/?uid-519536-action-viewspace-itemid-610995

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

-- The End --