About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc
全部博文(172)
分类: 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