10.1.2 ROLLUP
从Oracle8i开始,oracle提供了很多在一个查询中生成多级别汇总数据的特性,第10章我们研究的是对group by的扩展,关于扩展内容和版本对应关系如下表:
功能 版本
ROLLUP oracle8i
CUBE oracle8i
GROUPING SETS oracle9i
本节研究rollup,对cube和grouping sets稍后介绍。ROLLUP是对group by的扩展,因此,它只能出现在group by子句中,依赖于分组的列,对每个分组会生成汇总数据,如下:
SELECT ….
FROM ….
GROUP BY ROLLUP(C1,C2,C3….C(n-1),C(n));
总共会进行n+1个分组。
那么实际上有n+1个group by的union all结果。
第1个分组:全分组。C1,C2,C3….C(n-1),C(n)
第2个分组:C1,C2,C3….C(n-1);//这个分组实际上就是对前面前n-1列分组的小计.
----然后逐渐递减分组列
第n个分组:C1。对上一个分组的小计。
第n+1个分组。不分组全量汇总。相当于合计。也是对group by C1的小计。相当于group by null。
有了rollup,对10.1.1实现的union多级别汇总就可以使用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);
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
New England 19756923
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
Mid-Atlantic 18923298
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
Southeast US 20605485
59285706
从上面的结果可以看出,rollup和group by联合一起使用,达到了按group by列顺序分组,并且实现小计和合计的功能。黄色部分就是对上面组的小计,最后红色的就是合计。而且,rollup这种分组还是有序的,先全部分组,然后对每个分组小计,最后合计,使处理结果更清楚。union all就很难实现这种效果了。而且最重要的是性能比union all好,通过上面查询的执行计划可以看出:
rollup仅仅一个索引扫描region,一个全表扫描all_orders,然后通过nested loop,最后sort group by rollup就能得到结果。比union all多次扫描效率高很多。
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 ( o.month,r.name);
REGION MONTH SUM(O.TOT_SALES)
---------------------------------------- ---------------- ----------------
New England 1月 1527645
Mid-Atlantic 1月 1832091
Southeast US 1月 1137063
1月 4496799
New England 2月 1847238
Mid-Atlantic 2月 1286028
Southeast US 2月 1855269
2月 4988535
New England 3月 1699449
Mid-Atlantic 3月 1911093
Southeast US 3月 1967979
3月 5578521
New England 4月 1792866
Mid-Atlantic 4月 1623438
Southeast US 4月 1830051
4月 5246355
New England 5月 1698855
Mid-Atlantic 5月 1778805
Southeast US 5月 1983282
5月 5460942
New England 6月 1510062
Mid-Atlantic 6月 1504455
Southeast US 6月 1705716
6月 4720233
New England 7月 1678002
Mid-Atlantic 7月 1820742
Southeast US 7月 1670976
7月 5169720
New England 8月 1642968
Mid-Atlantic 8月 1381560
Southeast US 8月 1436295
8月 4460823
New England 9月 1726767
Mid-Atlantic 9月 1178694
Southeast US 9月 1905633
9月 4811094
New England 10月 1648944
Mid-Atlantic 10月 1530351
Southeast US 10月 1610523
10月 4789818
New England 11月 1384185
Mid-Atlantic 11月 1598667
Southeast US 11月 1661598
11月 4644450
New England 12月 1599942
Mid-Atlantic 12月 1477374
Southeast US 12月 1841100
12月 4918416
59285706
可以看出,达到了按月小计的功能,当然最后红色的合计值和按区域小计一致。
注意:
rollup中列的顺序不同,则统计的结果不同。因为它是按列从右递减分组的。
比如,我们需要统计第1个季度,并且年月汇总所有区域的数据,如下:
SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
r.name region, SUM(o.tot_sales) sums
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
WHERE o.month BETWEEN 1 AND 3
GROUP BY ROLLUP (o.year, o.month, r.name);
YEAR MONTH REGION SUMS
--------- ---------------- ---------------------------------------- ---------------------------------------------
2000 1月 New England 1018430
2000 1月 Mid-Atlantic 1221394
2000 1月 Southeast US 758042
2000 1月 2997866
2000 2月 New England 1231492
2000 2月 Mid-Atlantic 857352
2000 2月 Southeast US 1236846
2000 2月 3325690
2000 3月 New England 1132966
2000 3月 Mid-Atlantic 1274062
2000 3月 Southeast US 1311986
2000 3月 3719014
2000 10042570
2001 1月 New England 509215
2001 1月 Mid-Atlantic 610697
2001 1月 Southeast US 379021
2001 1月 1498933
2001 2月 New England 615746
2001 2月 Mid-Atlantic 428676
2001 2月 Southeast US 618423
2001 2月 1662845
2001 3月 New England 566483
2001 3月 Mid-Atlantic 637031
2001 3月 Southeast US 655993
2001 3月 1859507
2001 5021285
15063855
可以看出,只要在rollup中增加或改变相关列的顺序,就能达到我们想要的小计和合计功能。
10.1.3部分ROLLUP
以上使用的rollup是完全的rollup查询,比如有n列,那么会生成n-1个小计,然后n-2个小计对n-1个,直到汇总合计。如果在实际查询中,有的小计或合计我们不需要,那么久要使用局部rollup,局部rollup就是将不需要小计(合计)的列放在group by中,而不是放在rollup中。
请看下面的查询:
SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
r.name region, SUM(o.tot_sales)
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
WHERE o.month BETWEEN 1 AND 3
GROUP BY o.year, ROLLUP (o.month, r.name);
这个相当于GROUP BY ROLLUP (o.year,o.month, r.name) 但是去掉了最后一行的汇总。
因为每次分组都有year。没有group by null。
再看这个查询:
SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
r.name region, SUM(o.tot_sales)
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
WHERE o.month BETWEEN 1 AND 3
GROUP BY o.year, o.month,ROLLUP (r.name);
这个相当于GROUP BY ROLLUP (o.year,o.month, r.name)去掉了对指定年的汇总和全量汇总。因为每次都有对year和month的分组,小计只有对年和月的小计,如下:
YEAR MONTH REGION SUM(o.tot_sales)
---------- ---------------- ---------------------------------------- ----------------------------------------------
2000 1月 New England 1018430
2000 1月 Mid-Atlantic 1221394
2000 1月 Southeast US 758042
2000 1月 2997866
2000 2月 New England 1231492
2000 2月 Mid-Atlantic 857352
2000 2月 Southeast US 1236846
2000 2月 3325690
2000 3月 New England 1132966
2000 3月 Mid-Atlantic 1274062
2000 3月 Southeast US 1311986
2000 3月 3719014
2001 1月 New England 509215
2001 1月 Mid-Atlantic 610697
2001 1月 Southeast US 379021
2001 1月 1498933
2001 2月 New England 615746
2001 2月 Mid-Atlantic 428676
2001 2月 Southeast US 618423
2001 2月 1662845
2001 3月 New England 566483
2001 3月 Mid-Atlantic 637031
2001 3月 Southeast US 655993
2001 3月 1859507
从Oracle8i开始,oracle提供了很多在一个查询中生成多级别汇总数据的特性,第10章我们研究的是对group by的扩展,关于扩展内容和版本对应关系如下表:
功能 版本
ROLLUP oracle8i
CUBE oracle8i
GROUPING SETS oracle9i
本节研究rollup,对cube和grouping sets稍后介绍。ROLLUP是对group by的扩展,因此,它只能出现在group by子句中,依赖于分组的列,对每个分组会生成汇总数据,如下:
SELECT ….
FROM ….
GROUP BY ROLLUP(C1,C2,C3….C(n-1),C(n));
总共会进行n+1个分组。
那么实际上有n+1个group by的union all结果。
第1个分组:全分组。C1,C2,C3….C(n-1),C(n)
第2个分组:C1,C2,C3….C(n-1);//这个分组实际上就是对前面前n-1列分组的小计.
----然后逐渐递减分组列
第n个分组:C1。对上一个分组的小计。
第n+1个分组。不分组全量汇总。相当于合计。也是对group by C1的小计。相当于group by null。
有了rollup,对10.1.1实现的union多级别汇总就可以使用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);
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
New England 19756923
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
Mid-Atlantic 18923298
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
Southeast US 20605485
59285706
从上面的结果可以看出,rollup和group by联合一起使用,达到了按group by列顺序分组,并且实现小计和合计的功能。黄色部分就是对上面组的小计,最后红色的就是合计。而且,rollup这种分组还是有序的,先全部分组,然后对每个分组小计,最后合计,使处理结果更清楚。union all就很难实现这种效果了。而且最重要的是性能比union all好,通过上面查询的执行计划可以看出:
rollup仅仅一个索引扫描region,一个全表扫描all_orders,然后通过nested loop,最后sort group by rollup就能得到结果。比union all多次扫描效率高很多。
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 ( o.month,r.name);
REGION MONTH SUM(O.TOT_SALES)
---------------------------------------- ---------------- ----------------
New England 1月 1527645
Mid-Atlantic 1月 1832091
Southeast US 1月 1137063
1月 4496799
New England 2月 1847238
Mid-Atlantic 2月 1286028
Southeast US 2月 1855269
2月 4988535
New England 3月 1699449
Mid-Atlantic 3月 1911093
Southeast US 3月 1967979
3月 5578521
New England 4月 1792866
Mid-Atlantic 4月 1623438
Southeast US 4月 1830051
4月 5246355
New England 5月 1698855
Mid-Atlantic 5月 1778805
Southeast US 5月 1983282
5月 5460942
New England 6月 1510062
Mid-Atlantic 6月 1504455
Southeast US 6月 1705716
6月 4720233
New England 7月 1678002
Mid-Atlantic 7月 1820742
Southeast US 7月 1670976
7月 5169720
New England 8月 1642968
Mid-Atlantic 8月 1381560
Southeast US 8月 1436295
8月 4460823
New England 9月 1726767
Mid-Atlantic 9月 1178694
Southeast US 9月 1905633
9月 4811094
New England 10月 1648944
Mid-Atlantic 10月 1530351
Southeast US 10月 1610523
10月 4789818
New England 11月 1384185
Mid-Atlantic 11月 1598667
Southeast US 11月 1661598
11月 4644450
New England 12月 1599942
Mid-Atlantic 12月 1477374
Southeast US 12月 1841100
12月 4918416
59285706
可以看出,达到了按月小计的功能,当然最后红色的合计值和按区域小计一致。
注意:
rollup中列的顺序不同,则统计的结果不同。因为它是按列从右递减分组的。
比如,我们需要统计第1个季度,并且年月汇总所有区域的数据,如下:
SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
r.name region, SUM(o.tot_sales) sums
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
WHERE o.month BETWEEN 1 AND 3
GROUP BY ROLLUP (o.year, o.month, r.name);
YEAR MONTH REGION SUMS
--------- ---------------- ---------------------------------------- ---------------------------------------------
2000 1月 New England 1018430
2000 1月 Mid-Atlantic 1221394
2000 1月 Southeast US 758042
2000 1月 2997866
2000 2月 New England 1231492
2000 2月 Mid-Atlantic 857352
2000 2月 Southeast US 1236846
2000 2月 3325690
2000 3月 New England 1132966
2000 3月 Mid-Atlantic 1274062
2000 3月 Southeast US 1311986
2000 3月 3719014
2000 10042570
2001 1月 New England 509215
2001 1月 Mid-Atlantic 610697
2001 1月 Southeast US 379021
2001 1月 1498933
2001 2月 New England 615746
2001 2月 Mid-Atlantic 428676
2001 2月 Southeast US 618423
2001 2月 1662845
2001 3月 New England 566483
2001 3月 Mid-Atlantic 637031
2001 3月 Southeast US 655993
2001 3月 1859507
2001 5021285
15063855
可以看出,只要在rollup中增加或改变相关列的顺序,就能达到我们想要的小计和合计功能。
10.1.3部分ROLLUP
以上使用的rollup是完全的rollup查询,比如有n列,那么会生成n-1个小计,然后n-2个小计对n-1个,直到汇总合计。如果在实际查询中,有的小计或合计我们不需要,那么久要使用局部rollup,局部rollup就是将不需要小计(合计)的列放在group by中,而不是放在rollup中。
请看下面的查询:
SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
r.name region, SUM(o.tot_sales)
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
WHERE o.month BETWEEN 1 AND 3
GROUP BY o.year, ROLLUP (o.month, r.name);
这个相当于GROUP BY ROLLUP (o.year,o.month, r.name) 但是去掉了最后一行的汇总。
因为每次分组都有year。没有group by null。
再看这个查询:
SELECT o.year, TO_CHAR(TO_DATE(o.month, 'MM'), 'Month') month,
r.name region, SUM(o.tot_sales)
FROM all_orders o JOIN region r
ON r.region_id = o.region_id
WHERE o.month BETWEEN 1 AND 3
GROUP BY o.year, o.month,ROLLUP (r.name);
这个相当于GROUP BY ROLLUP (o.year,o.month, r.name)去掉了对指定年的汇总和全量汇总。因为每次都有对year和month的分组,小计只有对年和月的小计,如下:
YEAR MONTH REGION SUM(o.tot_sales)
---------- ---------------- ---------------------------------------- ----------------------------------------------
2000 1月 New England 1018430
2000 1月 Mid-Atlantic 1221394
2000 1月 Southeast US 758042
2000 1月 2997866
2000 2月 New England 1231492
2000 2月 Mid-Atlantic 857352
2000 2月 Southeast US 1236846
2000 2月 3325690
2000 3月 New England 1132966
2000 3月 Mid-Atlantic 1274062
2000 3月 Southeast US 1311986
2000 3月 3719014
2001 1月 New England 509215
2001 1月 Mid-Atlantic 610697
2001 1月 Southeast US 379021
2001 1月 1498933
2001 2月 New England 615746
2001 2月 Mid-Atlantic 428676
2001 2月 Southeast US 618423
2001 2月 1662845
2001 3月 New England 566483
2001 3月 Mid-Atlantic 637031
2001 3月 Southeast US 655993
2001 3月 1859507
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24984814/viewspace-704462/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24984814/viewspace-704462/