oracle model 使用变量,oracle的model语句入门

selectID,ValfromaddTotalunionallselectnull,Sum(Val)fromaddTotal;

=================================================

以产品产量表为例，一个工厂(用code表示)生产多种产品(用p_id表示)，每种产品具有生产量(v1)和销售量(v2)

产品代码具有审核关系，比如’10’=’30’+’31’，其中’10’代表大类，’30’和’31’代表’10’大类下的小类。

SQL> create table t603 (code varchar(10),p_id varchar(7),v1 number(10),v2 number(10));

Table created.

SQL> insert into t603 values(’600001’,’30’,1,1);

SQL> insert into t603 values(’600001’,’31’,1,1);

SQL> insert into t603 values(’600001’,’10’,2,2);

SQL> insert into t603 values(’600002’,’10’,3,2);

SQL> insert into t603 values(’600002’,’31’,2,1);

SQL> insert into t603 values(’600002’,’30’,2,1);

SQL> commit;

Commit complete.

SQL> select * from t603;

CODE P_ID V1 V2

600001 30 1 1

600001 31 1 1

600001 10 2 2

600002 10 3 2

600002 31 2 1

600002 30 2 1

6 rows selected.

SELECT code,

p_id, v1

FROM t603

WHERE code IN (’600001’,’600002’)

MODEL RETURN UPDATED ROWS

PARTITION BY (code)

DIMENSION BY (p_id)

MEASURES (v1)

RULES (

v1[’err1’] = v1[’30’] + v1[’31’] -v1[’10’])

ORDER BY code, p_id;

其中rule表示计算规则，’err1’表示这条审核关系的代号，它的值等于P_ID为’30’的v1值+P_ID为’31’的v1值-P_ID为’10’的v1值

PARTITION BY (code)表示按工厂分区，即审核在一个工厂内的产品

MODEL 关键字后面的 RETURN UPDATED ROWS 子句将结果限制为在该查询中创建或更新的那些行。使用该子句是使结果集只包含新计算的值，在本例中就是审核结果

CODE P_ID V1

600001 err1 0

600002 err1 1

如果返回值=0，表示v1[’30’] + v1[’31’] =v1[’10’]审核通过，否则，审核不通过

SELECT code,

p_id, v1,v2

FROM t603

WHERE code IN (’600001’,’600002’)

MODEL RETURN UPDATED ROWS

PARTITION BY (code)

DIMENSION BY (p_id)

MEASURES (v1,v2)

RULES (

v1[’err1’] = v1[’30’] + v1[’31’] -v1[’10’],

v2[’err1’] = v2[’30’] + v2[’31’] -v2[’10’])

ORDER BY code, p_id;

CODE P_ID V1 V2

600001 err1 0 0

600002 err1 1 0

如果表格中包含多个维度的数据，比如时间，多个维度都可以编写规则,比如2008年的审核关系

SELECT year,code,

p_id, v1

FROM t603_1

WHERE code IN (’600001’,’600002’)

MODEL RETURN UPDATED ROWS

PARTITION BY (code)

DIMENSION BY (p_id,year)

MEASURES (v1)

RULES (

v1[’err1’,2008] = v1[’30’,2008] + v1[’31’,2008] -v1[’10’,2008])

ORDER BY code, p_id;

YEAR CODE P_ID V1

2008 600001 err1 0

2008 600002 err1 1

如果维度不影响规则，也可以只分区，而沿用原来的规则

SELECT year,code,

p_id, v1

FROM t603_1

WHERE code IN (’600001’,’600002’)

MODEL RETURN UPDATED ROWS

PARTITION BY (code,year)

DIMENSION BY (p_id)

MEASURES (v1)

RULES (

v1[’err1’] = v1[’30’] + v1[’31’] -v1[’10’])

ORDER BY code, p_id;

YEAR CODE P_ID V1

2008 600001 err1 0

2008 600002 err1 1

SQL> create table t603_2 as select * from t603_1;

Table created.

SQL> insert into t603_2 select ’2007’ year,code,p_id,v1,v2 from t603_1;

6 rows created.

如果表中具有多个年份的数据，每个年份的审核结果都能显示

SELECT year,code,

p_id, v1

FROM t603_2

WHERE code IN (’600001’,’600002’)

MODEL RETURN UPDATED ROWS

PARTITION BY (code,year)

DIMENSION BY (p_id)

MEASURES (v1)

RULES (

v1[’err1’] = v1[’30’] + v1[’31’] -v1[’10’])

ORDER BY code, p_id;

YEAR CODE P_ID V1

2008 600001 err1 0

2007 600001 err1 0

2007 600002 err1 1

2008 600002 err1 1

规则也可以是多个维度不同取值，本例假定不同年份之间比较，比如要求2008年的’10’=2007年的’30’+’31’

SELECT year,code,

p_id, v1

FROM t603_2

WHERE code IN (’600001’,’600002’)

MODEL RETURN UPDATED ROWS

PARTITION BY (code)

DIMENSION BY (p_id,year)

MEASURES (v1)

RULES (

v1[’err1’,2008] = v1[’30’,2007] + v1[’31’,2007] -v1[’10’,2008])

ORDER BY code, p_id;

YEAR CODE P_ID V1

2008 600001 err1 0

2008 600002 err1 1

如果年份很多，每个年份都是和上年比较，这种描述可以用CV()函数简化

SQL> insert into t603_2 select ’2006’ year,code,p_id,v1,v2 from t603_1;

SELECT year,code,

p_id, v1

FROM t603_2

WHERE code IN (’600001’,’600002’)

MODEL RETURN UPDATED ROWS

PARTITION BY (code)

DIMENSION BY (p_id,year)

MEASURES (v1)

RULES (

v1[’err1’,for year in( 2007 ,2008)] = v1[’30’,CV(year)-1] + v1[’31’,CV(year)-1] -v1[’10’,CV(year)])

ORDER BY code, p_id;

YEAR CODE P_ID V1

2007 600001 err1 0

2008 600001 err1 0

2007 600002 err1 1

2008 600002 err1 1

如果year是数值类型，还可以用for year from 2007 to 2009 increment 1的语法，如果是其他类型，还可以用在in子句带子查询的办法，

比如for year in (select year from t603_2)。

但需要注意不能采用year in 的语法，year in的语法只能符号引用已经存在的单元格，而v1[’err1’,x]是新单元格。

单个年份的写法如下:

SELECT year,code,

p_id, v1

FROM t603_2

WHERE code IN (’600001’,’600002’)

MODEL RETURN UPDATED ROWS

PARTITION BY (code)

DIMENSION BY (p_id,year)

MEASURES (v1)

RULES (

v1[’err1’,2008] = v1[’30’,CV()] + v1[’31’,CV()] -v1[’10’,CV()])

ORDER BY code, p_id;

YEAR CODE P_ID V1

2008 600001 err1 0

2008 600002 err1 1

而

SELECT year,code,

p_id, v1

FROM t603_2

WHERE code IN (’600001’,’600002’)

MODEL RETURN UPDATED ROWS

PARTITION BY (code)

DIMENSION BY (p_id,year)

MEASURES (v1)

RULES (

v1[’err1’,year in (’2008’)] = v1[’30’,CV()] + v1[’31’,CV()] -v1[’10’,CV()])

则返回0行