《Pro Oracle SQL》Chapter7 -- 7.7Extending Reports with GROUPING()

7.7Extending Reports with GROUPING()  用GROUPING()函数扩展报告    (page209)
    Another use of GROUPING() is in the HAVING clause, where it can be used to control which aggregation levels appear in the output. The report seen in previous examples creates about five pages of output, which may be more than the customer cares to see.   By using the GROUPING() function, these aggregations can be condensed to roll up the totals for either or all of the columns used in the CUBE extension.   Several variations of GROUPING() have been used to modify the previous SQL. The modifications and resulting output are shown in Listing 7-14. 
    GROUPING()函数的另一种用法是在HAVING子句中运用，在那里(运用可用)来控制哪个层次的聚合出现在输出中。 在之前例子报告中，创建了大约5页的输出，可能比起客户关心的数据要多一些。通过使用GROUPING()函数，(可控制)哪些聚合行能被浓缩( 扼要 ) ，对CUBE扩展式中出现的一列或多列进行累计求和。 把GROUPING()函数的几个变换应用到对之前的SQL的修改中。修改和结果输出都展示在列表7-14当中了。
    Examining the data in Listing 7-14 you can see that applying GROUPING() to the CUST_INCOME_LEVEL column created aggregates from all AGE_RANGE values to be accumulated across all income levels.  Doing so for the AGE_RANGE column had similar effects, with totals aggregated for all values of INCOME_LEVEL without regard to the value of AGE_RANGE.  Including all of the columns from the CUBE extension as arguments to the GROUPING() function will cause the aggregations to be condensed to a single row, similar to what could be done with SUM(PROFIT) and a simple GROUP BY PROD_CATEGORY. Using the CUBE extension, however, allows simple changes to the HAVING clause to create several different reports.
    检查列表7-14的数据你可以发现,把GROUP()函数应用于CUST_INCOME_LEVEL列，将创建跨越所有收入层级的所有 AGE_RANGE值的聚合。同样应用于AGE_RANGE列，也会有相似的效果，将创建不论年龄范围聚合所有INCOME_LEVEL值的总数。若GROUPING()函数包含CUBE扩展的所有列，将使得聚合浓缩成一行。与一个简单的GROUP BY PROD_CATEGORY,然后SUM(PROFIT)有异曲同工之效。 然而，使用CUBE扩展，允许简单的改变HAVING子句来创建好些不同的报告。
Listing 7-14.  GROUPING() in the HAVING Clause         HAVING子句中的GROUPING()
CUST_INCOME_LEVEL
 
35  group by prod_category, cube(cust_income_level,age_range)
36    having grouping(cust_income_level)=1
 
QUERY                                                      AGE
TAG    PRODUCT CATEGORY               INCOME LEVEL         RANGE             PROFIT
------ ------------------------------ -------------------- -------- ---------------
Q3     Hardware                       ALL INCOME           60-70         $85,314.04
Q3     Hardware                       ALL INCOME           10-20         $90,849.87
Q3     Hardware                       ALL INCOME           0-10          $92,207.47
... 
Q4     Hardware                       ALL INCOME           ALL AGE      $987,386.78
AGE_RANGE
35  group by prod_category, cube(cust_income_level,age_range)
36    having grouping(age_range)=1
QUERY                                                      AGE
TAG    PRODUCT CATEGORY               INCOME LEVEL         RANGE             PROFIT
------ ------------------------------ -------------------- -------- ---------------
Q2     Hardware                       K: 250,000 - 299,999 ALL AGE       $26,678.00
Q2     Hardware                       L: 300,000 and above ALL AGE       $28,974.28
Q2     Hardware                       J: 190,000 - 249,999 ALL AGE       $43,761.47
...
Q4     Hardware                       ALL INCOME           ALL AGE      $987,386.78
CUST_INCOME_LEVEL, AGE_RANGE
35  group by prod_category, cube(cust_income_level,age_range)
36    having grouping(cust_income_level)=1 and grouping(age_range)=1 
QUERY                                                      AGE
TAG    PRODUCT CATEGORY               INCOME LEVEL         RANGE             PROFIT
------ ------------------------------ -------------------- -------- ---------------
Q4     Electronics                              ALL INCOME           ALL AGE      $838,994.19
Q4     Hardware                                ALL INCOME           ALL AGE      $987,386.78
Q4     Peripherals and Accessories        ALL INCOME           ALL AGE      $1,751,079.16
Q4     Photo                                     ALL INCOME           ALL AGE      $1,570,866.04
Q4     Software/Other                        ALL INCOME           ALL AGE      $873,603.25