高级分组3

10.1.4 CUBE
        CUBE(交叉列表)也是对group by运算的一种扩展，它比rollup扩展更加精细，组合类型更多，rollup是按组合的列从右到左递减分组计算，而CUBE则是对所有可能的组合情况进行分组，这样分组的情况更多，覆盖所有的可能分组，并计算所有可能的分组的小计。比如：
CUBE(C1,C2,C3……C(N))对N个列进行CUBE分组，那么可能的分组情况有：
不分组：C(n,0)
取一列分组:C(n,1)
-----
取N列分组，全分组：C(n,n)
那么运用数学上的组合公式，得出所有所有可能的组合方式有:C(n,0)+C(n,1)+….+C(n,n)=2^n种。
我们以前面的rollup组合列为例子：rollup(name,month)是计算按区域名和月份分组以及每个区域的所有月份的小计以及总计。但是使用cube(name,month)则有4种分组，比rollup多一个每个月的所有区域的小计。下面比较一下这两种分组方式：

分组公式        描述
rollup(name,month)        分组情况有：
group by name,month
group by name,null  //每个区域所有月份小计
group by null,null  //合计
cube(name,month)        分组情况有:
group by null,null  //总计
group by null,month //每个月份的所有区域小计
group by name,null //每个区域的所有月份小计
group by name,month





       
CUBE使用方式：
和rollup一样，是
select …
from …
group by cube(分组列列表)

        请看上面表格中的分组例子：
SELECT r.name region, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
SUM(o.tot_sales) sums
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
GROUP BY CUBE(r.name, o.month)


REGION                                   MONTH                  SUMS
---------------------------------------- ---------------- ---------------------------------------------------
                                                            59285706
                                         1月                 4496799
                                         2月                 4988535
                                         3月                 5578521
                                         4月                 5246355
                                         5月                 5460942
                                         6月                 4720233
                                         7月                 5169720
                                         8月                 4460823
                                         9月                 4811094
                                         10月                4789818
                                         11月                4644450
                                         12月                4918416
New England                                                 19756923
New England                              1月                 1527645
New England                              2月                 1847238
New England                              3月                 1699449
New England                              4月                 1792866
New England                              5月                 1698855
New England                              6月                 1510062
New England                              7月                 1678002
New England                              8月                 1642968
New England                              9月                 1726767
New England                              10月                1648944
New England                              11月                1384185
New England                              12月                1599942
Mid-Atlantic                                                18923298
Mid-Atlantic                             1月                 1832091
Mid-Atlantic                             2月                 1286028
Mid-Atlantic                             3月                 1911093
Mid-Atlantic                             4月                 1623438
Mid-Atlantic                             5月                 1778805
Mid-Atlantic                             6月                 1504455
Mid-Atlantic                             7月                 1820742
Mid-Atlantic                             8月                 1381560
Mid-Atlantic                             9月                 1178694
Mid-Atlantic                             10月                1530351
Mid-Atlantic                             11月                1598667
Mid-Atlantic                             12月                1477374
Southeast US                                                20605485
Southeast US                             1月                 1137063
Southeast US                             2月                 1855269
Southeast US                             3月                 1967979
Southeast US                             4月                 1830051
Southeast US                             5月                 1983282
Southeast US                             6月                 1705716
Southeast US                             7月                 1670976
Southeast US                             8月                 1436295
Southeast US                             9月                 1905633
Southeast US                             10月                1610523
Southeast US                             11月                1661598
Southeast US                             12月                1841100

        从上面结果可以看出，红色部分为group by null,null的结果，计算合计值。黄色部分为group by null,month的结果，计算每个月中所有区域的小计。绿色为group by name,null的结果，计算每个区域所有月份的小计。灰色的为group by name,month的结果。其中：
        group by name,month的所有结果的和=group by name,null的和=group by null,month的和=合计值。
        group by name,month中对应name的和=group by name,null对应的name的值。
