学步园

利用Oracle 10g的 MODEL SQL进行行间计算
以产品产量表为例，一个工厂（用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