高级分组1

oracle高级分组

10.高级分组
本章目标：
对于增强的group by需要掌握：
1.使用rollup(也就是roll up累计的意思)操作产生subtotal(小计)的值。
2.使用cube操作产生cross-tabulation(列联交叉表)的值。
3.使用grouping函数标识通过rollup和cube建立的行的值。
4.使用grouping sets产生一个single result set(结果集)。
5.使用grouping_id和group_id函数。
关键字：rollup(累计，累加)，cube(交叉)，subtotal(小计),cross-tabulation(列联交叉表，交叉列表)。

背景知识：
我们知道，通过group by指定列，可以求出按照指定的列一次性统计组的信息，比如求sum，min，max，avg等。然而在实际应用中，比如数据仓库中，我们需要对数据提供多维分析，对每个维度分析得到汇总数据，提供多角度的数据分析支持。那么单纯使用group by就很难达到这种目标，当然，我们可以使用union all来将多个维度的分析数据汇总，但是在性能上来说，性能就很低了。所以oracle在8i的时候，提供了增强的group by的一系列特性。本章就专门研究一下这些特性。
注：
        对于分组的group by,rollup,cube,grouping sets后面的列不必要都出现在查询列中，但是不可出现没有分组的列，这符合SQL的语义要求。


数据准备：
  

10.1 多维汇总
        首先，有一需求：根据all_orders表和region表，计算对应区域每个月的销售额汇总。这个需求很简单，只要通过group by就可以实现，如下：
        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;
显示数据如下：

REGION                            MONTH            SUM(O.TOT_SALES)
---------------------------------------- ---------------- ---------------------------------------------------
Mid-Atlantic                             5月                       1778805
Mid-Atlantic                             8月                       1381560
Mid-Atlantic                             9月                       1178694
Southeast US                             10月                      1610523
Southeast US                             2月                       1855269
Southeast US                             3月                       1967979
Mid-Atlantic                             10月                      1530351
New England                              8月                       1642968
New England                              11月                      1384185
New England                              12月                      1599942
Southeast US                             11月                      1661598
Southeast US                             12月                      1841100
Southeast US                             6月                       1705716
New England                              7月                       1678002
Mid-Atlantic                             4月                       1623438
Mid-Atlantic                             11月                      1598667
New England                              5月                       1698855
Southeast US                             8月                       1436295
New England                              6月                       1510062
New England                              9月                       1726767
Southeast US                             4月                       1830051
Mid-Atlantic                             2月                       1286028
Mid-Atlantic                             7月                       1820742
New England                              1月                       1527645
New England                              3月                       1699449
New England                              10月                      1648944
Southeast US                             1月                       1137063
Southeast US                             5月                       1983282
Southeast US                             7月                       1670976
Mid-Atlantic                             1月                       1832091
Mid-Atlantic                             12月                      1477374
Southeast US                             9月                       1905633
Mid-Atlantic                             3月                       1911093
Mid-Atlantic                             6月                       1504455
New England                              2月                       1847238
New England                              4月                       1792866
       


上面的语句很容易实现对应区域每个月的销售额的汇总统计，但是更复杂的需求，我们可能要对跨越所有月份的每个区域单独汇总，生成小计，并且加上所有区域的汇总或者对应每个月跨越所有区域，生成小计，并且加上所有月的汇总统计，简而言之，也就是要实现多级别小计和总计的统计。实现这个需求，简单的group by就无法实现了，下面逐步研究实现的方法。
       
10.1.1 UNION
        在一个数据仓库应用中，经常需要生成多维度的汇总数据，小计和合计就是跨越多个维度的。生成小计和合计是数据仓库应用的一个核心内容。
        我们已经意识到，使用简单的group by并不能实现上述汇总查询后，再按照相关列计算小计和合计。那么我们可以使用group by来计算上面我们提到的小计和合计，然后采用union连接相关结果，获得我们想要的答案。

1.        实现对每个区域按月汇总的数据。
2.        实现对每个区域所有月份的小计。
3.        实现对所有区域所有月份的总计。
分别使用group by实现上述需求，然后union结果。当然这是一种实现方式，在Oracle8i之前，只有这种方式才能实现这种需求，后面我们将要说这种方式的缺点。
--对每个区域按月分组
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
--对每个区域的所有月分组小计，因此，月份为NULL
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
--对所有区域所有月份总计，因此月份和区域都为NULL
SELECT NULL, NULL, SUM(o.tot_sales)
FROM all_orders o JOIN region r
ON r.region_id = o.region_id;

REGION                               MONTH            SUM(O.TOT_SALES)
---------------------------------------- ---------------- ---------------------------------------------------------
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                             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                             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
Mid-Atlantic                                                      18923298
New England                                                       19756923
Southeast US                                                      20605485
                                                                  59285706       

分析这个结果可以看出，比如Mid_Atlantic的小计数据，就是上面Mid_Atlantic按月分组的明细数据的再次汇总。最后的59285706就是所有按区域和月份分组的明细的汇总，也等于上面按区域汇总的小计的和。
其实，现在可以看出，如果有一个分组函数，能够对指定列的全部分组，然后能够对指定列逐渐减少分组，直到所有的列排列完成，比如上面的区域名,月份分组。如果能实现分组统计：
区域名,月份
区域名                  //对应区域的小计
全部汇总                //所有区域所有月份的合计
那么我们的问题就能很简单地解决了，Oracle引入了RollUp，专门就是解决这个问题。

下面回到上面用Union实现这个功能上来，分析它的缺点，首先看下这个查询的执行计划(环境不同，计划可能不同)：




PLAN_TABLE_OUTPUT

-----------------------------------------------------

| Id | Operation                  | Name            |

-----------------------------------------------------

|  0 | SELECT STATEMENT           |                 |

|  1 | UNION-ALL                  |                 |

|  2 | SORT GROUP BY              |                 |

|  3 | MERGE JOIN                 |                 |

|  4 | TABLE ACCESS BY INDEX ROWID| REGION          |

|  5 | INDEX FULL SCAN            | REGION_PK       |

|* 6 | SORT JOIN                  |                 |

|  7 | TABLE ACCESS FULL          | ALL_ORDERS      |

|  8 | SORT GROUP BY              |                 |

|  9 | MERGE JOIN                 |                 |

|  10| TABLE ACCESS BY INDEX ROWID| REGION          |

|  11| INDEX FULL SCAN            | REGION_PK       |

|* 12| SORT JOIN                  |                 |

|  13| TABLE ACCESS FULL          | ALL_ORDERS      |

|  14| SORT AGGREGATE             |                 |

|  15| NESTED LOOPS               |                 |

|  16| TABLE ACCESS FULL          | ALL_ORDERS      |

|* 17| INDEX UNIQUE SCAN          | REGION_PK       |


        分析执行计划，得出oracle需要做下列动作才能完成这个查询：
Three FULL TABLE scans on all_orders
Three INDEX scan on region_pk (Primary key of table region)
Two Sort-Merge Joins
One NESTED LOOPS JOIN
Two SORT GROUP BY operations
One SORT AGGREGATE operation
One UNION ALL
    可以知道，性能是非常差的，我们的原始表all_orders和region很小，实际上一般最少有几百万条，而且我们的分组汇总列很少，如果很多，还要写更多的union,性能很不好，为了解决这个问题，请看下节ROLLUP。

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

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