【SQL】Oracle的model用法简介（转载）
原文地址：
http://www.examda.com/oracle/zonghe/20110124/131213889.htmlModel语句是Oracle 10g的新功能之一。 本文通过一些简单的例子帮助理解Model语句的用法，复杂使用场景请参考其他文章。
环境：当然需要Oracle 10g以上，本人是在11g上测试的。
1. 什么是model语句
model语句是Oracle10g的新功能，可以在select语句里面像其他编程语言操作数组一样，对SQL的结果集进行处理。执行顺序是位于Having之后。
select的解析执行顺序
1. from语句
2. where语句 (结合条件)
3. start with语句
4. connect by语句
5. where语句
6. group by语句
7. having语句
8. model语句
9. select语句
10. union、minus、intersect等集合演算演算
11. order by语句
model的好处
Oracle 9i为止，需要使用各种计算分析函数，union all等，以及借助其他开发语言（C#及Java等）进行复杂计算统计合并等。使用Model之后，这些都可以在SQL里面进行了。
model典型使用场景。
合计行追加
行列变换
使用当前行的前后行
RegExp_Replace函数的循环执行
2. HelloWorld
先看一个简单的例子。
例句1
select ArrValue,soeji
from (select
'abcdefghijklmn' as ArrValue,
1 as
soeji from dual)
model
dimension by(soeji)
measures(ArrValue)
rules(ArrValue[1] = 'Hello World');
结果：
ArrValue
soeji
Hello World
1
说明：
model model语句的关键字，必须。
dimension by dimension维度的意思，可以理解为数组的索引，必须。
measures 指定作为数组的列
rules 对数组进行各种操作的描述。
例句1的理解：
select 'abcdefghijklmn' as ArrValue,
1 as soeji from dual;
上面子查询的结果：
ArrValue soeji
abcdefghijklmn
1
根据下面语句
model
dimension by (soeji)
measures(ArrValue)
soeji作为索引对数组ArrValue进行操作，rules(ArrValue[1] = 'Hello
World')就是说用Hello World覆盖ArrValue[1]里面的值。
在看一个例子，例句2：
select ArrValue,soeji
from (select 'abcdefghijklmn' as ArrValue,
1 as soeji from dual)
model
dimension by(soeji)
measures(ArrValue)
rules(ArrValue[1] = 'Hello World',
ArrValue[2] = 'Hello model');
执行结果：
ArrValue soeji
Hello World
1
Hello model
2
rules的缺省行为是存在就更新，不存在则追加，因此，ArrValue[1] = 'Hello World'是更新一条，ArrValue[2] = 'Hello Model'insert一条。
再看一个例子，例句3：
select ArrValue,soeji
from (select
'abcdefghijklmn' as ArrValue,
1 as
soeji from dual)
model
dimension by(soeji)
measures(ArrValue)
rules(ArrValue[3] = 'Hello Oracle');
结果是：
ArrValue soeji
abcdefghijklmn
1
Hello Oracle
3
model语句里面，索引可以是不连续的。 再看一个例子，例句4：
select ArrValue,soeji
from (select
'abcdefghijklmn' as ArrValue,
1 as
soeji from dual)
model return
updated rows
dimension by(soeji)
measures(ArrValue)
rules(ArrValue[4] = 'Hello CodeZine');
结果是：
ArrValue soeji
Hello CodeZine
4
使用model return updated rows的话，被rules更新或者插入的行才显示，没有更新过的行不再作为SQL的结果。
再来一个，有下表，希望在检索结果后面加一个合计行：
addTotal
ID
Val
1
30
2
100
3
50
4
300
select ID,Val from addTotal
model
dimension by(ID)
measures(Val)
rules(
Val[null] = Val[1]+Val[2]+Val[3]+Val[4]);
结果
ID
Val
1
30
2
100
3
50
4
300
null
480
不使用model的话可以使用rollup，union
all等实现。
rollup方式：
select ID,sum(Val) as Val
from addTotal
group by rollup(ID);union all方式：
select ID,Val
from addTotal
union all
select null,Sum(Val)
from addTotal;
再转载一篇文章：
原文地址：http://blog.csdn.net/wh62592855/article/details/4783021
ORACLE 10G 中新增的 MODEL 子句可以用来进行行间计算。 MODEL 子句允许像访问数组中元素那样访问记录中的某个列。这就提供了诸如电子表格计算之类的计算能力。
1 、 MODEL 子句示例
下面这个查询获取 2003 年内由员工 #21 完成的产品类型为 #1 和 #2 的销量，并根据 2003 年的销售数据预测出 2004 年 1 月、 2 月、 3 月的销量。
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[1,2004]=sales_amount[1,2003],
Sales_amount[2,2004]=sales_amount[2,2003] + sales_amount[3,2003],
Sales_amount[3,2004]=ROUND(sales_amount[3,2003]*1.25,2)
)
Order by prd_type_id,year,month;
现在小分析一下上面这个查询：
*.
partition by (prd_type_id) 指定结果是根据 prd_type_id 分区的。
*.
dimension by (month,year) 定义数组的维度是 month 和 year 。这就意味着必须提供月份和年份才能访问数组中的单元。
*.
measures (amount sales_amount) 表明数组中的每个单元包含一个数量，同时表明数组名为 sales_amount 。
*.
MEASURES 之后的三行命令分别预测 2004 年 1 月、 2 月、 3 月的销量。
*.
Order by prd_type_id,year,month 仅仅是设置整个查询返回结果的顺序。
上面这个查询的输出结果如下：
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 1 10034.84
1 2003 2 15144.65
1 2003 3 20137.83
1 2003 4 25057.45
1 2003 5 17214.56
1 2003 6 15564.64
1 2003 7 12654.84
1 2003 8 17434.82
1 2003 9 19854.57
1 2003 10 21754.19
1 2003 11 13029.73
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 12 10034.84
1 2004 1 10034.84
1 2004 2 35282.48
1 2004 3 25172.29
2 2003 1 1034.84
2 2003 2 1544.65
2 2003 3 2037.83
2 2003 4 2557.45
2 2003 5 1714.56
2 2003 6 1564.64
2 2003 7 1264.84
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
2 2003 8 1734.82
2 2003 9 1854.57
2 2003 10 2754.19
2 2003 11 1329.73
2 2003 12 1034.84
2 2004 1 1034.84
2 2004 2 3582.48
2 2004 3 2547.29
30 rows selected.
2 、用位置标记和符号标记访问数据单元
前面的例子已经介绍了如何使用位置标记来访问数组中的某个单元。还可以使用符号标记显式指定维度的含义。例如， sales_amount[month=1,year=2004] 。下面这个查询用符号标记重写了前面的查询。
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[month=1,year=2004]=sales_amount[month=1, year=2003],
Sales_amount[month=2, year=2004]=sales_amount[month=2, year=2003] + sales_amount[month=3,year=2003],
Sales_amount[month=3, year=2004]=ROUND(sales_amount[month=3, year=2003]*1.25,2)
)
Order by prd_type_id,year,month;
使用位置标记或符号标记之间有一个区别需要了解，即它们处理维度中空值的方式不同。例如， sales_amount[null,2003] 返回月份为空值、年份为 2003 的销量，而 sales_amount[month=null,year=2004] 则不会访问任何有效的数据单元，因为 null=null 的返回值总是 false 。
3 、用 BETWEEN 和 AND 返回特定范围内的数据单元
BETWEEN 和 AND 关键字可用于访问一段范围内的数据单元。例如，下面这个表达式将 2004 年 1 月的销量设置为 2003 年 1 月至 3 月销量的平均值取整：
Sales_amount[1,2004]=ROUND(AVG(sales_amount)[month between 1 and 3,2003],2)
下面这个查询展示了上述表达式的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[1,2004]=ROUND(AVG(sales_amount)[month between 1 and 3,2003],2)
)
Order by prd_type_id,year,month;
结果如下：
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 1 10034.84
1 2003 2 15144.65
1 2003 3 20137.83
1 2003 4 25057.45
1 2003 5 17214.56
1 2003 6 15564.64
1 2003 7 12654.84
1 2003 8 17434.82
1 2003 9 19854.57
1 2003 10 21754.19
1 2003 11 13029.73
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 12 10034.84
1 2004 1 15105.77
2 2003 1 1034.84
2 2003 2 1544.65
2 2003 3 2037.83
2 2003 4 2557.45
2 2003 5 1714.56
2 2003 6 1564.64
2 2003 7 1264.84
2 2003 8 1734.82
2 2003 9 1854.57
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
2 2003 10 2754.19
2 2003 11 1329.73
2 2003 12 1034.84
2 2004 1 1539.11
26 rows selected.
4 、用 ANY 和 IS ANY 访问所有的数据单元
可以用 ANY 和 IS ANY 谓词访问数组中所有的数据单元。 ANY 和位置标记合用， IS ANY 和符号标记合用。例如，下面这个表达式将 2004 年 1 月的销量设置为所有年份月份的销量之和取整：
Sales_amount[1,2004]=ROUND(SUM(sales_amount)[ANY,year IS ANY],2)
下面这个查询展示了上述表达式的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[1,2004]=ROUND(SUM(sales_amount)[ANY,year IS ANY],2)
)
Order by prd_type_id,year,month;
结果如下：
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 1 10034.84
1 2003 2 15144.65
1 2003 3 20137.83
1 2003 4 25057.45
1 2003 5 17214.56
1 2003 6 15564.64
1 2003 7 12654.84
1 2003 8 17434.82
1 2003 9 19854.57
1 2003 10 21754.19
1 2003 11 13029.73
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 12 10034.84
1 2004 1 197916.96
2 2003 1 1034.84
2 2003 2 1544.65
2 2003 3 2037.83
2 2003 4 2557.45
2 2003 5 1714.56
2 2003 6 1564.64
2 2003 7 1264.84
2 2003 8 1734.82
2 2003 9 1854.57
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
2 2003 10 2754.19
2 2003 11 1329.73
2 2003 12 1034.84
2 2004 1 20426.96
26 rows selected.
5 、用 CURRENTV() 获取某个维度的当前值
CURRENTV() 函数用于获得某个维度的当前值。例如，下面的表达式将 2004 年第一个月的销量设置为 2003 年同月销量的 1.25 倍。注意此处用 CURRENTV() 获得当前月份，其值为 1
Sales_amount[1,2004]=ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
下面这个查询展示了上述表达式的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[1,2004]=ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
)
Order by prd_type_id,year,month;
运行结果如下：
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 1 10034.84
1 2003 2 15144.65
1 2003 3 20137.83
1 2003 4 25057.45
1 2003 5 17214.56
1 2003 6 15564.64
1 2003 7 12654.84
1 2003 8 17434.82
1 2003 9 19854.57
1 2003 10 21754.19
1 2003 11 13029.73
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 12 10034.84
1 2004 1 12543.55
2 2003 1 1034.84
2 2003 2 1544.65
2 2003 3 2037.83
2 2003 4 2557.45
2 2003 5 1714.56
2 2003 6 1564.64
2 2003 7 1264.84
2 2003 8 1734.82
2 2003 9 1854.57
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
2 2003 10 2754.19
2 2003 11 1329.73
2 2003 12 1034.84
2 2004 1 1293.55
26 rows selected.
6 、用 FOR 循环访问数据单元
可以通过 FOR 循环访问数据单元。例如，下面这个表达式将 2004 年前三个月的销量设置为 2003 年相应月份销量的 1.25 倍。注意其中使用了 FOR 循环，还通过 INCREMENT 关键字定义每一次循环迭代中 month 的增量：
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
下面这个和查询语句展示了上述表达式的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
)
Order by prd_type_id,year,month;
运行结果如下：
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 1 10034.84
1 2003 2 15144.65
1 2003 3 20137.83
1 2003 4 25057.45
1 2003 5 17214.56
1 2003 6 15564.64
1 2003 7 12654.84
1 2003 8 17434.82
1 2003 9 19854.57
1 2003 10 21754.19
1 2003 11 13029.73
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 12 10034.84
1 2004 1 12543.55
1 2004 2 18930.81
1 2004 3 25172.29
2 2003 1 1034.84
2 2003 2 1544.65
2 2003 3 2037.83
2 2003 4 2557.45
2 2003 5 1714.56
2 2003 6 1564.64
2 2003 7 1264.84
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
2 2003 8 1734.82
2 2003 9 1854.57
2 2003 10 2754.19
2 2003 11 1329.73
2 2003 12 1034.84
2 2004 1 1293.55
2 2004 2 1930.81
2 2004 3 2547.29
30 rows selected.
7 、处理空值和缺失值
1 ）使用 IS PRESENT
当数据单元指定的记录在 MODEL 子句执行之前存在，则 IS PRESENT 返回 TRUE 。例如：
Sales_amount[CURRENTV(),2003] IS PRESENT
如果 Sales_amount[CURRENTV(),2003] 存在，则返回 TRUE 。
下面的表达式将 2004 年前三个月的销量设置为 2003 年同期销量的 1.25 倍：
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
CASE WHEN Sales_amount[CURRENTV(),2003] IS PRESENT THEN
ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
ELSE
0
END
下面这个查询展示了上述表达式的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
CASE WHEN Sales_amount[CURRENTV(),2003] IS PRESENT THEN
ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
ELSE
0
END
)
Order by prd_type_id,year,month;
2 ）使用 PRESENTV()
如果 cell 引用的记录在 MODEL 子句执行以前就存在，那么 PRESENTV(cell,expr1,expr2) 返回表达式 expr1 。如果这条记录不存在，则返回表达式 expr2 。例如：
PRESENTV(sales_amount[CURRENTV(),2003],
ROUND(sales_amount[CURRENTV(),2003]*1.25,2),0)
如果 sales_amount[CURRENTV(),2003] 存在，上面的表达式返回取整后的销量；否则，返回 0. 下面这个查询展示了上述表达式的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
PRESENTV(sales_amount[CURRENTV(),2003],
ROUND(sales_amount[CURRENTV(),2003]*1.25,2),0)
)
Order by prd_type_id,year,month;
3 ）使用 PRESENTNNV()
如果 cell 引用的单元在 MODEL 子句执行之前已经存在，并且该单元的值不为空，则 PRESENTNNV(cell,expr1,expr2) 返回表达式 expr1 。如果记录不存在，或单元值为空值，则返回表达式 expr2 。例如：
PRESENTNNV(sales_amount[CURRENTV(),2003],
ROUND(sales_amount[CURRENTV(),2003]*1.25,2),0)
如果 sales_amount[CURRENTV(),2003] 存在且为非空值，那么上面的表达式将返回取整后的销量；否则返回 0 。
4 ）使用 IGNORE NAV 和 KEEP NAV
IGNORE NAV 的返回值如下：
*.
空值或缺失数字值时返回 0 。
*.
空值或缺失字符串值时返回空字符串。
*.
空值或缺失日期值时返回 01-JAN-2000 。
*.
其他所有数据库类型时返回空值。
KEEP NAV 对空值或缺失数字值返回空值。注意默认条件下使用 KEEP NAV 。
下面这个查询展示了 IGNORE NAV 的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model IGNORE NAV
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
)
Order by prd_type_id,year,month;
8 、更新已有的单元
默认情况下，如果表达式左端的引用单元存在，则更新该单元。如果该单元不存在，就在数组中创建一条新的记录。可以用 RULESUPDATE 改变这种默认的行为，指出在单元不存在的情况下不创建新纪录。
下面这个查询展示了 RULES UPDATE 的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model IGNORE NAV
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
RULES UPDATE
(
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
)
Order by prd_type_id,year,month;
运行结果如下：
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 1 10034.84
1 2003 2 15144.65
1 2003 3 20137.83
1 2003 4 25057.45
1 2003 5 17214.56
1 2003 6 15564.64
1 2003 7 12654.84
1 2003 8 17434.82
1 2003 9 19854.57
1 2003 10 21754.19
1 2003 11 13029.73
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 12 10034.84
2 2003 1 1034.84
2 2003 2 1544.65
2 2003 3 2037.83
2 2003 4 2557.45
2 2003 5 1714.56
2 2003 6 1564.64
2 2003 7 1264.84
2 2003 8 1734.82
2 2003 9 1854.57
2 2003 10 2754.19
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
2 2003 11 1329.73
2 2003 12 1034.84
24 rows selected.
可以看到，虽然 2004 年的单元不存在，可是同时指定了 RULES UPDATE ，所以不会在数组中为 2004 年创建新纪录，因此这条查询语句不会返回 2004 年的记录。
以上。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/20335819/viewspace-710727/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
SQL For Modeling
请登录后发表评论
登录
全部评论
