model子句提供了一种很好替代电子表格的方法，通过model子句，可以使用SQL语句的一些很强大的功能

如聚合，并行，以及多维多变量分析。我们通过使用EXCEL提供的电子表格进行公式计算，但在实际中，如果数据量

较大，且要实现智能计算的时候，model子句是一个很好的选择。

下面我们逐一分析讨论model子句。

一、示例表创建

SQL> create table sales_fact
  2  as
  3  select country_name country,country_subregion region,
  4         prod_name product,calendar_year year,
  5         calendar_week_number weeks,sum(amount_sold) sale,
  6         sum(amount_sold*
  7                         (case
  8                              when mod(rownum,10) = 0 then
  9                                   1.4
 10                              when mod(rownum,5) = 0 then
 11                                   0.6
 12                              when mod(rownum,2) = 0 then
 13                                   0.9
 14                              when mod(rownum,2) = 1 then
 15                                   1.2
 16                              else
 17                                   1
 18                           end)
 19              )receipts
 20  from sales s,times t,customers ct,countries cy,products p
 21  where s.time_id = t.time_id
 22    and s.cust_id = ct.cust_id
 23    and s.prod_id = p.prod_id
 24    and ct.country_id = cy.country_id
 25  group by country_name,country_subregion,prod_name,
 26           calendar_year,calendar_week_number
 27  /

表已创建。

SQL>

 

二、总看model子句

SQL> select product,country,year,weeks,receipts,sale,inventory
  2  from sales_fact
  3  where country = 'Australia'
  4    and product = 'Xtend Memory'
  5    and year = '2001'
  6    and weeks <=10
  7  model return updated rows
  8  partition by (product,country)
  9  dimension by (year,weeks)
 10  measures(0 inventory,receipts,sale)
 11  rules automatic order
 12  (
 13  inventory[year,weeks] = nvl(inventory[cv(year),cv(weeks)-1],0)
 14                        + receipts[cv(year),cv(weeks)]
 15                        - sale[cv(year),cv(weeks)]
 16  )
 17  order by weeks,receipts,sale
 18  /

PRODUCT
--------------------------------------------------------------------------------
COUNTRY
--------------------------------------------------------------------------------
      YEAR      WEEKS   RECEIPTS       SALE  INVENTORY
---------- ---------- ---------- ---------- ----------
Xtend Memory
Australia
      2001          1     96.894      92.26      4.634

Xtend Memory
Australia
      2001          2    134.928     118.38     21.182

Xtend Memory
Australia
      2001          3     61.412      47.24     35.354

Xtend Memory
Australia
      2001          4     245.64      256.7     24.294

Xtend Memory
Australia
      2001          5     95.906      93.44      26.76

Xtend Memory
Australia
      2001          6     20.196      22.44     24.516

Xtend Memory
Australia
      2001          7     62.964      69.96      17.52

Xtend Memory
Australia
      2001          8     48.186      46.06     19.646

Xtend Memory
Australia
      2001          9     95.008      92.67     21.984

Xtend Memory
Australia
      2001         10     75.717      69.05     28.651

已选择10行。

SQL>

说明：大家可以把model子句想像为一张EXCEL电子表格。第7行中使用model return updated rows
          申明model子句，第8行使用partition by 子句指定分区列，分区类似于电子表中的一张工作表；

         第9行申明维度值，维度值类似于行标签(1,2,3.......)和列标签(A,B,C......)。第10句measures指定度量值列

        inventory,receipts,sale，度量值类于单元格。第11行到第16行指定规则，规则类似于计算公式。

        cv函数提供了一个引用单元格的能力，表示从规则左侧计算得来规则右侧的值。如cv(year)，指的是规则左侧year的值。

 

二、model子句中的位置标记与符号标记

1、位置标记

    位置标记就是提供了结果集中的单元格操作。如果单元格存在，则更新单元格，如果不存在，则添加单元格。位置标记可以理解为

    单元格在结果集中的保存。如下：

SQL> select product,country,year,weeks,receipts,sale,inventory
  2  from sales_fact
  3  where country = 'Australia'
  4    and product = 'Xtend Memory'
  5    and year in('2001','2002')
  6    and weeks <=5
  7  model return updated rows
  8  partition by (product,country)
  9  dimension by (year,weeks)
 10  measures (0 inventory,receipts,sale)
 11  rules automatic order
 12  (
 13  inventory[year,weeks] = nvl(inventory[cv(year),cv(weeks)-1],0)
 14                        + receipts[cv(year),cv(weeks)]
 15                        - sale[cv(year),cv(weeks)],
 16  sale[2002,1] = 0,
 17  receipts[2002,1] = 0
 18  )
 19  /

PRODUCT
----------------------------------------------------------------------------
COUNTRY
----------------------------------------------------------------------------
      YEAR      WEEKS   RECEIPTS       SALE  INVENTORY
