oracle model类型,Oracle SQL高级编程——Model子句全解析-Oracle
下面的语句会报错，因既没有显式的指定求解顺序，又没有加Automatic Order，此时Oracle会使用Sequential Order这种顺序，并不适合这个规则。SH@ prod> select product , country , year , week , inventory , sale , receipts from sales_fact2 where country in
下面的语句会报错，因既没有显式的指定求解顺序，又没有加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)
5 dimension by (year , week)
6 measures (0 inventory , sale , receipts)
7 rules (
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 ;
select product , country , year , week , inventory , sale , receipts from sales_fact
*
ERROR at line 1:
ORA-32637: Self cyclic rule in sequential order MODEL
添加了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)
5 dimension by (year , week)
6 measures (0 inventory , sale , receipts)
7 rules sequential 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 ;
select product , country , year , week , inventory , sale , receipts from sales_fact
*
ERROR at line 1:
ORA-32637: Self cyclic rule in sequential order MODEL
解决办法1，添另Automatic 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)
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 1999 1 2.676 54 56.196
Xtend Memory Australia 1999 3 4.73 95 99.33
Xtend Memory Australia 1999 4 4.73 41 40.5
Xtend Memory Australia 1999 5 10.064 80 85.344
Xtend Memory Australia 1999 6 6.014 41 36.45
Xtend Memory Australia 1999 8 -2.196 103 100.914
Xtend Memory Australia 1999 9 13.806 53 69.342
Xtend Memory Australia 2000 1 -11.675 47 35.025
Xtend Memory Australia 2000 3 11.602 93 105.012
Xtend Memory Australia 2000 4 6.948 47 41.886
Xtend Memory Australia 2000 5 16.288 47 56.04
Xtend Memory Australia 2000 7 0 71 70.8
Xtend Memory Australia 2000 8 2.327 47 48.867
Xtend Memory Australia 2001 1 4.634 92 96.894
Xtend Memory Australia 2001 2 21.182 118 134.928
Xtend Memory Australia 2001 3 35.354 47 61.412
Xtend Memory Australia 2001 4 24.294 257 245.64
Xtend Memory Australia 2001 5 26.76 93 95.906
Xtend Memory Australia 2001 6 24.516 22 20.196
Xtend Memory Australia 2001 7 17.52 70 62.964
Xtend Memory Australia 2001 8 19.646 46 48.186
Xtend Memory Australia 2001 9 21.984 93 95.008
解决办法2，具体指定顺序：
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)
5 dimension by (year , week)
6 measures (0 inventory , sale , receipts)
7 rules (
8 inventory[year , week] order by year , week
9 = nvl(inventory[cv(year) , cv(week)-1] , 0 )
10 – sale[cv(year) , cv(week)]
11 + receipts[cv(year) , cv(week)])
12 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 1999 1 2.676 54 56.196
Xtend Memory Australia 1999 3 4.73 95 99.33
Xtend Memory Australia 1999 4 4.73 41 40.5
Xtend Memory Australia 1999 5 10.064 80 85.344
Xtend Memory Australia 1999 6 6.014 41 36.45
Xtend Memory Australia 1999 8 -2.196 103 100.914
Xtend Memory Australia 1999 9 13.806 53 69.342
Xtend Memory Australia 2000 1 -11.675 47 35.025
Xtend Memory Australia 2000 3 11.602 93 105.012
Xtend Memory Australia 2000 4 6.948 47 41.886
Xtend Memory Australia 2000 5 16.288 47 56.04
Xtend Memory Australia 2000 7 0 71 70.8
Xtend Memory Australia 2000 8 2.327 47 48.867
Xtend Memory Australia 2001 1 4.634 92 96.894
Xtend Memory Australia 2001 2 21.182 118 134.928
Xtend Memory Australia 2001 3 35.354 47 61.412
Xtend Memory Australia 2001 4 24.294 257 245.64
Xtend Memory Australia 2001 5 26.76 93 95.906
Xtend Memory Australia 2001 6 24.516 22 20.196
Xtend Memory Australia 2001 7 17.52 70 62.964
Xtend Memory Australia 2001 8 19.646 46 48.186
Xtend Memory Australia 2001 9 21.984 93 95.008
魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。
更多推荐
0
0- 0
已为社区贡献11条内容
所有评论(0)