Oracle SQL高级编程——Model子句全解析

最新推荐文章于 2021-04-04 23:31:09 发布

conggova

最新推荐文章于 2021-04-04 23:31:09 发布

阅读量1.4k

点赞数 1

分类专栏：数据库技术 SQL 文章标签： oracle sql

本文链接：https://blog.csdn.net/conggova/article/details/77786835

版权

参见《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)

最低0.47元/天解锁文章

conggova

关注

1
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
Oracle SQL高级编程——Model子句全解析

参见《Oracle SQL高级编程》。第一个例子，初步认识SH@ prod> col product format a30SH@ prod> col country format a10SH@ prod> col region format a10SH@ prod> col year format 9999SH@ prod> col week format 99SH@ prod> col
复制链接

扫一扫