ORACLE 10G & SQL MODLE
10G 技术文档中对MODLE的描述
概述
利用 SQL MODEL 子句，您可以根据查询结果定义多维数组，然后将规则应用于该数组以计算新值。这些规则可以是复杂的相互依赖的计算。与外部解决方案相比，通过将高级计算集 成到数据库中，可以大幅度提升性能、可伸缩性以及可管理性。用户可以将数据保留在 Oracle 环境内，而无需将数据复制到单独的应用程序或 PC 电子表格中。
MODEL 子句通过将查询列映射到以下三组来定义多维数组：分区列、维度列和度量列。这些元素执行以下任务：
 | 分区以类似于分析函数的分区方式（在数据仓库指南中标题为“数据仓库中用于分析的 SQL”的一章中有述）来定义结果集的逻辑块。将 MODEL 规则应用于每分区的单元格。 |
| 维度用于标识分区内的每个度量单元格。这些列用于标识日期、区域以及产品名之类的特征。 |
| 度量类似于星型模式中事实表的度量。它们通常包含数值，例如销售单位或成本。通过指定每个单元格的完整维度组合，可以在单元格所处的分区内对其进行访问。 |
要针对这些多维数组创建规则，您需要定义以维度值形式表达的计算规则。规则灵活且简洁，并且可以使用通配符和 FOR 循环，以最大限度地表达您的意图。利用 MODEL 子句构建的计算通过将分析集成到数据库中改善了传统的电子表格计算，通过符号引用提高了可读性，并提供了可伸缩性和更好的可管理性。
测试案例:
按月份得到quantity的累加值
SQL> with tmp as (
2 select 'Jan' Month,100 Quantity from dual
3 union
4 select 'Feb',200 from dual
5 union
6 select 'Mar',340 from dual
7 union
8 select 'Apr',200 from dual
9 union
10 select 'May',250 from dual
11 ) select Month,Quantity_count from
12 tmp
13 model ignore nav /* &keep nav*/
14 partition by ('X' as x)
15 dimension by (Month)
16 measures (Quantity as Quantity_count)
17 rules
18 (Quantity_count['Jan']=Quantity_count['Jan'],
19 Quantity_count['Feb']=Quantity_count['Jan']+Quantity_count['Feb'],
20 Quantity_count['Mar']=Quantity_count['Feb']+Quantity_count['Mar'],
21 Quantity_count['Apr']=Quantity_count['Mar']+Quantity_count['Apr'],
22 Quantity_count['May']=Quantity_count['Apr']+Quantity_count['May']
23 )
24 /
MONTH QUANTITY_COUNT
----- --------------
Apr 840
Feb 300
Jan 100
Mar 640
May 1090
SQL>
2 select 'Jan' Month,100 Quantity from dual
3 union
4 select 'Feb',200 from dual
5 union
6 select 'Mar',340 from dual
7 union
8 select 'Apr',200 from dual
9 union
10 select 'May',250 from dual
11 ) select Month,Quantity_count from
12 tmp
13 model ignore nav /* &keep nav*/
14 partition by ('X' as x)
15 dimension by (Month)
16 measures (Quantity as Quantity_count)
17 rules
18 (Quantity_count['Jan']=Quantity_count['Jan'],
19 Quantity_count['Feb']=Quantity_count['Jan']+Quantity_count['Feb'],
20 Quantity_count['Mar']=Quantity_count['Feb']+Quantity_count['Mar'],
21 Quantity_count['Apr']=Quantity_count['Mar']+Quantity_count['Apr'],
22 Quantity_count['May']=Quantity_count['Apr']+Quantity_count['May']
23 )
24 /
MONTH QUANTITY_COUNT
----- --------------
Apr 840
Feb 300
Jan 100
Mar 640
May 1090
SQL>
同样累加值也可以通过下面语句得到
SQL>
SQL> with tmp as (
2 select '1' Month,100 Quantity from dual
3 union
4 select '2',200 from dual
5 union
6 select '3',340 from dual
7 union
8 select '4',200 from dual
9 union
10 select '5',250 from dual
11 )select t1.month,sum(t2.quantity) Quantity_count
12 from tmp t1 left join tmp t2
13 on t2.month<=t1.month
14 group by t1.month
15 order by 1
16 /
MONTH QUANTITY_COUNT
----- --------------
1 100
2 300
3 640
4 840
5 1090
SQL>
SQL> with tmp as (
2 select '1' Month,100 Quantity from dual
3 union
4 select '2',200 from dual
5 union
6 select '3',340 from dual
7 union
8 select '4',200 from dual
9 union
10 select '5',250 from dual
11 )select t1.month,sum(t2.quantity) Quantity_count
12 from tmp t1 left join tmp t2
13 on t2.month<=t1.month
14 group by t1.month
15 order by 1
16 /
MONTH QUANTITY_COUNT
----- --------------
1 100
2 300
3 640
4 840
5 1090
SQL>
其中指定ignore nav&keep nav处理 NULL 度量和缺失单元格
更多说明及案例请参考ORACLE官方说明
http://www.oracle.com/technology/global/cn/obe/10gr2_db_single/bidw/sqlmodel/sqlmodel_otn.htm#t8
