《Pro Oracle SQL》CHAPTER 9 The Model Clause -- 9.6 Aggregation

Aggregation   聚合     (page 285)
    Data aggregation is commonly used in the data warehouse queries. The Model clause provides the
ability to aggregate the data using aggregate functions over the range of dimension columns. 
    数据聚合在数据仓库查询中很常用。Model子句提供使用聚合函数在维度列范围上聚合数据的能力。
    Many different aggregation function calls such as sum,  max,  avg,  stddev, and  OLAP function calls can be used to aggregate the data in a rule. It is easier to understand aggregation with an example.
    许多不同的聚合函数调用如sum，max，avg，stddev，以及OLAP函数调用能被用于在规则中聚合数据。通过实例较容易理解聚合。
     In Listing 9-16, the rule in lines 9 to 12 is calculating average inventory by Year using the clause
avg_inventory[year,ANY] = avg(inventory) [cv(year), week] . In the left hand side of the rule,
avg_invntory is the rule name. The first dimension in this rule is  Year  column .  As the dimension
clause is specifying the Week column as the second dimension, specifying ANY in the second position
of the rule argument matches with any value of week column including nulls. In the right hand side of
the rule, the clause  avg(inventory) applies the  avg  function on the Inventory column. The first
dimension is cv(year). The second dimension is specified as  week. There is no need for the use of CV in
the second dimension, as the function must be applied on all weeks in the year as computed by the
clause cv(year). 
    列表9-16中，行9到12的规则按Year计算平均库存，使用子句avg_inventory[year,ANY] = avg(inventory) [cv(year), week] 。在规则的左手边，avg_invntory是规则名。规则的第一维度是Year列。因为维度子句指定Week列是第二维度，在规则参数的第二个位置上指 定ANY匹配任意week列值包括nulls。 在规则的右手边，子句avg(inventory) 在Inventory列上应用avg函数。第一维度是cv(year)。第二维度指定的是week。没有必要在第二维度上使用CV，因为函数必须应用于一年中的所有周上，按子句cv(year)计算。
    Line 13 shows the use of avg. Line 14 shows an example of using the  max function. 
    行13展示了avg的使用。行14展示了一个使用max函数的例子。
Listing 9-16.  Aggregation
 1    select product, country, year, week, inventory, avg_inventory, max_sale
 2        from sales_fact
 3        where country in ('Australia') and product ='Xtend Memory'
 4        model return updated rows
 5        partition by (product, country)
 6        dimension by (year, week)
 7        measures ( 0 inventory ,0  avg_inventory , 0 max_sale, sale, receipts)
 8        rules automatic order(
 9             inventory [year, week ] =
10                                      nvl(inventory [cv(year), cv(week)-1 ] ,0)
11                                       - sale[cv(year), cv(week) ] +
12                                       + receipts [cv(year), cv(week) ],
13              avg_inventory [ year,ANY ] = avg (inventory) [ cv(year), week ],
14              max_Sale [ year, ANY ]     = max( sale) [ cv(year), week ] 
15        )
16*     order by product, country,year, week
 
PRODUCT      COUNTRY     YEAR WEEK  INVENTORY AVG_INVENTORY  MAX_SALE
------------ ---------- ----- ---- ---------- ------------- ---------
...
Xtend Memory Australia   2001   42     17.532         28.60    278.44
Xtend Memory Australia   2001   43     24.511         28.60    278.44
Xtend Memory Australia   2001   44     29.169         28.60    278.44
...
Xtend Memory Australia   2001   52    -22.931         28.60    278.44