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
分享到:
相关推荐
NULL 博文链接:https://caohong286.iteye.com/blog/1493368
NULL 博文链接:https://caohong286.iteye.com/blog/1488059
NULL 博文链接:https://caohong286.iteye.com/blog/1486738
NULL 博文链接:https://caohong286.iteye.com/blog/1485710
NULL 博文链接:https://caohong286.iteye.com/blog/1482168
NULL 博文链接:https://caohong286.iteye.com/blog/1504194
NULL 博文链接:https://caohong286.iteye.com/blog/1260817
Pro Oracle SQL unlocks the power of SQL in the Oracle Database—one of the most potent SQL implementations on the market today. To master it requires a three-pronged approach: learn the language ...
Walking the Tree: From the Top Down 5-9 Ranking Rows with the LEVEL Pseudocolumn 5-10 Formatting Hierarchical Reports Using LEVEL and LPAD 5-11 Pruning Branches 5-13 Summary 5-14 Practice 5 Overview 5...
Clause-view个人中心、设置常用的itemview
资源分类:Python库 所属语言:Python 资源全名:clause-1.1.2.tar.gz 资源来源:官方 安装方法:https://lanzao.blog.csdn.net/article/details/101784059
(b) the name of the table, the names of the table's attributes, the data types of the table's attributes, the formats of the table's attributes, and the maximum number of rows that the table can have...
Considered the best Oracle PL/SQL programming guide by the Oracle community, this definitive guide is precisely what you need to make the most of Oracle’s powerful procedural language. The sixth ...
Chatopera 语义理解系统:机器学习,聊天机器人,意图识别
运算符、表达式、Conditions、函数、Common sql ddl c、Clause
10g-including new features such as regular expressions and the MODEL SQL clause-as well as versions 8, 8i, and 9i <br>The authors are well-known as Oracle gurus-Greenwald is the author of Oracle ...
1000BASE-X IEEE 802.3-2008 Clause 36 - Physical Coding Sublayer (PCS)_latest.tar.gz
关于xeus-sql 主页: : 软件包许可证:BSD-3-Clause 原料许可证: 简介:基于xeus和SOCISQL的Jupyter内核文档: : 当前构建状态蔚蓝 变体状态linux_64 osx_64 win_64当前发行信息姓名资料下载版本平台类安装xeus-sql...