group by name,month中对应month的和=group by null,month对应month的和。


        当然对于cube的运算，和rollup一样，也可以使用union实现，但是cube的组合方式呈级数增长，则union也会增长，而且性能不好，访问表多次，无cube的优化，语句没有cube简单。上例我们可以使用对应的4个group by 然后union all获得结果，但是结果的顺序不能保证。
SELECT NULL region, NULL month, SUM(o.tot_sales)
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
UNION ALL
SELECT NULL, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month, SUM(o.tot_sales)
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
GROUP BY o.month
UNION ALL
SELECT r.name region, NULL, SUM(o.tot_sales)
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
GROUP BY r.name
UNION ALL
SELECT r.name region, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
SUM(o.tot_sales)
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
GROUP BY r.name, o.month;

        看下union的执行计划：

对all_orders和region都访问了4次，效率不高。下面看下cube的执行计划：

只需要访问region和all_orders一次，而且有专门的GENERATE CUBE计算，提高效率，保证执行结果的有序性。
        实际上，有上面对cube的分析可以得出，那个cube语句实际上等价于下列rollup语句：
SELECT r.name region, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
SUM(o.tot_sales)
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
GROUP BY rollup(r.name, o.month)
union
SELECT r.name region, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
SUM(o.tot_sales)
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
GROUP BY rollup( o.month,r.name);
        不可以使用union all,因为有重复数据。


        对于CUBE来说，列的名字只要一样，那么顺序无所谓，结果都是一样的，因为cube是各种可能情况的组合,只不过统计的结果顺序不同而已。但是对于rollup来说，列的顺序不同，则结果不同，详细见rollup。

        对本章的cube例子，改写cube的顺序，如下：
SELECT r.name region, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
SUM(o.tot_sales) sums
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
GROUP BY CUBE(o.month, r.name);
REGION                                   MONTH                  SUMS
---------------------------------------- ---------------- ----------------------------------------------------
                                                            59285706
New England                                                 19756923
Mid-Atlantic                                                18923298
Southeast US                                                20605485
                                         1月                 4496799
New England                              1月                 1527645
Mid-Atlantic                             1月                 1832091
Southeast US                             1月                 1137063
                                         2月                 4988535
New England                              2月                 1847238
Mid-Atlantic                             2月                 1286028
Southeast US                             2月                 1855269
                                         3月                 5578521
New England                              3月                 1699449
Mid-Atlantic                             3月                 1911093
Southeast US                             3月                 1967979
                                         4月                 5246355
New England                              4月                 1792866
Mid-Atlantic                             4月                 1623438
Southeast US                             4月                 1830051
                                         5月                 5460942
New England                              5月                 1698855
Mid-Atlantic                             5月                 1778805
Southeast US                             5月                 1983282
                                         6月                 4720233
New England                              6月                 1510062
Mid-Atlantic                             6月                 1504455
Southeast US                             6月                 1705716
                                         7月                 5169720
New England                              7月                 1678002
Mid-Atlantic                             7月                 1820742
Southeast US                             7月                 1670976
                                         8月                 4460823
New England                              8月                 1642968
Mid-Atlantic                             8月                 1381560
Southeast US                             8月                 1436295
                                         9月                 4811094
New England                              9月                 1726767
Mid-Atlantic                             9月                 1178694
Southeast US                             9月                 1905633
                                         10月                4789818
New England                              10月                1648944
Mid-Atlantic                             10月                1530351
Southeast US                             10月                1610523
                                         11月                4644450
New England                              11月                1384185
Mid-Atlantic                             11月                1598667
Southeast US                             11月                1661598
                                         12月                4918416
New England                              12月                1599942
Mid-Atlantic                             12月                1477374
Southeast US                             12月                1841100
       
我们可以看出，与cube(r.name,o.month)结果一样，只不过顺序不一样。cube(o.month,r.name)的顺序是：
        group by null,null
group by null,r.name
group by o.month,null
group by o.month,r.name
其中最后两个分组是先小计再分组。

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

转载于:http://blog.itpub.net/24984814/viewspace-704463/