参见 《Oracle SQL高级编程》。
第一个例子,初步认识
SH@ prod> col product format a30
SH@ prod> col country format a10
SH@ prod> col region format a10
SH@ prod> col year format 9999
SH@ prod> col week format 99
SH@ prod> col sale format 999999
SH@ prod> set linesize 180 pagesize 100
SH@ prod> select product , country , year , week , inventory , sale , receipts from sales_fact
2 where country in ('Australia') and product = 'Xtend Memory'
3 model return updated rows
4 partition by (product , country)
5 dimension by (year , week)
6 measures (0 inventory , sale , receipts)
7 rules automatic order(
8 inventory[year , week] = nvl(inventory[cv(year) , cv(week)-1] , 0 )
9 - sale[cv(year) , cv(week)]
10 + receipts[cv(year) , cv(week)] )
11 order by product , country , year , week ;
PRODUCT COUNTRY YEAR WEEK INVENTORY SALE RECEIPTS
------------------------------ ---------- ----- ---- ---------- ------- ----------
Xtend Memory Australia 1998 1 8.88 58 67.03
Xtend Memory Australia 1998 2 14.758 29 35.268
Xtend Memory Australia 1998 3 20.656 29 35.388
Xtend Memory Australia 1998 4 8.86 29 17.694
Xtend Memory Australia 1998 5 14.82 30 35.76
Xtend Memory Australia 1998 6 8.942 59 52.902
Xtend Memory Australia 1998 9 2.939 59 61.719
Xtend Memory Australia 1998 10 .01 118 114.831
Xtend Memory Australia 1998 12 -14.9 60 44.7
Xtend Memory Australia 1998 14 11.756 59 70.536
Xtend Memory Australia 1998 15 5.878 59 52.902
Xtend Memory Australia 1998 17 11.756 59 70.536
Xtend Memory Australia 1998 18 8.817 118 114.621
Xtend Memory Australia 1998 19 2.919 59 53.082
Xtend Memory Australia 1998 21 2.98 60 62.58
Xtend Memory Australia 1998 23 -11.756 118 105.804
Xtend Memory Australia 1998 26 11.756 118 129.316
Xtend Memory Australia 1998 27 14.632 58 60.396
Xtend Memory Australia 1998 28 .202 58 43.29
Xtend Memory Australia 1998 29 -14.228 58 43.29
Xtend Memory Australia 1998 34 -2.886 115 112.554
Xtend Memory Australia 1998 35 -8.638 58 51.768
Xtend Memory Australia 1998 38 -11.464 116 104.376
Xtend Memory Australia 1998 39 -5.792 116 121.512
Xtend Memory Australia 1998 40 -11.544 58 51.768
Xtend Memory Australia 1998 41 -17.376 58 52.488
Xtend Memory Australia 1998 42 -5.832 116 127.384
Model子句剖析
在一个使用MODEL子句的SQL语句中,有3组列:分区列,维度列以及度量值列。
上一个例子中:
partition by (product , country)指定了分区列,
dimension by (year , week)指定了维度列,也就是指向数元素的索引,
measures (0 inventory , sale , receipts)指定了度量值列。
rules automatic order(
inventory[year , week] = nvl(inventory[cv(year) , cv(week)-1] , 0 )
- sale[cv(year) , cv(week)] +
+ receipts[cv(year) , cv(week)] )
这个子句表示规则。
这个是一个递推的规则:本周的库存量 = 上周的 – 卖掉的 + 新进的。
Cv函数表示规则表达示左侧中的索引的值。
用位置标记进行UPSERT操作
有则修改,没有则插入。
SH@ prod> select product , country , year , week , inventory , sale , receipts from sales_fact
2 where country in ('Australia') and product = 'Xtend Memory'
3 model return updated rows
4 partition by (product , country)
5 dimension by (year , week)
6 measures (0 inventory , sale , receipts)
7 rules automatic order(
8 inventory[year , week] = nvl(inventory[cv(year) , cv(week)-1] , 0 )
9 - sale[cv(year) , cv(week)]
10 + receipts[cv(year) , cv(week)] ,
11 sale[1997 , 1] = 0 ,
12 receipts[1997 , 1] = 0 )
13 order by product , country , year , week ;
PRODUCT COUNTRY YEAR WEEK INVENTORY SALE RECEIPTS
------------------------------ ---------- ----- ---- ---------- ------- ----------
Xtend Memory Australia 1997 1 0 0 0
Xtend Memory Australia 1998 1 8.88 58 67.03
Xtend Memory Australia 1998 2 14.758 29 35.268
Xtend Memory Australia 1998 3 20.656 29 35.388
Xtend Memory Australia 1998 4 8.86 29 17.694
Xtend Memory Australia 1998 5 14.82 30 35.76
Xtend Memory Australia 1998 6 8.942 59 52.902
Xtend Memory Australia 1998 9 2.939 59 61.719
Xtend Memory Australia 1998 10 .01 118 114.831
Xtend Memory Australia 1998 12 -14.9 60 44.7
修改了1998年的初始值,下面其它月份的值也受到了影响,因为是递推的。
SH@ prod> select product , country , year , week , inventory , sale , receipts from sales_fact
2 where country in ('Australia') and product = 'Xtend Memory'
3 model return updated rows
4 partition by (product , country)
5 dimension by (year , week)
6 measures (0 inventory , sale , receipts)
7 rules automatic order(
8 inventory[year , week] = nvl(inventory[cv(year) , cv(week)-1] , 0 )
9 - sale[cv(year) , cv(week)]
10 + receipts[cv(year) , cv(week)] ,
11 sale[1998 , 1] = 0 ,
12 receipts[1998 , 1] = 0 )
13 order by product , country , year , week ;
PRODUCT COUNTRY YEAR WEEK INVENTORY SALE RECEIPTS
------------------------------ ---------- ----- ---- ---------- ------- ----------
Xtend Memory Australia 1998 1 0 0 0
Xtend Memory Australia 1998 2 5.878 29 35.268
Xtend Memory Australia 1998 3 11.776 29 35.388
Xtend Memory Australia 1998 4 -.02 29 17.694
Xtend Memory Australia 1998 5 5.94 30 35.76
Xtend Memory Australia 1998 6 .062 59 52.902
Xtend Memory Australia 1998 9 2.939 59 61.719
Xtend Memory Australia 1998 10 .01 118 114.831
Xtend Memory Australia 1998 12 -14.9 60 44.7
Xtend Memory Australia 1998 14 11.756 59 70.536
用符号标记进行UPDATE操作(不能INSERT)
SH@ prod> select product , country , year , week , sale from sales_fact
2 where country in ('Australia') and product = 'Xtend Memory'
3 model return updated rows
4 partition by (product , country)
5 dimension by (year , week)
6 measures ( sale )
7 rules( sale[year in(2000 , 2001) , week in (1 , 52 , 53 )] order by year , week
8 = sale[cv(year) , cv(week)] * 1.10 )
9 order by product , country , year , week ;
PRODUCT COUNTRY YEAR WEEK SALE
------------------------------ ---------- ----- ---- -------
Xtend Memory Australia 2000 1 51
Xtend Memory Australia 2000 52 74
Xtend Memory Australia 2001 1 101
Xtend Memory Australia 2001 52 25
因为是return updated rows所以只返回了四行,不存在的行并不会被插入。
针对维度用FOR进行UPSERT操作
SH@ prod> select product , country , year , week , inventory , sale , receipts from sales_fact
2 where country in ('Australia') and product = 'Xtend Memory'
3 model return updated rows
4 partition by (product , country)
5 dimension by (year , week)
6 measures (0 inventory , sale , receipts)
7 rules automatic order(
8 inventory[year , week] = nvl(inventory[cv(year) , cv(week)-1] , 0 )
9 - sale[cv(year) , cv(week)]
10 + receipts[cv(year) , cv(week)] ,
11 sale[2002, for week from 1 to 53 increment 1 ] = 0 ,
12 receipts[2002 , for week from 1 to 53 increment 1] = 0 )
13 order by product , country , year , week ;
PRODUCT COUNTRY YEAR WEEK INVENTORY SALE RECEIPTS
------------------------------ ---------- ----- ---- ---------- ------- ----------
Xtend Memory Australia 1998 1 8.88 58 67.03
Xtend Memory Australia 1998 2 14.758 29 35.268
Xtend Memory Australia 1998 3 20.656 29 35.388
Xtend Memory Australia 1998 4 8.86 29 17.694
Xtend Memory Australia 1998 5 14.82 30 35.76
不加RETURN UPDATED ROWS
下面的语句与上数第二个是一样的,只是没有加RETURN UPDATED ROWS。会返回所有行。
SH@ prod> select product , country , year , week , sale from sales_fact
2 where country in ('Australia') and product = 'Xtend Memory'
3 model
4 partition by (product , country)
5 dimension by (year , week)
6 measures ( sale )
7 rules( sale[year in(2000 , 2001) , week in (1 , 52 , 53 )] order by year , week
8 = sale[cv(year) , cv(week)] * 1.10 )
9 order by product , country , year , week ;
PRODUCT COUNTRY YEAR WEEK SALE
------------------------------ ---------- ----- ---- -------
Xtend Memory Australia 1998 1 58
Xtend Memory Australia 1998 2 29
Xtend Memory Australia 1998 3 29
Xtend Memory Australia 1998 4 29
Xtend Memory Australia 1998 5 30
Xtend Memory Australia 1998 6 59
Xtend Memory Australia 1998 9 59
Xtend Memory Australia 1998 10 118
Xtend Memory Australia 1998 12 60
位置标记的UPSERT与RETURN UPDATED ROWS
由于规则只更新了一行,所以一定只返回一行。
SH@ prod> select product , country , year , week , sale from sales_fact
2 where country in ('Australia') and product = 'Xtend Memory'
3 model return updated rows
4 partition by (product , country)
5 dimension by (year , week)
6 measures ( sale )
7 rules( sale[2000,1] = 0 )
8 order by product , country , year , week ;
PRODUCT COUNTRY YEAR WEEK SALE
------------------------------ ---------- ----- ---- -------
Xtend Memory Australia 2000 1 0
Model子句中规则的求解顺序
下面的语句会报错,因既没有显式的指定求解顺序,又没有加Automatic Order,此时Oracle会使用Sequential Order这种顺序,并不适合这个规则。
SH@ prod> select product , country , year , week , inventory , sale , receipts from sales_fact
2 where country in ('Australia') and product = 'Xtend Memory' and week < 10
3 model return updated rows
4 partition by (product , country)