---------- ---------- ---------- ---------- ----------
Xtend Memory
Australia
      2001          5     95.906      93.44      26.76

Xtend Memory
Australia
      2001          4     245.64      256.7     24.294

Xtend Memory
Australia
      2001          1     96.894      92.26      4.634

Xtend Memory
Australia
      2001          3     61.412      47.24     35.354

Xtend Memory
Australia
      2001          2    134.928     118.38     21.182

Xtend Memory
Australia
      2002          1          0          0          0

已选择6行。

SQL>

2、符号标记

   符号标记提供了在规则左侧申明范围值的能力，可以理解为在规则中限定对返回结果集的条件。

SQL> select product,country,year,weeks,sale
  2  from sales_fact
  3  where product = 'Xtend Memory'
  4    and country = 'Australia'
  5  model return updated rows
  6  partition by (product,country)
  7  dimension by (year,weeks)
  8  measures (sale)
  9  rules
 10  (
 11  sale[year in('2000','2001'),weeks in(1,2)] order by year,weeks = sale[cv(year),cv(weeks)]*1.2
 12  )
 13  order by year,weeks
 14  /

PRODUCT
--------------------------------------------------------------------------------
COUNTRY
--------------------------------------------------------------------------------
      YEAR      WEEKS       SALE
---------- ---------- ----------
Xtend Memory
Australia
      2000          1      56.04

Xtend Memory
Australia
      2001          1    110.712

Xtend Memory
Australia
      2001          2    142.056

SQL>

该SQL与总述中的SQL有几处不同，如automatic order子句与order by year,weeks子句。

当不加order by year,weeks时，会报“ORA-32637”错误。在下文中，我们将讨论这些区别。

 

三、model子句中for循环

使用for循环可以指定规则左侧的值列表，for循环只可以定义在规则左侧用来将新单元格添加到结果集中上，不能用在规则左侧。如下：

SQL> select product,country,year,weeks,receipts,sale,inventory
  2  from sales_fact
  3  where product = 'Xtend Memory'
  4    and country = 'Australia'
  5    and year = '2001'
  6    and weeks <= 10
  7  model return updated rows
  8  partition by (product,country)
  9  dimension by (year,weeks)
 10  measures(receipts,sale,0 inventory)
 11  rules automatic order
 12  (
 13  inventory[year,weeks] = nvl(inventory[cv(year),cv(weeks)-1],0)
 14                        + receipts[cv(year),cv(weeks)]
 15                        - sale[cv(year),cv(weeks)],
 16  sale[2001,for weeks from 3 to 10 increment 1] = 0,
 17  receipts[2001,for weeks from 3 to 10 increment 1] = 0
 18  )
 19  order by weeks,receipts,sale
 20  /

PRODUCT
--------------------------------------------------------------------------------
COUNTRY
--------------------------------------------------------------------------------
      YEAR      WEEKS   RECEIPTS       SALE  INVENTORY
---------- ---------- ---------- ---------- ----------
Xtend Memory
Australia
      2001          1     96.894      92.26      4.634

Xtend Memory
Australia
      2001          2    134.928     118.38     21.182

Xtend Memory
Australia
      2001          3          0          0     21.182

Xtend Memory
Australia
      2001          4          0          0     21.182

Xtend Memory
Australia
      2001          5          0          0     21.182

Xtend Memory
Australia
      2001          6          0          0     21.182

Xtend Memory
Australia
      2001          7          0          0     21.182

Xtend Memory
Australia
      2001          8          0          0     21.182

Xtend Memory
Australia
      2001          9          0          0     21.182

Xtend Memory
Australia
      2001         10          0          0     21.182

已选择10行。

SQL>

l四、关于return updated rows子句

       下面我们和2.2中的例子进行对比，如下：

