About me:Oracle ACE,optimistic,passionate and harmonious. Focus on oracle programming,peformance tuning,db design, j2ee,Linux/AIX,web2.0 tech,etc
全部博文（148）
分类： Oracle
2020-06-28 17:05:49
背景介绍：有些很复杂的计算难以用单个SQL实现，诸如预测销售、计算市场占有率、解决方程问题、分析时间序列以及递归计算等问题。这些问题的计算，经常需要跨越多个维度，这类问题就算用单个SQL能解决，也很复杂，难于理解，经常使用多个子查询，join,union等，因此，性能经常很差。
一般，对这类问题用SQL很难描述，经常将数据导到表格中处理(必须EXCEL,根据EXCEL相关公式计算)或者导入到外部程序中处理，但是性能，可管理型，数据安全等都无法保证。
Oracle 10G为了解决这类问题，引入了新的Model查询功能，可以对来处理，然后将规则应用于这些数组来计算新值，这些规则可以是复杂的相互依赖的计算。与外部解决方案相比，通过将高级计算集成到数据库中，可以大幅度提升性能、可伸缩性以及可管理性。用户可以将数据保留在 Oracle 环境内，而无需将数据复制到单独的应用程序或 PC 电子表格中。
MODEL就是行间计算，可以对某些行跨越某些维度按指定的公式计算新的单元格值，和扩展GROUP BY有所不同，扩展GROUP BY目的是多维度分组，然后生成小计和合计，而MODEL是行间计算，根据规则和公式生成有行间关系的单元格值。
用MODEL查询，如果清楚MODEL查询的语法结构，那么SQL就通俗易懂了，比复杂的join,子查询，union all可读性更强，而且效率更高，常用于数据仓库报表中。
首先，通过一个简单的例子了解下Model查询的基本元素。sales_history表存储历史销售情况，对应表结构如下：
|
区域编号 |
REGION_ID |
NUMBER |
|
年份YYYY |
YEAR |
NUMBER |
|
月份 |
MONTH |
NUMBER |
|
销售额 |
SALES |
NUMBER |
这个表中存储了对应区域的2000年和2001年对应月份的销售额情况，这里举例子，区域有5,6,7三个，那么对每个区域来说总共有24条数据。由以上基础数据，来预测2004年对应区域1，2，3月份的销售额，使用简单的公式是2004年的对应区域对应月的销售额为2000年和2001年对应区域对应月销售额的平均值，公式如下：
sales_2004
= (sales_2000+sales_2001)/2
在Oracle 10g中使用Model查询很容易获得以上结果，如下：
SELECT r, y, m, s
FROM sales_history
WHERE month <= 3
--MODEL子句开始
MODEL
--返回更新的行还是所有行
RETURN UPDATED ROWS
--分区生成多维数组
PARTITION BY (region_id r)
--指定多维数组的维度
DIMENSION BY (year y, month m)
--指定需要做计算的列
MEASURES (sales s)
--指定需要做计算的列的计算方式
RULES (s[2004, FOR m in (1,2,3)] = (s[2000,CV( )] + s[2001,CV( )]) / 2)
ORDER BY y, r, m;
黄色部分为Model查询的基本组成部分。
上面的查询结果为：
R Y M S
---------- ---------- ---------- ----------
5 2000 1 1018430
5 2000 2 1231492
5 2000 3 1132966
6 2000 1 1221394
6 2000 2 857352
6 2000 3 1274062
7 2000 1 758042
7 2000 2 1236846
7 2000 3 1311986
5 2001 1 509215
5 2001 2 615746
5 2001 3 566483
6 2001 1 610697
6 2001 2 428676
6 2001 3 637031
7 2001 1 379021
7 2001 2 618423
7 2001 3 655993
5 2004 1 763822.5
5 2004 2 923619
5 2004 3 849724.5
6 2004 1 916045.5
6 2004 2 643014
6 2004 3 955546.5
7 2004 1 568531.5
7 2004 2 927634.5
7 2004 3 983989.5
已选择27行。
上面的结果相同于根据原有的行，根据相关规则生成了更新的多余结果，黄色部分就是应用的规则生成的值：比如黄色的预测销售额=红色销售额和/2。如果没有RETURN UPDATED ROWS，那么结果只会生成2004年的预测结果，如果加了就包含所有原始数据以及计算结果，这个结果就类似于将原始数据放到Excel表格中，根据Excel公式生成了一些新行。
上面的查询就是Model查询，下面介绍此查询的一些关键词：MODEL,PARTITION
BY,DIMENSION BY,MEASURES,以及RULES。
Model: 子句的开始，Model子句允许将关系性数据看作多维数组，这个多维数组就是一个MODEL。当你一旦将关系型数据放到多维数组中，就允许对这些数据像电子表格一样计算，比如应用一些公式计算等。
Partition by(分区):Partition by子句定义了MODEL的逻辑块。可以想象partition就是将数据分到多个MODEL中，每个MODEL都有相同的结构，但是是按partition分离的数据子集。这个partition和分析函数中的分区很相似。当对各自的分区MODEL单元格应用相关计算后，会对各自的MODEL生成结果集，每个分区计算是独立的。（这里的partition by要有括号，和分析函数不同）
Dimension by(维度): Dimension by子句指定了由Model子句建立的多维数组的维度。在一个指定分区的多维数组中，dimension by的列唯一指定一个单元格。model查询中的维度和星型查询中的维度是一致的。在上面的例子中，说明以year和month来划分维度，每个分区都是二维数组，在每个分区中，year和month确定了一个单元格。
Measures(度量): Measures字句指定的列就是要做计算的列。和星查询中的事实表度量一致。上面的例子中，测量的就是sales列，也就是对sales列进行相关规则的计算。
每个cell（单元格）的值就是由measures子句指定的。上面的例子每个单元格只有一个值，后面将说到一个单元格可以包含多个值。
Rules(规则):Rules关键字就是指定了需要做的动作的公式。详细见19.3。
Return Updated Rows:指定最后只显示根据原有模型的计算结果，不包括原有模型的数据，如果去掉此句，那么上面的查询将2000年和2001年的数据也显示出来(原始数据，而不是进过计算的)。
注意：
MODEL语句执行顺序：MODEL子句在同一层SQL里，它是在SELECT和ORDERBY之前运行的，在其他之后运行的（和分析函数很类似），在有MODEL查询的同一层sql的SELECT和ORDERBY里不能有分析函数。
MODEL中的别名规则：MODEL查询中的SELECT，如果在MODEL查询中使用了别名，那么SELECT也必须要使用这个别名，ORDERBY同样，当然SELECT也可以重新取别名，然后ORDERBY中可以引用SELECT中重新取的别名，ORDERBY也可以用原来的，含义有所不同。因为select和order by在model之后执行，但是where中没有使用别名，因为在model之前执行，类似地也要考虑这些。当然select 我们也可以对别名再取个别名。
未完待续，见PART2：http://blog.chinaunix.net/uid-7655508-id-5835051.html
