oracle 高级分组

    1. 10.高级分组
    2. 本章目标:
    3. 对于增强的group by需要掌握:
    4. 1.使用rollup(也就是roll up累计的意思)操作产生subtotal(小计)的值。
    5. 2.使用cube操作产生cross-tabulation(列联交叉表)的值。
    6. 3.使用grouping函数标识通过rollup和cube建立的行的值。
    7. 4.使用grouping sets产生一个single result set(结果集)。
    8. 5.使用grouping_id和group_id函数。
    9. 关键字:rollup(累计,累加),cube(交叉),subtotal(小计),cross-tabulation(列联交叉表,交叉列表)。
    10. 背景知识:
    11. 我 们知道,通过group by指定列,可以求出按照指定的列一次性统计组的信息,比如求sum,min,max,avg等。然而在实际应用中,比如数据仓 库中,我们需要对数据提供多维分析,对每个维度分析得到汇总数据,提供多角度的数据分析支持。那么单纯使用group by就很难达到这种目标,当然,我 们可以使用union all来将多个维度的分析数据汇总,但是在性能上来说,性能就很低了。所以oracle在8i的时候,提供了增强的 group by的一系列特性。本章就专门研究一下这些特性。
    12. 注:
    13. 对于分组的group by,rollup,cube,grouping sets后面的列不必要都出现在查询列中,但是不可出现没有分组的列,这符合SQL的语义要求。
    14. 数据准备:
    15. 10.1 多维汇总
    16. 首先,有一需求:根据all_orders表和region表,计算对应区域每个月的销售额汇总。这个需求很简单,只要通过group by就可以实现,如下:
    17. SELECT r.name region,
    18. TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month, SUM(o.tot_sales)
    19. FROM all_orders o JOIN region r
    20. ON r.region_id = o.region_id
    21. GROUP BY r.name, o.month;
    22. 显示数据如下:
    23. REGION                            MONTH            SUM(O.TOT_SALES)
    24. ---------------------------------------- ---------------- ---------------------------------------------------
    25. Mid-Atlantic                             5月                       1778805
    26. Mid-Atlantic                             8月                       1381560
    27. Mid-Atlantic                             9月                       1178694
    28. Southeast US                             10月                      1610523
    29. Southeast US                             2月                       1855269
    30. Southeast US                             3月                       1967979
    31. Mid-Atlantic                             10月                      1530351
    32. New England                              8月                       1642968
    33. New England                              11月                      1384185
    34. New England                              12月                      1599942
    35. Southeast US                             11月                      1661598
    36. Southeast US                             12月                      1841100
    37. Southeast US                             6月                       1705716
    38. New England                              7月                       1678002
    39. Mid-Atlantic                             4月                       1623438
    40. Mid-Atlantic                             11月                      1598667
    41. New England                              5月                       1698855
    42. Southeast US                             8月                       1436295
    43. New England                              6月                       1510062
    44. New England                              9月                       1726767
    45. Southeast US                             4月                       1830051
    46. Mid-Atlantic                             2月                       1286028
    47. Mid-Atlantic                             7月                       1820742
    48. New England                              1月                       1527645
    49. New England                              3月                       1699449
    50. New England                              10月                      1648944
    51. Southeast US                             1月                       1137063
    52. Southeast US                             5月                       1983282
    53. Southeast US                             7月                       1670976
    54. Mid-Atlantic                             1月                       1832091
    55. Mid-Atlantic                             12月                      1477374
    56. Southeast US                             9月                       1905633
    57. Mid-Atlantic                             3月                       1911093
    58. Mid-Atlantic                             6月                       1504455
    59. New England                              2月                       1847238
    60. New England                              4月                       1792866
    61. 上 面的语句很容易实现对应区域每个月的销售额的汇总统计,但是更复杂的需求,我们可能要对跨越所有月份的每个区域单独汇总,生成小计,并且加上所有区域的汇 总或者对应每个月跨越所有区域,生成小计,并且加上所有月的汇总统计,简而言之,也就是要实现多级别小计和总计的统计。实现这个需求,简单的 group by就无法实现了,下面逐步研究实现的方法。
    62. 10.1.1 UNION
    63. 在一个数据仓库应用中,经常需要生成多维度的汇总数据,小计和合计就是跨越多个维度的。生成小计和合计是数据仓库应用的一个核心内容。
    64. 我们已经意识到,使用简单的group by并不能实现上述汇总查询后,再按照相关列计算小计和合计。那么我们可以使用group by来计算上面我们提到的小计和合计,然后采用union连接相关结果,获得我们想要的答案。
    65. 1.        实现对每个区域按月汇总的数据。
    66. 2.        实现对每个区域所有月份的小计。
    67. 3.        实现对所有区域所有月份的总计。
    68. 分别使用group by实现上述需求,然后union结果。当然这是一种实现方式,在Oracle8i之前,只有这种方式才能实现这种需求,后面我们将要说这种方式的缺点。
    69. --对每个区域按月分组
    70. SELECT r.name region,
    71. TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month, SUM(o.tot_sales)
    72. FROM all_orders o JOIN region r
    73. ON r.region_id = o.region_id
    74. GROUP BY r.name, o.month
    75. UNION ALL
    76. --对每个区域的所有月分组小计,因此,月份为NULL
    77. SELECT r.name region, NULL, SUM(o.tot_sales)
    78. FROM all_orders o JOIN region r
    79. ON r.region_id = o.region_id
    80. GROUP BY r.name
    81. UNION ALL
    82. --对所有区域所有月份总计,因此月份和区域都为NULL
    83. SELECT NULL, NULL, SUM(o.tot_sales)
    84. FROM all_orders o JOIN region r
    85. ON r.region_id = o.region_id;
    86. REGION                               MONTH            SUM(O.TOT_SALES)
    87. ---------------------------------------- ---------------- ---------------------------------------------------------
    88. New England                              1月                       1527645
    89. New England                              2月                       1847238
    90. New England                              3月                       1699449
    91. New England                              4月                       1792866
    92. New England                              5月                       1698855
    93. New England                              6月                       1510062
    94. New England                              7月                       1678002
    95. New England                              8月                       1642968
    96. New England                              9月                       1726767
    97. New England                              10月                      1648944
    98. New England                              11月                      1384185
    99. New England                              12月                      1599942
    100. Mid-Atlantic                             1月                       1832091
    101. Mid-Atlantic                             2月                       1286028
    102. Mid-Atlantic                             3月                       1911093
    103. Mid-Atlantic                             4月                       1623438
    104. Mid-Atlantic                             5月                       1778805
    105. Mid-Atlantic                             6月                       1504455
    106. Mid-Atlantic                             7月                       1820742
    107. Mid-Atlantic                             8月                       1381560
    108. Mid-Atlantic                             9月                       1178694
    109. Mid-Atlantic                             10月                      1530351
    110. Mid-Atlantic                             11月                      1598667
    111. Mid-Atlantic                             12月                      1477374
    112. Southeast US                             1月                       1137063
    113. Southeast US                             2月                       1855269
    114. Southeast US                             3月                       1967979
    115. Southeast US                             4月                       1830051
    116. Southeast US                             5月                       1983282
    117. Southeast US                             6月                       1705716
    118. Southeast US                             7月                       1670976
    119. Southeast US                             8月                       1436295
    120. Southeast US                             9月                       1905633
    121. Southeast US                             10月                      1610523
    122. Southeast US                             11月                      1661598
    123. Southeast US                             12月                      1841100
    124. Mid-Atlantic                                                      18923298
    125. New England                                                       19756923
    126. Southeast US                                                      20605485
    127. 59285706
    128. 分析这个结果可以看出,比如Mid_Atlantic的小计数据,就是上面Mid_Atlantic按月分组的明细数据的再次汇总。最后的59285706就是所有按区域和月份分组的明细的汇总,也等于上面按区域汇总的小计的和。
    129. 其实,现在可以看出,如果有一个分组函数,能够对指定列的全部分组,然后能够对指定列逐渐减少分组,直到所有的列排列完成,比如上面的区域名,月份分组。如果能实现分组统计:
    130. 区域名,月份
    131. 区域名                  //对应区域的小计
    132. 全部汇总                //所有区域所有月份的合计
    133. 那么我们的问题就能很简单地解决了,Oracle引入了RollUp,专门就是解决这个问题。
    134. 下面回到上面用Union实现这个功能上来,分析它的缺点,首先看下这个查询的执行计划(环境不同,计划可能不同):
    135. PLAN_TABLE_OUTPUT
    136. -----------------------------------------------------
    137. | Id | Operation                  | Name            |
    138. -----------------------------------------------------
    139. |  0 | SELECT STATEMENT           |                 |
    140. |  1 | UNION-ALL                  |                 |
    141. |  2 | SORT GROUP BY              |                 |
    142. |  3 | MERGE JOIN                 |                 |
    143. |  4 | TABLE ACCESS BY INDEX ROWID| REGION          |
    144. |  5 | INDEX FULL SCAN            | REGION_PK       |
    145. |* 6 | SORT JOIN                  |                 |
    146. |  7 | TABLE ACCESS FULL          | ALL_ORDERS      |
    147. |  8 | SORT GROUP BY              |                 |
    148. |  9 | MERGE JOIN                 |                 |
    149. |  10| TABLE ACCESS BY INDEX ROWID| REGION          |
    150. |  11| INDEX FULL SCAN            | REGION_PK       |
    151. |* 12| SORT JOIN                  |                 |
    152. |  13| TABLE ACCESS FULL          | ALL_ORDERS      |
    153. |  14| SORT AGGREGATE             |                 |
    154. |  15| NESTED LOOPS               |                 |
    155. |  16| TABLE ACCESS FULL          | ALL_ORDERS      |
    156. |* 17| INDEX UNIQUE SCAN          | REGION_PK       |
    157. 分析执行计划,得出oracle需要做下列动作才能完成这个查询:
    158. Three FULL TABLE scans on all_orders
    159. Three INDEX scan on region_pk (Primary key of table region)
    160. Two Sort-Merge Joins
    161. One NESTED LOOPS JOIN
    162. Two SORT GROUP BY operations
    163. One SORT AGGREGATE operation
    164. One UNION ALL
    165. 可以知道,性能是非常差的,我们的原始表all_orders和region很小,实际上一般最少有几百万条,而且我们的分组汇总列很少,如果很多,还要写更多的union,性能很不好,为了解决这个问题,请看下节ROLLUP。
    166. 10.1.2 ROLLUP
    167. 从Oracle8i开始,oracle提供了很多在一个查询中生成多级别汇总数据的特性,第10章我们研究的是对group by的扩展,关于扩展内容和版本对应关系如下表:
    168. 功能        版本
    169. ROLLUP        oracle8i
    170. CUBE        oracle8i
    171. GROUPING SETS        oracle9i
    172. 本节研究rollup,对cube和grouping sets稍后介绍。ROLLUP是对group by的扩展,因此,它只能出现在group by子句中,依赖于分组的列,对每个分组会生成汇总数据,如下:
    173. SELECT ….
    174. FROM ….
    175. GROUP BY ROLLUP(C1,C2,C3….C(n-1),C(n));
    176. 总共会进行n+1个分组。
    177. 那么实际上有n+1个group by的union all结果。
    178. 第1个分组:全分组。C1,C2,C3….C(n-1),C(n)
    179. 第2个分组:C1,C2,C3….C(n-1);//这个分组实际上就是对前面前n-1列分组的小计.
    180. ----然后逐渐递减分组列
    181. 第n个分组:C1。对上一个分组的小计。
    182. 第n+1个分组。不分组全量汇总。相当于合计。也是对group by C1的小计。相当于group by null。
    183. 有了rollup,对10.1.1实现的union多级别汇总就可以使用rollup实现,如下:
    184. SELECT r.name region,
    185. TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month, SUM(o.tot_sales)
    186. FROM all_orders o JOIN region r
    187. ON r.region_id = o.region_id
    188. GROUP BY ROLLUP (r.name, o.month);
    189. REGION                                   MONTH            SUM(O.TOT_SALES)
    190. ---------------------------------------- ---------------- ----------------
    191. New England                              1月                       1527645
    192. New England                              2月                       1847238
    193. New England                              3月                       1699449
    194. New England                              4月                       1792866
    195. New England                              5月                       1698855
    196. New England                              6月                       1510062
    197. New England                              7月                       1678002
    198. New England                              8月                       1642968
    199. New England                              9月                       1726767
    200. New England                              10月                      1648944
    201. New England                              11月                      1384185
    202. New England                              12月                      1599942
    203. New England                                                       19756923
    204. Mid-Atlantic                             1月                       1832091
    205. Mid-Atlantic                             2月                       1286028
    206. Mid-Atlantic                             3月                       1911093
    207. Mid-Atlantic                             4月                       1623438
    208. Mid-Atlantic                             5月                       1778805
    209. Mid-Atlantic                             6月                       1504455
    210. Mid-Atlantic                             7月                       1820742
    211. Mid-Atlantic                             8月                       1381560
    212. Mid-Atlantic                             9月                       1178694
    213. Mid-Atlantic                             10月                      1530351
    214. Mid-Atlantic                             11月                      1598667
    215. Mid-Atlantic                             12月                      1477374
    216. Mid-Atlantic                                                      18923298
    217. Southeast US                             1月                       1137063
    218. Southeast US                             2月                       1855269
    219. Southeast US                             3月                       1967979
    220. Southeast US                             4月                       1830051
    221. Southeast US                             5月                       1983282
    222. Southeast US                             6月                       1705716
    223. Southeast US                             7月                       1670976
    224. Southeast US                             8月                       1436295
    225. Southeast US                             9月                       1905633
    226. Southeast US                             10月                      1610523
    227. Southeast US                             11月                      1661598
    228. Southeast US                             12月                      1841100
    229. Southeast US                                                      20605485
    230. 59285706
    231. 从 上面的结果可以看出,rollup和group by联合一起使用,达到了按group by列顺序分组,并且实现小计和合计的功能。黄色部分就是对上面 组的小计,最后红色的就是合计。而且,rollup这种分组还是有序的,先全部分组,然后对每个分组小计,最后合计,使处理结果更清楚。 union all就很难实现这种效果了。而且最重要的是性能比union all好,通过上面查询的执行计划可以看出:
    232. rollup仅仅一个索引扫描region,一个全表扫描all_orders,然后通过nested loop,最后sort group by rollup就能得到结果。比union all多次扫描效率高很多。
    233. rollup改变列的顺序,可以轻松改变统计结果,上面的查询小计是对每个区域的所有月小计,我们可以改变列的顺序达到对每个月份的所有区域小计,如下:
    234. SELECT r.name region,
    235. TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month, SUM(o.tot_sales)
    236. FROM all_orders o JOIN region r
    237. ON r.region_id = o.region_id
    238. GROUP BY ROLLUP ( o.month,r.name);
    239. REGION                                MONTH            SUM(O.TOT_SALES)
    240. ---------------------------------------- ---------------- ----------------
    241. New England                             1月                       1527645
    242. Mid-Atlantic                             1月                       1832091
    243. Southeast US                             1月                       1137063
    244. 1月                       4496799
    245. New England                             2月                       1847238
    246. Mid-Atlantic                              2月                       1286028
    247. Southeast US                              2月                       1855269
    248. 2月                       4988535
    249. New England                              3月                       1699449
    250. Mid-Atlantic                             3月                       1911093
    251. Southeast US                             3月                       1967979
    252. 3月                       5578521
    253. New England                              4月                       1792866
    254. Mid-Atlantic                             4月                       1623438
    255. Southeast US                             4月                       1830051
    256. 4月                       5246355
    257. New England                              5月                       1698855
    258. Mid-Atlantic                             5月                       1778805
    259. Southeast US                             5月                       1983282
    260. 5月                       5460942
    261. New England                              6月                       1510062
    262. Mid-Atlantic                             6月                       1504455
    263. Southeast US                             6月                       1705716
    264. 6月                       4720233
    265. New England                              7月                       1678002
    266. Mid-Atlantic                             7月                       1820742
    267. Southeast US                             7月                       1670976
    268. 7月                       5169720
    269. New England                              8月                       1642968
    270. Mid-Atlantic                             8月                       1381560
    271. Southeast US                             8月                       1436295
    272. 8月                       4460823
    273. New England                              9月                       1726767
    274. Mid-Atlantic                             9月                       1178694
    275. Southeast US                             9月                       1905633
    276. 9月                       4811094
    277. New England                              10月                      1648944
    278. Mid-Atlantic                             10月                      1530351
    279. Southeast US                             10月                      1610523
    280. 10月                      4789818
    281. New England                              11月                      1384185
    282. Mid-Atlantic                             11月                      1598667
    283. Southeast US                             11月                      1661598
    284. 11月                      4644450
    285. New England                              12月                      1599942
    286. Mid-Atlantic                             12月                      1477374
    287. Southeast US                             12月                      1841100
    288. 12月                      4918416
    289. 59285706
    290. 可以看出,达到了按月小计的功能,当然最后红色的合计值和按区域小计一致。
    291. 注意:
    292. rollup中列的顺序不同,则统计的结果不同。因为它是按列从右递减分组的。
    293. 比如,我们需要统计第1个季度,并且年月汇总所有区域的数据,如下:
    294. SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
    295. r.name region, SUM(o.tot_sales) sums
    296. FROM all_orders o JOIN region r
    297. ON r.region_id = o.region_id
    298. WHERE o.month BETWEEN 1 AND 3
    299. GROUP BY ROLLUP (o.year, o.month, r.name);
    300. YEAR MONTH         REGION                                         SUMS
    301. --------- ---------------- ---------------------------------------- ---------------------------------------------
    302. 2000 1月              New England                                 1018430
    303. 2000 1月              Mid-Atlantic                                1221394
    304. 2000 1月              Southeast US                                 758042
    305. 2000 1月                                                          2997866
    306. 2000 2月              New England                                 1231492
    307. 2000 2月              Mid-Atlantic                                 857352
    308. 2000 2月              Southeast US                                1236846
    309. 2000 2月                                                          3325690
    310. 2000 3月              New England                                 1132966
    311. 2000 3月              Mid-Atlantic                                1274062
    312. 2000 3月              Southeast US                                1311986
    313. 2000 3月                                                          3719014
    314. 2000                                                             10042570
    315. 2001 1月              New England                                  509215
    316. 2001 1月              Mid-Atlantic                                 610697
    317. 2001 1月              Southeast US                                 379021
    318. 2001 1月                                                          1498933
    319. 2001 2月              New England                                  615746
    320. 2001 2月              Mid-Atlantic                                 428676
    321. 2001 2月              Southeast US                                 618423
    322. 2001 2月                                                          1662845
    323. 2001 3月              New England                                  566483
    324. 2001 3月              Mid-Atlantic                                 637031
    325. 2001 3月              Southeast US                                 655993
    326. 2001 3月                                                          1859507
    327. 2001                                                              5021285
    328. 15063855
    329. 可以看出,只要在rollup中增加或改变相关列的顺序,就能达到我们想要的小计和合计功能。
    330. 10.1.3部分ROLLUP
    331. 以上使用的rollup是完全的rollup查询,比如有n列,那么会生成n-1个小计,然后n-2个小计对n-1个,直到汇总合计。如果在实际查询中,有的小计或合计我们不需要,那么久要使用局部rollup,局部rollup就是将不需要小计(合计)的列放在group by中,而不是放在rollup中。
    332. 请看下面的查询:
    333. SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
    334. r.name region, SUM(o.tot_sales)
    335. FROM all_orders o JOIN region r
    336. ON r.region_id = o.region_id
    337. WHERE o.month BETWEEN 1 AND 3
    338. GROUP BY o.year, ROLLUP (o.month, r.name);
    339. 这个相当于GROUP BY ROLLUP (o.year,o.month, r.name) 但是去掉了最后一行的汇总。
    340. 因为每次分组都有year。没有group by null。
    341. 再看这个查询:
    342. SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
    343. r.name region, SUM(o.tot_sales)
    344. FROM all_orders o JOIN region r
    345. ON r.region_id = o.region_id
    346. WHERE o.month BETWEEN 1 AND 3
    347. GROUP BY o.year, o.month,ROLLUP (r.name);
    348. 这个相当于GROUP BY ROLLUP (o.year,o.month, r.name)去掉了对指定年的汇总和全量汇总。因为每次都有对year和month的分组,小计只有对年和月的小计,如下:
    349. YEAR MONTH        REGION                            SUM(o.tot_sales)
    350. ---------- ---------------- ---------------------------------------- ----------------------------------------------
    351. 2000 1月              New England                                 1018430
    352. 2000 1月              Mid-Atlantic                                1221394
    353. 2000 1月              Southeast US                                 758042
    354. 2000 1月                                                          2997866
    355. 2000 2月              New England                                 1231492
    356. 2000 2月              Mid-Atlantic                                 857352
    357. 2000 2月              Southeast US                                1236846
    358. 2000 2月                                                          3325690
    359. 2000 3月              New England                                 1132966
    360. 2000 3月              Mid-Atlantic                                1274062
    361. 2000 3月              Southeast US                                1311986
    362. 2000 3月                                                          3719014
    363. 2001 1月              New England                                  509215
    364. 2001 1月              Mid-Atlantic                                 610697
    365. 2001 1月              Southeast US                                 379021
    366. 2001 1月                                                          1498933
    367. 2001 2月              New England                                  615746
    368. 2001 2月              Mid-Atlantic                                 428676
    369. 2001 2月              Southeast US                                 618423
    370. 2001 2月                                                          1662845
    371. 2001 3月              New England                                  566483
    372. 2001 3月              Mid-Atlantic                                 637031
    373. 2001 3月              Southeast US                                 655993
    374. 2001 3月                                                          1859507
    375. 10.1.4 CUBE
    376. CUBE(交 叉列表)也是对group by运算的一种扩展,它比rollup扩展更加精细,组合类型更多,rollup是按组合的列从右到左递减分组计算,而 CUBE则是对所有可能的组合情况进行分组,这样分组的情况更多,覆盖所有的可能分组,并计算所有可能的分组的小计。比如:
    377. CUBE(C1,C2,C3……C(N))对N个列进行CUBE分组,那么可能的分组情况有:
    378. 不分组:C(n,0)
    379. 取一列分组:C(n,1)
    380. -----
    381. 取N列分组,全分组:C(n,n)
    382. 那么运用数学上的组合公式,得出所有所有可能的组合方式有:C(n,0)+C(n,1)+….+C(n,n)=2^n种。
    383. 我们以前面的rollup组合列为例子:rollup(name,month)是计算按区域名和月份分组以及每个区域的所有月份的小计以及总计。但是使用cube(name,month)则有4种分组,比rollup多一个每个月的所有区域的小计。下面比较一下这两种分组方式:
    384. 分组公式        描述
    385. rollup(name,month)        分组情况有:
    386. group by name,month
    387. group by name,null  //每个区域所有月份小计
    388. group by null,null  //合计
    389. cube(name,month)        分组情况有:
    390. group by null,null  //总计
    391. group by null,month //每个月份的所有区域小计
    392. group by name,null //每个区域的所有月份小计
    393. group by name,month
    394. CUBE使用方式:
    395. 和rollup一样,是
    396. select …
    397. from …
    398. group by cube(分组列列表)
    399. 请看上面表格中的分组例子:
    400. SELECT r.name region, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
    401. SUM(o.tot_sales) sums
    402. FROM all_orders o JOIN region r
    403. ON r.region_id = o.region_id
    404. GROUP BY CUBE(r.name, o.month)
    405. REGION                                   MONTH                  SUMS
    406. ---------------------------------------- ---------------- ---------------------------------------------------
    407. 59285706
    408. 1月                 4496799
    409. 2月                 4988535
    410. 3月                 5578521
    411. 4月                 5246355
    412. 5月                 5460942
    413. 6月                 4720233
    414. 7月                 5169720
    415. 8月                 4460823
    416. 9月                 4811094
    417. 10月                4789818
    418. 11月                4644450
    419. 12月                4918416
    420. New England                                                 19756923
    421. New England                              1月                 1527645
    422. New England                              2月                 1847238
    423. New England                              3月                 1699449
    424. New England                              4月                 1792866
    425. New England                              5月                 1698855
    426. New England                              6月                 1510062
    427. New England                              7月                 1678002
    428. New England                              8月                 1642968
    429. New England                              9月                 1726767
    430. New England                              10月                1648944
    431. New England                              11月                1384185
    432. New England                              12月                1599942
    433. Mid-Atlantic                                                18923298
    434. Mid-Atlantic                             1月                 1832091
    435. Mid-Atlantic                             2月                 1286028
    436. Mid-Atlantic                             3月                 1911093
    437. Mid-Atlantic                             4月                 1623438
    438. Mid-Atlantic                             5月                 1778805
    439. Mid-Atlantic                             6月                 1504455
    440. Mid-Atlantic                             7月                 1820742
    441. Mid-Atlantic                             8月                 1381560
    442. Mid-Atlantic                             9月                 1178694
    443. Mid-Atlantic                             10月                1530351
    444. Mid-Atlantic                             11月                1598667
    445. Mid-Atlantic                             12月                1477374
    446. Southeast US                                                20605485
    447. Southeast US                             1月                 1137063
    448. Southeast US                             2月                 1855269
    449. Southeast US                             3月                 1967979
    450. Southeast US                             4月                 1830051
    451. Southeast US                             5月                 1983282
    452. Southeast US                             6月                 1705716
    453. Southeast US                             7月                 1670976
    454. Southeast US                             8月                 1436295
    455. Southeast US                             9月                 1905633
    456. Southeast US                             10月                1610523
    457. Southeast US                             11月                1661598
    458. Southeast US                             12月                1841100
    459. 从上面结果可以看出,红色部分为group by null,null的结果,计算合计值。黄色部分为group by null,month的结果,计算每个月中所有区域的小计。绿色为group by name,null的结果,计算每个区域所有月份的小计。灰色的为group by name,month的结果。其中:
    460. group by name,month的所有结果的和=group by name,null的和=group by null,month的和=合计值。
    461. group by name,month中对应name的和=group by name,null对应的name的值。
    462. group by name,month中对应month的和=group by null,month对应month的和。
    463. 当然对于cube的运算,和rollup一样,也可以使用union实现,但是cube的组合方式呈级数增长,则union也会增长,而且性能不好,访问表多次,无cube的优化,语句没有cube简单。上例我们可以使用对应的4个group by 然后union all获得结果,但是结果的顺序不能保证。
    464. SELECT NULL region, NULL month, SUM(o.tot_sales)
    465. FROM all_orders o JOIN region r
    466. ON r.region_id = o.region_id
    467. UNION ALL
    468. SELECT NULL, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month, SUM(o.tot_sales)
    469. FROM all_orders o JOIN region r
    470. ON r.region_id = o.region_id
    471. GROUP BY o.month
    472. UNION ALL
    473. SELECT r.name region, NULL, SUM(o.tot_sales)
    474. FROM all_orders o JOIN region r
    475. ON r.region_id = o.region_id
    476. GROUP BY r.name
    477. UNION ALL
    478. SELECT r.name region, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
    479. SUM(o.tot_sales)
    480. FROM all_orders o JOIN region r
    481. ON r.region_id = o.region_id
    482. GROUP BY r.name, o.month;
    483. 看下union的执行计划:
    484. 只需要访问region和all_orders一次,而且有专门的GENERATE CUBE计算,提高效率,保证执行结果的有序性。
    485. 实际上,有上面对cube的分析可以得出,那个cube语句实际上等价于下列rollup语句:
    486. SELECT r.name region, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
    487. SUM(o.tot_sales)
    488. FROM all_orders o JOIN region r
    489. ON r.region_id = o.region_id
    490. GROUP BY rollup(r.name, o.month)
    491. union
    492. SELECT r.name region, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
    493. SUM(o.tot_sales)
    494. FROM all_orders o JOIN region r
    495. ON r.region_id = o.region_id
    496. GROUP BY rollup( o.month,r.name);
    497. 不可以使用union all,因为有重复数据。
    498. 对于CUBE来说,列的名字只要一样,那么顺序无所谓,结果都是一样的,因为cube是各种可能情况的组合,只不过统计的结果顺序不同而已。但是对于rollup来说,列的顺序不同,则结果不同,详细见rollup。
    499. 对本章的cube例子,改写cube的顺序,如下:
    500. SELECT r.name region, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
    501. SUM(o.tot_sales) sums
    502. FROM all_orders o JOIN region r
    503. ON r.region_id = o.region_id
    504. GROUP BY CUBE(o.month, r.name);
    505. REGION                                   MONTH                  SUMS
    506. ---------------------------------------- ---------------- ----------------------------------------------------
    507. 59285706
    508. New England                                                 19756923
    509. Mid-Atlantic                                                18923298
    510. Southeast US                                                20605485
    511. 1月                 4496799
    512. New England                              1月                 1527645
    513. Mid-Atlantic                             1月                 1832091
    514. Southeast US                             1月                 1137063
    515. 2月                 4988535
    516. New England                              2月                 1847238
    517. Mid-Atlantic                             2月                 1286028
    518. Southeast US                             2月                 1855269
    519. 3月                 5578521
    520. New England                              3月                 1699449
    521. Mid-Atlantic                             3月                 1911093
    522. Southeast US                             3月                 1967979
    523. 4月                 5246355
    524. New England                              4月                 1792866
    525. Mid-Atlantic                             4月                 1623438
    526. Southeast US                             4月                 1830051
    527. 5月                 5460942
    528. New England                              5月                 1698855
    529. Mid-Atlantic                             5月                 1778805
    530. Southeast US                             5月                 1983282
    531. 6月                 4720233
    532. New England                              6月                 1510062
    533. Mid-Atlantic                             6月                 1504455
    534. Southeast US                             6月                 1705716
    535. 7月                 5169720
    536. New England                              7月                 1678002
    537. Mid-Atlantic                             7月                 1820742
    538. Southeast US                             7月                 1670976
    539. 8月                 4460823
    540. New England                              8月                 1642968
    541. Mid-Atlantic                             8月                 1381560
    542. Southeast US                             8月                 1436295
    543. 9月                 4811094
    544. New England                              9月                 1726767
    545. Mid-Atlantic                             9月                 1178694
    546. Southeast US                             9月                 1905633
    547. 10月                4789818
    548. New England                              10月                1648944
    549. Mid-Atlantic                             10月                1530351
    550. Southeast US                             10月                1610523
    551. 11月                4644450
    552. New England                              11月                1384185
    553. Mid-Atlantic                             11月                1598667
    554. Southeast US                             11月                1661598
    555. 12月                4918416
    556. New England                              12月                1599942
    557. Mid-Atlantic                             12月                1477374
    558. Southeast US                             12月                1841100
    559. 我们可以看出,与cube(r.name,o.month)结果一样,只不过顺序不一样。cube(o.month,r.name)的顺序是:
    560. group by null,null
    561. group by null,r.name
    562. group by o.month,null
    563. group by o.month,r.name
    564. 其中最后两个分组是先小计再分组。
    565. 10.1.5部分CUBE
    566. 部分CUBE和部分ROLLUP类似,把不想要的小计和合计的列放到group by中,不放到cube中就可以了。比如:
    567. SELECT r.name region, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
    568. SUM(o.tot_sales) sums
    569. FROM all_orders o JOIN region r
    570. ON r.region_id = o.region_id
    571. GROUP BY r.name, CUBE(o.month);
    572. REGION                                   MONTH                  SUMS
    573. ---------------------------------------- ---------------- -------------------------------------------------
    574. New England                                                 19756923
    575. New England                              1月                 1527645
    576. New England                              2月                 1847238
    577. New England                              3月                 1699449
    578. New England                              4月                 1792866
    579. New England                              5月                 1698855
    580. New England                              6月                 1510062
    581. New England                              7月                 1678002
    582. New England                              8月                 1642968
    583. New England                              9月                 1726767
    584. New England                              10月                1648944
    585. New England                              11月                1384185
    586. New England                              12月                1599942
    587. Mid-Atlantic                                                18923298
    588. Mid-Atlantic                             1月                 1832091
    589. Mid-Atlantic                             2月                 1286028
    590. Mid-Atlantic                             3月                 1911093
    591. Mid-Atlantic                             4月                 1623438
    592. Mid-Atlantic                             5月                 1778805
    593. Mid-Atlantic                             6月                 1504455
    594. Mid-Atlantic                             7月                 1820742
    595. Mid-Atlantic                             8月                 1381560
    596. Mid-Atlantic                             9月                 1178694
    597. Mid-Atlantic                             10月                1530351
    598. Mid-Atlantic                             11月                1598667
    599. Mid-Atlantic                             12月                1477374
    600. Southeast US                                                20605485
    601. Southeast US                             1月                 1137063
    602. Southeast US                             2月                 1855269
    603. Southeast US                             3月                 1967979
    604. Southeast US                             4月                 1830051
    605. Southeast US                             5月                 1983282
    606. Southeast US                             6月                 1705716
    607. Southeast US                             7月                 1670976
    608. Southeast US                             8月                 1436295
    609. Southeast US                             9月                 1905633
    610. Southeast US                             10月                1610523
    611. Southeast US                             11月                1661598
    612. Southeast US                             12月                1841100
    613. 从上面结果可以看出,当将区域名从cube中移到group by中,则总会按区域名统计,则结果中移除了按月小计和总计的结果。我们可以发现,如果cube中只有一个列,那么和rollup的结果一致,也就是上面的语句等价于:
    614. SELECT r.name region, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
    615. SUM(o.tot_sales) sums
    616. FROM all_orders o JOIN region r
    617. ON r.region_id = o.region_id
    618. GROUP BY r.name, rollup(o.month);
    619. 注:对上面的rollup和cube的小计或总计中,有的列是null的表示按此列小计,如果前面的非汇总列都是null则表示总计。在实际应用中不可能把这些null给别人看,因为别人不知道你是用rollup或cube计算的。这时候可能会用到nvl或其它的转换,详细请看下节grouing函数。
    620. 10.1.6 GROUPING函数
    621. 在实际应用中,使用rollup或cube可以统计小计和合计的值,那么在小计和合计中会出现列的值为NULL的情况,客户就不知道什么意思了。为了增强客户的可读性,我们可能会想到使用NVL函数,如下:
    622. SELECT NVL(TO_CHAR(o.year), 'All Years') year,
    623. NVL(TO_CHAR(TO_DATE(o.month, 'MM'), 'Month'), 'First Quarter') month,
    624. NVL(r.name, 'All Regions') region, SUM(o.tot_sales) sums
    625. FROM all_orders o JOIN region r
    626. ON r.region_id = o.region_id
    627. WHERE o.month BETWEEN 1 AND 3
    628. GROUP BY ROLLUP (o.year, o.month, r.name);
    629. YEAR       MONTH                REGION                     SUMS
    630. ---------- -------------------- -------------------- -----------------------------------------------------
    631. 2000       1月                  New England             1018430
    632. 2000       1月                  Mid-Atlantic            1221394
    633. 2000       1月                  Southeast US             758042
    634. 2000       1月                  All Regions             2997866
    635. 2000       2月                  New England             1231492
    636. 2000       2月                  Mid-Atlantic             857352
    637. 2000       2月                  Southeast US            1236846
    638. 2000       2月                  All Regions             3325690
    639. 2000       3月                  New England             1132966
    640. 2000       3月                  Mid-Atlantic            1274062
    641. 2000       3月                  Southeast US            1311986
    642. 2000       3月                  All Regions             3719014
    643. 2000       First Quarter        All Regions            10042570
    644. 2001       1月                  New England              509215
    645. 2001       1月                  Mid-Atlantic             610697
    646. 2001       1月                  Southeast US             379021
    647. 2001       1月                  All Regions             1498933
    648. 2001       2月                  New England              615746
    649. 2001       2月                  Mid-Atlantic             428676
    650. 2001       2月                  Southeast US             618423
    651. 2001       2月                  All Regions             1662845
    652. 2001       3月                  New England              566483
    653. 2001       3月                  Mid-Atlantic             637031
    654. 2001       3月                  Southeast US             655993
    655. 2001       3月                  All Regions             1859507
    656. 2001       First Quarter        All Regions             5021285
    657. All Years  First Quarter        All Regions            15063855
    658. 可 以看出,原来是NULL的都被有意义的值代替,增加了用户可读性。使用NVL函数对这个例子来说是非常适合的,但是另外一个情况我们要考虑一下,如果这些 列中本身就有NULL值的存在,那么我们使用NVL函数之后,到底是小计还是本身的值呢???带着这个问题,我们来看一个例子:
    659. SELECT * FROM disputed_orders;
    660. ORDER_NBR CUST_NBR SALES_EMP_ID SALE_PRICE ORDER_DT    EXPECTED_SHIP_DT  STATUS
    661. --------- -------- ------------ ---------- ----------- -------------- ---------
    662. 1001        1         7354         99 22-jul-2001 23-jul-2001    DELIVERED
    663. 1000        1         7354            19-jul-2001 24-jul-2001
    664. 1002        5         7368            12-jul-2001 25-jul-2001
    665. 1003        4         7654         56 16-jul-2001 26-jul-2001    DELIVERED
    666. 1004        4         7654         34 18-jul-2001 27-jul-2001    PENDING
    667. 1005        8         7654         99 22-jul-2001 24-jul-2001    DELIVERED
    668. 1006        1         7354            22-jul-2001 28-jul-2001
    669. 1007        5         7368         25 20-jul-2001 22-jul-2001    PENDING
    670. 1008        5         7368         25 21-jul-2001 23-jul-2001    PENDING
    671. 1009        1         7354         56 18-jul-2001 22-jul-2001    DELIVERED
    672. 1012        1         7354         99 22-jul-2001 23-jul-2001    DELIVERED
    673. 1011        1         7354            19-jul-2001 24-jul-2001
    674. 1015        5         7368            12-jul-2001 25-jul-2001
    675. 1017        4         7654         56 16-jul-2001 26-jul-2001    DELIVERED
    676. 1019        4         7654         34 18-jul-2001 27-jul-2001    PENDING
    677. 1021        8         7654         99 22-jul-2001 24-jul-2001    DELIVERED
    678. 1023        1         7354            22-jul-2001 28-jul-2001
    679. 1025        5         7368         25 20-jul-2001 22-jul-2001    PENDING
    680. 1027        5         7368         25 21-jul-2001 23-jul-2001    PENDING
    681. 1029        1         7354         56 18-jul-2001 22-jul-2001    DELIVERED
    682. 可以看到,有的status值为NULL。那么现在我们需要汇总每个customer对应状态的数目以及所有customer每个状态的数目最后合计。使用NVL函数如下:
    683. SELECT NVL(TO_CHAR(cust_nbr), 'All Customers') customer,
    684. NVL(status, 'All Status') status,
    685. COUNT(*) FROM disputed_orders
    686. GROUP BY CUBE(cust_nbr, status);
    687. CUSTOMER             STATUS                 COUNT(*)
    688. -------------------- -------------------- ----------
    689. All Customers        All Status                    6
    690. All Customers        All Status                   20
    691. All Customers        PENDING                       6
    692. All Customers        DELIVERED                     8
    693. 1                    All Status                    4
    694. 1                    All Status                    8
    695. 1                    DELIVERED                     4
    696. 4                    All Status                    4
    697. 4                    PENDING                       2
    698. 4                    DELIVERED                     2
    699. 5                    All Status                    2
    700. 5                    All Status                    6
    701. 5                    PENDING                       4
    702. 8                    All Status                    2
    703. 8                    DELIVERED                     2
    704. 从上面的结果可以看出,这种使用NVL函数没有任何意义,得出的结果混淆,比如黄色部分,对用户1有两个all status,其实其中一个是本身表中的status为null,因此统计混乱。
    705. Oracle为了解决这个问题,专门有一个grouping函数。grouping函数解释如下:
    706. 1.        grouping函数只有一个参数,接受来自于group by,rollup,cube,grouping sets的列。
    707. 2.        grouping函数对rollup,cube的汇总列(小计或合计行的列值)置为1,其他的非汇总列置为0,如在单独的group by中使用肯定全为0,因为没有通过rollup或cube的小计或合计行。
    708. 3.        grouping函数可以有效地避免nvl函数带来的小计或合计与本身表中有null带来的混淆问题。
    709. 4.        使用方式为:
    710. SELECT  . . .  [GROUPING(grouping_column_name)]  . . .
    711. FROM  . . .
    712. GROUP BY  . . .  {ROLLUP | CUBE} (grouping_column_name)
    713. 下面用grouping函数改写上面的的例子如下:
    714. SELECT  decode(grouping(cust_nbr),1,'ALL CUSTOMER',cust_nbr) customer,
    715. decode(grouping(status),1,'ALL STATUS',nvl(status,'原始值为空')) status,
    716. COUNT(*) FROM disputed_orders
    717. GROUP BY CUBE(cust_nbr, status);
    718. CUSTOMER             STATUS                 COUNT(*)
    719. -------------------- -------------------- ----------
    720. ALL CUSTOMER         原始值为空                    6
    721. ALL CUSTOMER         ALL STATUS                   20
    722. ALL CUSTOMER         PENDING                       6
    723. ALL CUSTOMER         DELIVERED                     8
    724. 1                    原始值为空                    4
    725. 1                    ALL STATUS                    8
    726. 1                    DELIVERED                     4
    727. 4                    ALL STATUS                    4
    728. 4                    PENDING                       2
    729. 4                    DELIVERED                     2
    730. 5                    原始值为空                    2
    731. 5                    ALL STATUS                    6
    732. 5                    PENDING                       4
    733. 8                    ALL STATUS                    2
    734. 8                    DELIVERED                     2
    735. 使用decode和grouping函数,避免了null的问题,现在黄色部分对status为null的也统计了。汇总和status为null的不再混淆。
    736. 再看一个例子:
    737. SELECT DECODE(GROUPING(o.year), 1, 'All Years', o.year) Year,
    738. DECODE(GROUPING(o.month), 1, 'All Months',
    739. TO_CHAR(TO_DATE(o.month, 'MM'), 'Month')) Month,
    740. DECODE(GROUPING(r.name), 1, 'All Regions', r.name) Region, SUM(o.tot_sales) sums
    741. FROM all_orders o JOIN region r
    742. ON r.region_id = o.region_id
    743. WHERE o.month BETWEEN 1 AND 3
    744. GROUP BY ROLLUP (o.year, o.month, r.name);
    745. YEAR       MONTH                REGION                     SUMS
    746. ---------- -------------------- -------------------- ----------
    747. 2000       January              New England             1018430
    748. 2000       January              Mid-Atlantic            1221394
    749. 2000       January              Southeast US             758042
    750. 2000       January              All Regions             2997866
    751. 2000       February             New England             1231492
    752. 2000       February             Mid-Atlantic             857352
    753. 2000       February             Southeast US            1236846
    754. 2000       February             All Regions             3325690
    755. 2000       March                New England             1132966
    756. 2000       March                Mid-Atlantic            1274062
    757. 2000       March                Southeast US            1311986
    758. 2000       March                All Regions             3719014
    759. 2000       All Months           All Regions            10042570
    760. 2001       January              New England              509215
    761. 2001       January              Mid-Atlantic             610697
    762. 2001       January              Southeast US             379021
    763. 2001       January              All Regions             1498933
    764. 2001       February             New England              615746
    765. 2001       February             Mid-Atlantic             428676
    766. 2001       February             Southeast US             618423
    767. 2001       February             All Regions             1662845
    768. 2001       March                New England              566483
    769. 2001       March                Mid-Atlantic             637031
    770. 2001       March                Southeast US             655993
    771. 2001       March                All Regions             1859507
    772. 2001       All Months           All Regions             5021285
    773. All Years  All Months           All Regions            15063855
    774. 使用deocode(当然也可以用case,没有decode简单)联合grouping函数,使报表的小计和合计列描述有意义,避免原始数据有null而使用nvl函数带来混淆的问题。
    775. 10.1.6 GROUPING SETS
    776. 以 上我们已经掌握了rollup,cube分组统计的知识。但是rollup和cube的分组统计包含了常规group by的统计明细以及对相关列的小计 和合计值。如果我们需要的只是按每个分组列小计呢?oracle提供了grouping sets操作,对group by的另一个扩展,专门对分组列分 别进行小计计算,不包括合计。使用方式和rollup和cube一样,都是放在group by中。如:
    777. grouping sets(C1,C2….Cn)则分组方式有n种,等于列的数目。
    778. group by c1,null,null…..null。
    779. group by null,c2,null….null。
    780. ….
    781. group by null,null……..Cn。
    782. 无group by null,null….null,也就是说没有合计行。
    783. 注意:grouping sets的统计结果和列的顺序无关。
    784. 如下例:
    785. SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
    786. r.name region, SUM(o.tot_sales) sums
    787. FROM all_orders o JOIN region r
    788. ON r.region_id = o.region_id
    789. WHERE o.month BETWEEN 1 AND 3
    790. GROUP BY GROUPING SETS (o.year, o.month, r.name);
    791. YEAR MONTH                REGION                     SUMS
    792. ----- -------------------- -------------------- ------------------------------------------------
    793. New England             5074332
    794. Mid-Atlantic            5029212
    795. Southeast US            4960311
    796. January                                      4496799
    797. February                                     4988535
    798. March                                        5578521
    799. 2001                                              5021285
    800. 2000                                             10042570
    801. 可以看出,没有常规group by统计结果,只有按每个单独列的小计,也不包含合计。改变grouping sets中列的顺序,不影响结果,上面的语句等价于:
    802. SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
    803. r.name region, SUM(o.tot_sales) sums
    804. FROM all_orders o JOIN region r
    805. ON r.region_id = o.region_id
    806. WHERE o.month BETWEEN 1 AND 3
    807. GROUP BY GROUPING SETS (o.month, r.name, o.year);
    808. 10.1.7ROLLUP、CUBE、GROUPING SETS总结
    809. 本章已经学习了小计和合计的三个主要增强的group by操作:rollup,cube,grouping sets操作。下面总结下他们的使用以及区别。
    810. 操作名        简述        列顺序是否要求        分组情况        使用情况
    811. ROLLUP        ROLLUP操作可以查询出常规group by的结果以及按列小计和合计的结果。        和列顺序有关。见分组情况。        ROLLUP(C1,C2…Cn)。
    812. 总共会进行n+1个分组。那么实际上有n+1个group by的union all结果。
    813. 顺序为:
    814. group by C1,C2….Cn
    815. group by C1,C2,….Cn-1
    816. group by C1,C2….Cn-2
    817. group by C1
    818. group by null,….null
    819. 可以看出是从右到左逐渐递减列的group by,所以和列的顺序有关,顺序不同,意义不同。
    820. 需要查询的分组明细以及小计和合计功能的时候使用。
    821. CUBE        CUBE操作是对所有可能的列组合分组,包括常规group by以及所有分组小计和合计的值。        和列顺序无关。        CUBE(C1,C2,C3……C(N))对N个列进行CUBE分组,那么可能的分组情况有:
    822. 不分组:C(n,0)
    823. 取一列分组:C(n,1)
    824. 取N列分组,全分组:C(n,n)
    825. 那么运用数学上的组合公式,得出所有所有可能的组合方式有:C(n,0)+C(n,1)+….+C(n,n)=2^n种。
    826. 如果只有一个列,那么和rollup一样。
    827. 比如cube(name,month)
    828. 分组情况有:
    829. group by null,null  //总计C(2,0)
    830. group by null,month //每个月份的所有区域小计
    831. group by name,null //每个区域的所有月份小计 C(2,1)
    832. group by name,month //C(2,2)常规group by
    833. 因此,结果和列的顺序无关。
    834. 适用于需要统计各种可能的组合情况的小计和合计以及常规分组明细。
    835. GROUPING SETS        只查询按每个列小计的结果,不包含合计值        和列顺序无关。        分组种类=列的数目。
    836. grouping sets(C1,C2,…Cn)
    837. 那么等价于下列union all
    838. group by C1,null…..
    839. group by null,C2….
    840. group by null,null….Cn        适用于只需要按每个列小计。
    841. 10.2分组扩展功能增强
    842. 10.1节我们只是对分组扩展功能使用了几个简单的例子说明,还足以证明扩展分组功能的强大,这些简单的例子适用于oracle8i以及后续版本,但是在Oracle9i开始,对分组扩展的功能提供了新的可行操作:
    843. ◆ 在group by子句中可以重复列名。
    844. ◆ 对复合列进行分组。
    845. ◆ 分组连接。
    846. 本节内容就是围绕上面3点在9i中引入的分组扩展扩展新功能展开。
    847. 10.2.1在group by中使用重复列在Oracle8i的时候,在group by中重复列名是不允许的。比如group by中包含了扩展子句(cube,rollup等),在这些扩展子句内外使用相同的列名是不合法的,比如在oracle8i中这样写:
    848. SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
    849. r.name region, SUM(o.tot_sales) total
    850. FROM all_orders o JOIN region r
    851. ON r.region_id = o.region_id
    852. WHERE o.month BETWEEN 1 AND 3
    853. GROUP BY o.year,ROLLUP (o.year, o.month, r.name);
    854. ERROR at line 6:
    855. ORA-30490: Ambiguous expression in GROUP BY ROLLUP or CUBE list
    856. 报分组列名混淆错误。但是在Oracle9i之后,则是正确的,结果如下:
    857. YEAR MONTH    REGION                                        TOTAL
    858. ---------- ---------------- ---------------------------------------- ----------------------------------------------
    859. 2000 1月              New England                                 1018430
    860. 2000 1月              Mid-Atlantic                                1221394
    861. 2000 1月              Southeast US                                 758042
    862. 2000 1月                                                          2997866
    863. 2000 2月              New England                                 1231492
    864. 2000 2月              Mid-Atlantic                                 857352
    865. 2000 2月              Southeast US                                1236846
    866. 2000 2月                                                          3325690
    867. 2000 3月              New England                                 1132966
    868. 2000 3月              Mid-Atlantic                                1274062
    869. 2000 3月              Southeast US                                1311986
    870. 2000 3月                                                          3719014
    871. 2001 1月              New England                                  509215
    872. 2001 1月              Mid-Atlantic                                 610697
    873. 2001 1月              Southeast US                                 379021
    874. 2001 1月                                                          1498933
    875. 2001 2月              New England                                  615746
    876. 2001 2月              Mid-Atlantic                                 428676
    877. 2001 2月              Southeast US                                 618423
    878. 2001 2月                                                          1662845
    879. 2001 3月              New England                                  566483
    880. 2001 3月              Mid-Atlantic                                 637031
    881. 2001 3月              Southeast US                                 655993
    882. 2001 3月                                                          1859507
    883. 2000                                                             10042570
    884. 2001                                                              5021285
    885. 2000                                                             10042570
    886. 2001                                                              5021285
    887. 因为o.year同时出现在group by中和rollup中,所以对每年的所有月份的合计重复统计,见黄色部分,在group by中同名列出现多次没有什么多大用处,只不过为了说明在Oracle9i是允许的。
    888. 10.2.2对复合列进行分组 首先说下复合列在Oracle中的使用,想必复合列的使用大家已经比较熟悉了。常见的复合列是在子查询和group by使用。多个列以括号包括,逗号分隔,这样的一个组被当作一个整体。比如下面是一个子查询的例子:
    889. select id,name,trade_id from product
    890. where (name,trade_id) in
    891. (('易达卡',2),('畅听卡',2));
    892. ID NAME         TRADE_ID
    893. ---------- ---------- ------------------------------
    894. 55 易达卡              2
    895. 58 畅听卡              2
    896. 可以看出,查询了name,trade_id分别对应('易达卡',2)和('畅听卡',2)的值。
    897. 下面看下在单独的group by中使用复合列。例如:
    898. select id,name,trade_id,count(*) from product  group by (id,name),trade_id;
    899. 上面的语句就是先找id,name相同的放在一起,然后再找trade_id相同的放在一组,这里的括号相同于优先级。
    900. 在 Oracle8i中,分组只支持列的独立分组,不支持复合列的分组,Oracle9i开始支持复合列的分组,也就是上面所说的多个列用括号包括,中间短 号,括号内的列相当于一个整体,就像一个列一样,不可拆开。比如Oracle8i只允许rollup(a,b,c),但是oracle9i允许 rollup(a,(b,c)),那么将(b,c)作为一个列参与分组计算。增强了分组的功能,可以过滤某些结果。如下例:
    901. SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
    902. r.name region, SUM(o.tot_sales) total
    903. FROM all_orders o JOIN region r
    904. ON r.region_id = o.region_id
    905. WHERE o.month BETWEEN 1 AND 3
    906. GROUP BY ROLLUP ((o.year, o.month),r.name);
    907. YEAR MONTH            REGION                                        TOTAL
    908. ---------- ---------------- ---------------------------------------- ----------
    909. 2000 1月              New England                                 1018430
    910. 2000 1月              Mid-Atlantic                                1221394
    911. 2000 1月              Southeast US                                 758042
    912. 2000 1月                                                          2997866
    913. 2000 2月              New England                                 1231492
    914. 2000 2月              Mid-Atlantic                                 857352
    915. 2000 2月              Southeast US                                1236846
    916. 2000 2月                                                          3325690
    917. 2000 3月              New England                                 1132966
    918. 2000 3月              Mid-Atlantic                                1274062
    919. 2000 3月              Southeast US                                1311986
    920. 2000 3月                                                          3719014
    921. 2001 1月              New England                                  509215
    922. 2001 1月              Mid-Atlantic                                 610697
    923. 2001 1月              Southeast US                                 379021
    924. 2001 1月                                                          1498933
    925. 2001 2月              New England                                  615746
    926. 2001 2月              Mid-Atlantic                                 428676
    927. 2001 2月              Southeast US                                 618423
    928. 2001 2月                                                          1662845
    929. 2001 3月              New England                                  566483
    930. 2001 3月              Mid-Atlantic                                 637031
    931. 2001 3月              Southeast US                                 655993
    932. 2001 3月                                                          1859507
    933. 15063855
    934. 由上面的结果分析,GROUP BY ROLLUP ((o.year, o.month),r.name)相当于group by (o.year,o.month),r.name、group by (o.year,o.month),null和group by null,null三个的union结果。与GROUP BY ROLLUP (o.year, o.month,r.name)少了group by rollup o.year,null,null。按年计算所有月份的小计。因为(o.year,o.month)是复合列,相当于一个列,不可分隔。
    935. 虽然Oracle8i未提供复合列分组的功能,但是我们可以用复合列的思想,将两个列通过连接操作,变成一个列就可以了,当然在select显示的时候必须与group by中的一致。
    936. 下面是oracle8i的实现:
    937. SELECT TO_CHAR(o.year)||' '||TO_CHAR(TO_DATE(o.month,'MM'),'Month')
    938. Year_Month,
    939. r.name region, SUM(o.tot_sales)
    940. FROM all_orders o JOIN region r
    941. ON r.region_id = o.region_id
    942. WHERE o.month BETWEEN 1 AND 3
    943. GROUP BY
    944. ROLLUP (TO_CHAR(o.year)||' '|| TO_CHAR(TO_DATE(o.month,'MM'),'Month'), r.name);
    945. 结果与oracle9i的例子一致,只不过year和month通过连接操作符变成一个列显示而已。
    946. group by 中使用复合列的好处在于过滤某些本来有的结果。比如上例就过滤了按年计算所有月份的小计值,但是保留了最后的合计值。如果使用前面说的局部 rollup:group by o.year,rollup(o.month,r.name)则会丢掉最后的合计值,而且包含了按年统计所有月份的小计 值,总之,应该根据具体需求,选择合适的方案。
    947. 10.2.3级联分组
    948. 在Oracle9i及后续版本,允许在group by子句后面使用多个ROLLUP,CUBE,GROUPING SETS操作,这在Oracle8i中是不允许的,在Oracle8i中值允许使用其中的一个。
    949. 下面研究一下rollup级联分组。
    950. 比如在Oracle8i中查询如下语句:
    951. SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
    952. r.name region, SUM(o.tot_sales) total
    953. FROM all_orders o JOIN region r
    954. ON r.region_id = o.region_id
    955. WHERE o.month BETWEEN 1 AND 3
    956. GROUP BY ROLLUP (o.year, o.month), ROLLUP(r.name);
    957. 那么报错:ERROR at line 6:
    958. ORA-30489: Cannot have more than one rollup/cube expression list
    959. 在Oracle9i中运行结果为:
    960. YEAR MONTH    REGION                                        TOTAL
    961. ---------- ---------------- ---------------------------------------- -----------------------------------------------
    962. New England                                 5074332
    963. Mid-Atlantic                                5029212
    964. Southeast US                                4960311
    965. 15063855
    966. 2000                  New England                                 3382888
    967. 2000                  Mid-Atlantic                                3352808
    968. 2000                  Southeast US                                3306874
    969. 2000                                                             10042570
    970. 2000 1月              New England                                 1018430
    971. 2000 1月              Mid-Atlantic                                1221394
    972. 2000 1月              Southeast US                                 758042
    973. 2000 1月                                                          2997866
    974. 2000 2月              New England                                 1231492
    975. 2000 2月              Mid-Atlantic                                 857352
    976. 2000 2月              Southeast US                                1236846
    977. 2000 2月                                                          3325690
    978. 2000 3月              New England                                 1132966
    979. 2000 3月              Mid-Atlantic                                1274062
    980. 2000 3月              Southeast US                                1311986
    981. 2000 3月                                                          3719014
    982. 2001                  New England                                 1691444
    983. 2001                  Mid-Atlantic                                1676404
    984. 2001                  Southeast US                                1653437
    985. 2001                                                              5021285
    986. 2001 1月              New England                                  509215
    987. 2001 1月              Mid-Atlantic                                 610697
    988. 2001 1月              Southeast US                                 379021
    989. 2001 1月                                                          1498933
    990. 2001 2月              New England                                  615746
    991. 2001 2月              Mid-Atlantic                                 428676
    992. 2001 2月              Southeast US                                 618423
    993. 2001 2月                                                          1662845
    994. 2001 3月              New England                                  566483
    995. 2001 3月              Mid-Atlantic                                 637031
    996. 2001 3月              Southeast US                                 655993
    997. 2001 3月                                                          1859507
    998. :等价于GROUP BY ROLLUP (o.year, o.month), ROLLUP(r.name)
    999. GROUP BY o.year,o.month,r.name
    1000. GROUP BY o.year,null,r.name
    1001. GROUP BY null,null,r.name
    1002. GROUP BY null,null,null
    1003. GROUP BY o.year,o.month,null
    1004. GROUP BY o.year,null,null
    1005. 的union all的结果。提供了比rollup(o.year,o.month,r.name)更多的分组功能,多了一个GROUP BY o.year,null,r.name。
    1006. group by null。因此才出现上述的6种分组结果:分组的结果为3*2=6,而没有group by null,o.month,r.name和group by null,o.month,null这两种结果。group by null,null。第2个rollup(r.name)的执行顺序是group by r.namegroup by o.year,null        当 在group by中使用多个rollup,cube,grouping sets操作的时候,我们叫这样的分组为级联分组。级联分组的结果就是产生每个 单独分组操作的交叉列表,但是要符合每个操作的处理步骤,比如上面的 GROUP BY ROLLUP (o.year, o.month), ROLLUP(r.name)操作,第1个rollup(o.year,o.month)的执行顺序是group by o.year,o.month
    1007. 根据上述的规则,我们将rollup(o.year,o.month)改为cube(o.year,o.month),那么cube的计算种类是4中,后面的rollup(r.name)为两种,那么这种级联分组应该有相当于group by的分类为4*2=8种,比rollup(o.year,o.month),rollup(r.name)多了group by null,o.month,r.name和group by null,o.month,null这两种结果。我们看下结果:
    1008. SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
    1009. r.name region, SUM(o.tot_sales) total
    1010. FROM all_orders o JOIN region r
    1011. ON r.region_id = o.region_id
    1012. WHERE o.month BETWEEN 1 AND 3
    1013. GROUP BY cube (o.year, o.month), ROLLUP(r.name);
    1014. YEAR MONTH            REGION                                        TOTAL
    1015. ---------- ---------------- ---------------------------------------- ----------
    1016. 2000 1月              New England                                 1018430
    1017. 2001 1月              New England                                  509215
    1018. 2000 1月              Mid-Atlantic                                1221394
    1019. 2001 1月              Mid-Atlantic                                 610697
    1020. 2000 1月              Southeast US                                 758042
    1021. 2001 1月              Southeast US                                 379021
    1022. 2000 2月              New England                                 1231492
    1023. 2001 2月              New England                                  615746
    1024. 2000 2月              Mid-Atlantic                                 857352
    1025. 2001 2月              Mid-Atlantic                                 428676
    1026. 2000 2月              Southeast US                                1236846
    1027. 2001 2月              Southeast US                                 618423
    1028. 2000 3月              New England                                 1132966
    1029. 2001 3月              New England                                  566483
    1030. 2000 3月              Mid-Atlantic                                1274062
    1031. 2001 3月              Mid-Atlantic                                 637031
    1032. 2000 3月              Southeast US                                1311986
    1033. 2001 3月              Southeast US                                 655993
    1034. 1月              New England                                 1527645
    1035. 1月               Mid-Atlantic                                1832091
    1036. 1月              Southeast US                                1137063
    1037. 2月              New England                                 1847238
    1038. 2月              Mid-Atlantic                                1286028
    1039. 2月              Southeast US                                1855269
    1040. 3月              New England                                 1699449
    1041. 3月              Mid-Atlantic                                1911093
    1042. 3月              Southeast US                                1967979
    1043. 2000                  New England                                 3382888
    1044. 2001                  New England                                 1691444
    1045. New England                                 5074332
    1046. 2000                  Mid-Atlantic                                3352808
    1047. 2001                  Mid-Atlantic                                1676404
    1048. Mid-Atlantic                                5029212
    1049. 2000                  Southeast US                                3306874
    1050. 2001                  Southeast US                                1653437
    1051. Southeast US                                4960311
    1052. 1月                                                          4496799
    1053. 2月                                                          4988535
    1054. 3月                                                          5578521
    1055. 2000                                                             10042570
    1056. 2001                                                              5021285
    1057. 15063855
    1058. 2000 1月                                                          2997866
    1059. 2001 1月                                                          1498933
    1060. 2000 2月                                                          3325690
    1061. 2001 2月                                                          1662845
    1062. 2000 3月                                                          3719014
    1063. 2001 3月                                                          1859507
    1064. 分析结果:黄色部分就是与使用rollup的时候多出的结果,正好是group by null,o.month,r.name和group by null,o.month,null的结果,其他与rollup(o.year,o.month),rollup(r.name)完全一致。
    1065. 下面研究一下CUBE级联分组。
    1066. 有了级联分组,可以将rollup与cube之间相互转换,比如:
    1067. SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
    1068. r.name region, SUM(o.tot_sales) total
    1069. FROM all_orders o JOIN region r
    1070. ON r.region_id = o.region_id
    1071. WHERE o.month BETWEEN 1 AND 3
    1072. GROUP BY ROLLUP(o.year),  ROLLUP (o.month), ROLLUP (r.name);
    1073. 3个单列的rollup级联分组,每个分组有2种,那么总共有2^3=8种,正好相当于下列cube运算的结果:
    1074. SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
    1075. r.name region, SUM(o.tot_sales) total
    1076. FROM all_orders o JOIN region r
    1077. ON r.region_id = o.region_id
    1078. WHERE o.month BETWEEN 1 AND 3
    1079. GROUP BY CUBE (o.year, o.month, r.name);
    1080. 再来考虑一个问题,如果对于cube级联分组,会出现什么情况下,我们知道cube是对所有可能性进行分组,有n个列,分组的种类是2^n个。那么cube级联分组不管怎么拆分,实际上和单独的cube所有列的结果是一致的。比如下列语句和上面的一致(简写如下):
    1081. GROUP BY CUBE (o.year, o.month), CUBE (r.name);
    1082. GROUP BY CUBE (o.year), CUBE (o.month, r.name);
    1083. GROUP BY CUBE (o.year), CUBE (o.month), CUBE (r.name);
    1084. 都是有8种分组方法,当然如果和其它的比如rollup联合起来,cube拆开与不拆开的结果也是一致的。
    1085. 10.2.3.1 GROUPING SETS级联分组
    1086. Grouping Sets 的级联分组很有用,因为可以知道,grouping sets分组只是对单列分别进行小计统计,比如有n列就是分别对这个n列进行单列小计,有n种结果。 但是当我们需要使用Grouping sets获得复合列的小计的时候,那么单独的grouping sets分组就没有办法了,但是可以使用级联 grouping sets操作。同rollup和cube的级联分组一样,比如 grouping sets(a,b),grouping sets(c,d)那么有2*2=4种统计方法:分别是group by(a,c)、
    1087. group by(a,d)、group by(b,c)和group by(b,c),即列的交叉分组。如:
    1088. SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
    1089. r.name region, SUM(o.tot_sales) total
    1090. FROM all_orders o JOIN region r
    1091. ON r.region_id = o.region_id
    1092. WHERE o.month BETWEEN 1 AND 3
    1093. GROUP BY GROUPING SETS (o.year, o.month), GROUPING SETS (r.name);
    1094. YEAR MONTH            REGION                                        TOTAL
    1095. --------- ---------------- ---------------------------------------- ----------
    1096. 3月              Mid-Atlantic                                1911093
    1097. 1月              New England                                 1527645
    1098. 2月              Southeast US                                1855269
    1099. 2月              Mid-Atlantic                                1286028
    1100. 1月              Southeast US                                1137063
    1101. 1月              Mid-Atlantic                                1832091
    1102. 3月              New England                                 1699449
    1103. 3月              Southeast US                                1967979
    1104. 2月              New England                                 1847238
    1105. 2000                  Mid-Atlantic                                3352808
    1106. 2001                  New England                                 1691444
    1107. 2000                  Southeast US                                3306874
    1108. 2001                  Southeast US                                1653437
    1109. 2000                  New England                                 3382888
    1110. 2001                  Mid-Atlantic                                1676404
    1111. 上面的黄色部分就是对(o.month,r.name)的分组结果,红色部分是(o.year,r.name)的分组结果,有两个复合列的分组统计。
    1112. 下面我们对上面的例子,增加一个列,看看结果:
    1113. SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
    1114. r.name region, SUM(o.tot_sales) total
    1115. FROM all_orders o JOIN region r
    1116. ON r.region_id = o.region_id
    1117. WHERE o.month BETWEEN 1 AND 3
    1118. GROUP BY GROUPING SETS (o.year, o.month), GROUPING SETS (o.year, r. name);
    1119. YEAR MONTH            REGION                                        TOTAL
    1120. ---------- ---------------- ---------------------------------------- ----------
    1121. 3月              Mid-Atlantic                                1911093
    1122. 1月              New England                                 1527645
    1123. 2月              Southeast US                                1855269
    1124. 2月              Mid-Atlantic                                1286028
    1125. 1月              Southeast US                                1137063
    1126. 1月              Mid-Atlantic                                1832091
    1127. 3月              New England                                 1699449
    1128. 3月              Southeast US                                1967979
    1129. 2月              New England                                 1847238
    1130. 2000                  Mid-Atlantic                                3352808
    1131. 2001                  New England                                 1691444
    1132. 2000                  Southeast US                                3306874
    1133. 2001                  Southeast US                                1653437
    1134. 2000                  New England                                 3382888
    1135. 2001                  Mid-Atlantic                                1676404
    1136. 2000 1月                                                          2997866
    1137. 2000 2月                                                          3325690
    1138. 2000 3月                                                          3719014
    1139. 2000                                                             10042570
    1140. 2001 1月                                                          1498933
    1141. 2001 2月                                                          1662845
    1142. 2001 3月                                                          1859507
    1143. 2001                                                              5021285
    1144. GROUPING SETS (o.year, o.month), GROUPING SETS (o.year, r. name) 相当于group by(o.year,o.year)等价于 group by o.year,group by(o.year,r.name),group by(o.month,o.year),group by(o.month,r.name) 对应上面4个区域的结果。
    1145. 其 实,因为最终的结果可以转化为对应的group by分组的union结果,而group by分组和列的顺序没有关系,因此级联 grouping sets和列的顺序也没有关系,只要分组的种类一致即可,比如上面的等价 于: GROUPING SETS (o.year, r.name), GROUPING SETS (o.year, o.month);
    1146. GROUPING SETS (o.month, o.year), GROUPING SETS (r.name, o.year);
    1147. 另外,在一个group by中可以同时使用grouping sets,cube,rollup,比如:
    1148. SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
    1149. r.name region, SUM(o.tot_sales) total
    1150. FROM all_orders o JOIN region r
    1151. ON r.region_id = o.region_id
    1152. WHERE o.month BETWEEN 1 AND 3
    1153. GROUP BY GROUPING SETS (o.month, o.year), ROLLUP(r.name), CUBE (o.year);
    1154. 上面的有8种统计结果,但是这样的语句其实一般意义不大,如果需要这样复杂的统计,需要仔细分析。
上一篇:C#面向对象二


下一篇:Blend4精选案例图解教程(二):找张图片玩特效