SQL> select product,country,year,weeks,sale
  2  from sales_fact
  3  where product = 'Xtend Memory'
  4    and country = 'Australia'
  5  model
  6  partition by (product,country)
  7  dimension by (year,weeks)
  8  measures(sale)
  9  rules
 10  (
 11  sale[year in('2000',2001'),weeks in(1,2)]
 12  order by year,weeks
 13  = sale[cv(year),cv(weeks)]*1.2
 14  )
 15  order by year,weeks
 16  /

PRODUCT
--------------------------------------------------------------------------------
COUNTRY
--------------------------------------------------------------------------------
      YEAR      WEEKS       SALE
---------- ---------- ----------
Xtend Memory
Australia
      2000         27      45.08

Xtend Memory
Australia
      2000         28      88.96

..........................................................

Xtend Memory
Australia
      2001         51     114.82

Xtend Memory
Australia
      2001         52      23.14

已选择159行。

SQL>

我们可以发现，加上retrn updated rows子句的时候，返回满足where语句的同时返回满足规则中的数据，而不加，则只返回满足

where语句的数据。我们可以得出，rows updated rows是为限定性子句，它只返回被规则修改过的数据。

 

五、关于model子句的求解顺序

     下面我们举例说明：

SQL> select product,country,year,weeks,receipts,sale,inventory
  2  from sales_fact
  3  where product = 'Xtend Memory'
  4    and country = 'Australia'
  5    and year = '2001'
  6    and weeks <= 10
  7  model return updated rows
  8  partition by (product,country)
  9  dimension by (year,weeks)
 10  measures (receipts,sale,0 inventory)
 11  rules
 12  (
 13  inventory[year,weeks] = nvl(inventory[cv(year),cv(weeks)-1],0)
 14                        + receipts[cv(year),cv(weeks)]
 15                        - sale[cv(year),cv(weeks)]
 16  )
 17  order by weeks
 18  /
from sales_fact
     *
第 2 行出现错误:
ORA-32637: 顺序排序 MODEL 中的自循环规则

SQL>

我们发现，把automatic order注释后，不能对其访问。

SQL> select product,country,year,weeks,receipts,sale,inventory
  2  from sales_fact
  3  where product = 'Xtend Memory'
  4    and country = 'Australia'
  5    and year = '2001'
  6    and weeks <= 10
  7  model return updated rows
  8  partition by (product,country)
  9  dimension by (year,weeks)
 10  measures (receipts,sale,0 inventory)
 11  rules
 12  (
 13  inventory[year,weeks] order by year,weeks
 14                       = nvl(inventory[cv(year),cv(weeks)-1],0)
 15                       + receipts[cv(year),cv(weeks)]
 16                       - sale[cv(year),cv(weeks)]
 17  )
 18   order by weeks,receipts,sale
 19  /

PRODUCT
--------------------------------------------------------------------------------
COUNTRY
--------------------------------------------------------------------------------
      YEAR      WEEKS   RECEIPTS       SALE  INVENTORY
---------- ---------- ---------- ---------- ----------
Xtend Memory
Australia
      2001          1     96.894      92.26      4.634

Xtend Memory
Australia
      2001          2    134.928     118.38     21.182

Xtend Memory
Australia
      2001          3     61.412      47.24     35.354

Xtend Memory
Australia
      2001          4     245.64      256.7     24.294

Xtend Memory
Australia
      2001          5     95.906      93.44      26.76

Xtend Memory
Australia
      2001          6     20.196      22.44     24.516

Xtend Memory
Australia
      2001          7     62.964      69.96      17.52

Xtend Memory
Australia
      2001          8     48.186      46.06     19.646

Xtend Memory
Australia
      2001          9     95.008      92.67     21.984

Xtend Memory
Australia
      2001         10     75.717      69.05     28.651

已选择10行。

SQL>

我们发现，在单元格级添加order by 语句同样也可以访问，与上述结果相同。

在实现业务中，使用model语句添加order by语句的时候小心，有些值是有先后顺序的。例如L：上述语句计算库存的时候。

SQL> select product,country,year,weeks,receipts,sale,inventory
  2  from sales_fact
  3  where product = 'Xtend Memory'
  4    and country = 'Australia'
  5    and year = '2001'
  6    and weeks <= 10
  7  model return updated rows
  8  partition by (product,country)
  9  dimension by (year,weeks)
 10  measures (receipts,sale,0 inventory)
 11  rules sequential order
 12  (
 13  inventory[year,weeks] order by year,weeks
 14                        = nvl(inventory[cv(year),cv(weeks)-1],0)
 15                        + receipts[cv(year),cv(weeks)]
 16                        - sale[cv(year),cv(weeks)],
 17  receipts[year in('2001'),weeks in(1,2,3)] order by year,weeks
 18                        = receipts[cv(year),cv(weeks)]*10
 19  )
 20  order by weeks,receipts,sale
 21  /

PRODUCT
------------------------------------------------------------------------------
COUNTRY
------------------------------------------------------------------------------
      YEAR      WEEKS   RECEIPTS       SALE  INVENTORY
---------- ---------- ---------- ---------- ----------
Xtend Memory
Australia
      2001          1     968.94      92.26      4.634

Xtend Memory
Australia
      2001          2    1349.28     118.38     21.182

Xtend Memory
Australia
      2001          3     614.12      47.24     35.354

Xtend Memory
Australia
      2001          4     245.64      256.7     24.294

Xtend Memory
Australia
      2001          5     95.906      93.44      26.76

Xtend Memory
Australia
      2001          6     20.196      22.44     24.516

Xtend Memory
Australia
      2001          7     62.964      69.96      17.52

Xtend Memory
Australia
      2001          8     48.186      46.06     19.646

Xtend Memory
Australia
      2001          9     95.008      92.67     21.984

Xtend Memory
Australia
      2001         10     75.717      69.05     28.651

已选择10行。

SQL>

在该例子中，使用了sequential order,sequential order  指定了规则按照其在列表中的先后顺序来执行。而使用automatic order则是由

oracle自动决定。

 

六、在model子句中使用聚合函数

      例子如下：

SQL> select product,country,year,weeks,sale,avg_sale,inventory,max_invty
  2  from sales_fact
  3  where product = 'Xtend Memory'
  4    and country = 'Australia'
  5    and year = '2001'
  6    and weeks <= 10
  7  model return updated rows
  8  partition by (product,country)
  9  dimension by (year,weeks)
 10  measures (receipts,sale,0 inventory,0 avg_sale,0 max_invty)
 11  rules automatic order
 12  (
 13  inventory[year,weeks] = nvl(inventory[cv(year),cv(weeks)-1],0)
 14                        + receipts[cv(year),cv(weeks)]
 15                        - sale[cv(year),cv(weeks)],
 16  avg_sale[year,any] = avg(sale)[cv(year),weeks],
 17  max_invty[year,any] = max(inventory)[cv(year),weeks]
 18  )
 19  order by weeks,sale,inventory
 20  /

PRODUCT
--------------------------------------------------------------------------------
COUNTRY
--------------------------------------------------------------------------------
      YEAR      WEEKS       SALE   AVG_SALE  INVENTORY  MAX_INVTY
---------- ---------- ---------- ---------- ---------- ----------
Xtend Memory
Australia
      2001          1      92.26      90.82      4.634     35.354

Xtend Memory
Australia
      2001          2     118.38      90.82     21.182     35.354

Xtend Memory
Australia
      2001          3      47.24      90.82     35.354     35.354

Xtend Memory
Australia
      2001          4      256.7      90.82     24.294     35.354

Xtend Memory
Australia
      2001          5      93.44      90.82      26.76     35.354

Xtend Memory
Australia
      2001          6      22.44      90.82     24.516     35.354

Xtend Memory
Australia
      2001          7      69.96      90.82      17.52     35.354

Xtend Memory
Australia
      2001          8      46.06      90.82     19.646     35.354

Xtend Memory
Australia
      2001          9      92.67      90.82     21.984     35.354

Xtend Memory
Australia
      2001         10      69.05      90.82     28.651     35.354

已选择10行。

SQL>

说明：使用[cv(year),weeks]来计算年与最大值，规则中的第二个值申明为any，说明与weeks的任何值相匹配。

 

七、model子句中的迭代

    在规则中使用迭代，可以完成一些复杂的商业需求。

   语法：[iterate (n) [until ]

          ( = ...)

SQL> select year,weeks,sale,sale_list
  2  from sales_fact
  3  where country = 'Australia'
  4    and product = 'Xtend Memory'
  5    and year = '2001'
  6    and weeks <= 10
  7  model return updated rows
  8  partition by (product,country)
  9  dimension by (year,weeks)
 10  measures (cast(' ' as varchar2(50)) sale_list,sale)
 11  rules iterate(5)
 12  (
 13  sale_list[year,weeks] order by year,weeks
 14                         = sale[cv(year),cv(weeks) - iteration_number + 2]
 15                         || case
 16                                when iteration_number = 0 then
 17                                     ' '
 18                                 else
 19                                     ','
 20                            end
 21                         || sale_list[cv(year),cv(weeks)]
 22  )
 23  order by weeks,sale
 24  /

      YEAR      WEEKS       SALE
---------- ---------- ----------
SALE_LIST
----------------------------------------------------------------------------
      2001          1      92.26
,,92.26,118.38,47.24

      2001          2     118.38
,92.26,118.38,47.24,256.7

      2001          3      47.24
92.26,118.38,47.24,256.7,93.44

      YEAR      WEEKS       SALE
---------- ---------- ----------
SALE_LIST
----------------------------------------------------------------------------
      2001          4      256.7
118.38,47.24,256.7,93.44,22.44

      2001          5      93.44
47.24,256.7,93.44,22.44,69.96

      2001          6      22.44
256.7,93.44,22.44,69.96,46.06

      YEAR      WEEKS       SALE
---------- ---------- ----------
SALE_LIST
----------------------------------------------------------------------------
      2001          7      69.96
93.44,22.44,69.96,46.06,92.67

      2001          8      46.06
22.44,69.96,46.06,92.67,69.05

      2001          9      92.67
69.96,46.06,92.67,69.05,

      YEAR      WEEKS       SALE
---------- ---------- ----------
SALE_LIST
----------------------------------------------------------------------------
      2001         10      69.05
46.06,92.67,69.05,,

已选择10行。

SQL>
 说明：rules iterate(5)表示规则循环5次，iteration_number是在规则中取循环次数的变量，cast为转换函数。