Oracle 学习之 MODEL

Oracle 学习之 MODEL

官网学习教程

概念

千篇一律：

model语句是Oracle10g的新功能，可以在select语句里面像其他编程语言操作数组一样，对SQL的结果集进行处理。执行顺序是位于Having之后。

Oracle官网介绍：

对于某些应用，MODEL子句可以替换基于PC的电子表格。SQL中的模型利用了Oracle数据库在可伸缩性，可管理性，协作和安全性方面的优势。核心查询引擎可以处理无限数量的数据。通过在数据库中定义和执行模型，用户可以避免在独立的建模环境之间传输大型数据集。可以在工作组之间轻松共享模型，从而确保所有应用程序的计算结果都是一致的。正如可以共享模型一样，访问也可以通过Oracle的安全性功能进行精确控制。MODEL子句具有丰富的功能，可以增强所有类型的应用程序。 – 谷歌翻译而来，见谅。

自我理解：

MODEL 可以实现类似excel中 = 的功能，可以减少表的子连接(它也可以实现多表之间的连接)，可以直接在表字段后追加一个字段，用于输出按定义好的规则计算的值。

官网格式

MODEL
[<global reference options>]
[<reference models>]
[MAIN <main-name>]
  [PARTITION BY (<cols>)]
  DIMENSION BY (<cols>)
  MEASURES (<cols>)
  [<reference options>]
  [RULES]  <rule options>
  (<rule>, <rule>,.., <rule>)
  
  
  <global reference options> ::= <reference options> <ret-opt>
   <ret-opt> ::= RETURN {
  ALL|UPDATED} ROWS
  <reference options> ::=
  [IGNORE NAV | [KEEP NAV]
  [UNIQUE DIMENSION | UNIQUE SINGLE REFERENCE]
  <rule options> ::=
  [UPDATE | UPSERT | UPSERT ALL]
  [AUTOMATIC ORDER | SEQUENTIAL ORDER]
  [ITERATE (<number>)  [UNTIL <condition>]]
  <reference models> ::= REFERENCE ON <ref-name> ON (<query>)
  DIMENSION BY (<cols>) MEASURES (<cols>) <reference options>

整理后的格式：

MODEL
RETURN {
  ALL|UPDATED} ROWS
[IGNORE NAV | [KEEP NAV]
[UNIQUE DIMENSION | UNIQUE SINGLE REFERENCE]
REFERENCE ON <ref-name> ON (<query>)
DIMENSION BY (<cols>) MEASURES (<cols>) 
[IGNORE NAV | [KEEP NAV]
[UNIQUE DIMENSION | UNIQUE SINGLE REFERENCE]
[MAIN <main-name>]
[PARTITION BY (<cols>)]
DIMENSION BY (<cols>)
MEASURES (<cols>)
[ [IGNORE NAV | [KEEP NAV]
  [UNIQUE DIMENSION | UNIQUE SINGLE REFERENCE]]
[RULES]  
[UPDATE | UPSERT | UPSERT ALL]
[AUTOMATIC ORDER | SEQUENTIAL ORDER]
[ITERATE (<number>)  [UNTIL <condition>]]
(<rule>, <rule>,.., <rule>)

解析：

参考：oracle中MODEL子句的再探

1. MODEL： 关键字。

2. RETURN {ALL|UPDATED} ROWS：

• RETURN ALL ROWS： 返回符合的所有结果集，包括新生成的。
• RETURN UPDATED ROWS： 只返回数据有变化的，这里数据变化包括新增字段有值的。

3. [IGNORE NAV | [KEEP NAV]： 替换null|保留null，0替换数组，空格替换字符，01-JAN-2001 替换日期，null替换其他，默认是KEEP NAV 。
4. [UNIQUE DIMENSION | UNIQUE SINGLE REFERENCE]： 组内维度唯一|右侧引用唯一，默认是UNIQUE DIMENSION，右侧引用唯一的另一个区别是可能更新多个单元，因为左侧单元可能不唯一。
5. REFERENCE ON ON ()： 维度、指标定义，内嵌子MODEL， ref-name 为别名，主MODEL 使用别名引用，<query> 为select子句。
6. DIMENSION BY (<cols>) MEASURES (<cols>) <reference options>： 这部分是关联子model 的，<cols>为分组字段， MEASURES关键字， (<cols>) 表示规则中要使用到的字段。
7. [IGNORE NAV | [KEEP NAV]： 为子MODEL的限制条件，与主 MODLE 功能一样。
8. [UNIQUE DIMENSION | UNIQUE SINGLE REFERENCE]： 为子MODEL的限制条件，与主 MODLE 功能一样。
9. [MAIN <main-name>]： 当有引用子MODEL时，标示以下条件是主MODEL的条件。
10. [PARTITION BY (<cols>)]： 按参数中的列分组，之后的运算都是分组进行的；参数可以是表达式，但是必须有别名。
11. DIMENSION BY (<cols>)： 声明各分组中的维度字段，相当于多维键值对的建，字段值即键；参数可以是表达式，但是必须有别名。
12. MEASURES (<cols>)： 声明各分组中的指标字段，即键值对中的值；参数可以是表达式，但是必须有别名。
13. [RULES]： 关键字。
14. [UPDATE | UPSERT | UPSERT ALL]： 指标计算规则，更新|更新和简单插入|更新和复杂插入，默认是UPSERT 。
15. [AUTOMATIC ORDER | SEQUENTIAL ORDER]： 指标计算的顺序，逻辑依赖顺序|书写顺序，默认是SEQUENTIAL ORDER 。
16. [ITERATE (<number >) [UNTIL <condition >]]： 重复 number 次的计算，直到满足 condition 的条件退出。
17. (<rule>, <rule>,.., <rule>)： 具体的规则。

注意：

1. UPDATE 只更新已有单元，不存在则无效果。
2. UPSERT 在上面的基础上，如果左侧单元是以位置引用的话，则不存在就插入；FOR也是位置引用，但是更新的单元为FOR列表和其他维度的交叉乘集；左侧单元使用ANY引用，不会产生新单元，ANY的意思是IS NULL OR IS NOT NULL。
3. UPSERT ALL 在上面的基础上，左侧单元可以使用IN，ANY等谓词。

UPSERT的执行过程：
1、找出左侧单元的逻辑引用，谓词也是逻辑引用。
2、计算出逻辑引用的笛卡尔积集合。
3、和位置引用一起得出需要运算的单元集。
4、根据RULE规则更新或新增记录。

自己用到的简略版格式

MODEL --行间计算
PARTITION BY(分组字段1, 分组字段2, 分组字段3...) -- 分组
DIMENSION BY(维度字段1,维度字段2...) -- 维度
MEASURES(成员字段1，成员字段2...) --成员(包含新增的成员字段列)
IGNORE NAV --空值处理
RULES(成员字段[维度字段1,维度字段2...] = 成员字段[维度字段1,维度字段2...]

官网例程练习

我们已经按照例程创建好了视图，但是发现直接查询视图的速度并不快，因为普通视图会将语句转换成对应的查询语句，然后再执行。所以我们这里修改一下，创建物化视图。

set serveroutput on;
declare
    V_SALS NUMBER ;
begin
SELECT COUNT(1