Oracle10g的MODELSQL进行行间计算

会计考友

　以产品产量表为例，一个工厂(用code表示)生产多种产品(用p_id表示)，每种产品具有生产量(v1)和销售量(v2)
1 n8 y+ M3 J6 \: W! w　　产品代码具有审核关系，比如’10’=’30’+’31’，其中’10’代表大类，’30’和’31’代表’10’大类下的小类。
# _, x9 F" t. p  \　　SQL> create table t603 (code varchar(10),p_id varchar(7),v1 number(10),v2 number(10));
　　Table created.
% {! n3 \$ d; G3 K) M1 q　　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);
% N5 B' x/ _; p: m( Z( B9 j5 X　　SQL> insert into t603 values(’600002’,’10’,3,2);
　　SQL> insert into t603 values(’600002’,’31’,2,1);
4 B( y* i8 h$ n　　SQL> insert into t603 values(’600002’,’30’,2,1);
' c0 ]% j" R7 ]- ~+ g　　SQL> commit;
, K! o# G5 b2 E. f, r' O　　Commit complete.
' {0 S7 e/ t6 T* V- y' }. M  z' b　　SQL> select * from t603;
4 Y+ B* U5 G8 a  F　　CODE P_ID V1 V2
  R9 |7 @2 V1 a: q　　600001 30 1 1
　　600001 31 1 1
　　600001 10 2 2
　　600002 10 3 2
% C6 F4 i4 {1 I% h- x4 O　　600002 31 2 1
/ k1 x! q& h1 Q: S　　600002 30 2 1
　　6 rows selected.
　　SELECT code,
　　p_id, v1
3 |' _5 A, w7 }, y3 I  [　　FROM t603
　　WHERE code IN (’600001’,’600002’)
7 e2 f/ a% A+ E" c. {$ O1 f　　MODEL RETURN UPDATED ROWS
　　PARTITION BY (code)
　　DIMENSION BY (p_id)
4 X0 E; b  D6 l+ }+ z9 @& L6 V/ Y　　MEASURES (v1)
+ s2 H4 d: h  W- G: e　　RULES (
# M; S( b5 Z- z. y6 l6 H2 c　　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
5 j3 s5 c. e# S' q' h　　600001 err1 0
　　600002 err1 1
　　如果返回值=0，表示v1[’30’] + v1[’31’] =v1[’10’]审核通过，否则，审核不通过
; _3 b4 O. t; N+ P6 e; D' O  A　　SELECT code,
　　p_id, v1,v2
+ a% u/ v+ @* Z, e% M, t　　FROM t603
' d" I, t, l* m/ I　　WHERE code IN (’600001’,’600002’)
　　MODEL RETURN UPDATED ROWS
　　PARTITION BY (code)
5 A, P) M4 J! U" O　　DIMENSION BY (p_id)
　　MEASURES (v1,v2)
( b9 E0 b  }( i7 u2 L* x8 j7 @　　RULES (
　　v1[’err1’] = v1[’30’] + v1[’31’] -v1[’10’],
5 S1 S# [- T: n　　v2[’err1’] = v2[’30’] + v2[’31’] -v2[’10’])
, j. R  G/ E6 J: y　　ORDER BY code, p_id;
　　CODE P_ID V1 V2
　　600001 err1 0 0
　　600002 err1 1 0
( m4 t% t2 m4 }8 u' S  z　　如果表格中包含多个维度的数据，比如时间，多个维度都可以编写规则,比如2008年的审核关系
5 A+ K$ P1 E2 p1 G( S! b　　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)
9 d- P. X2 }7 a, e( [" |: R0 U) u　　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
  x8 _1 L; R5 Y0 [　　FROM t603_1
　　WHERE code IN (’600001’,’600002’)
　　MODEL RETURN UPDATED ROWS
　　PARTITION BY (code,year)
# h+ q+ P; I3 y7 [' _' T4 e+ e( q　　DIMENSION BY (p_id)
8 F" T" ]5 @; S% B) \" U$ a　　MEASURES (v1)
　　RULES (
# {# A' i1 ^! A9 z! B9 J) W& |　　v1[’err1’] = v1[’30’] + v1[’31’] -v1[’10’])
% P7 }: l% ^) x" K　　ORDER BY code, p_id;
7 N# a) c, R, r; C' \　　YEAR CODE P_ID V1
; ?7 }6 [. G( C  M0 m　　2008 600001 err1 0
　　2008 600002 err1 1
! l" J5 v0 x: ?% s　　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;
1 V% }$ T, E) p, P　　6 rows created.

会计考友

　如果表中具有多个年份的数据，每个年份的审核结果都能显示
　　SELECT year,code,
　　p_id, v1
6 I9 f; a- I) O1 r　　FROM t603_2
　　WHERE code IN (’600001’,’600002’)
: W( n$ v8 J* w* v) B　　MODEL RETURN UPDATED ROWS
　　PARTITION BY (code,year)
% r7 f& Q+ s/ C+ B* H　　DIMENSION BY (p_id)
) f) L' q1 H  F- Y6 P  U　　MEASURES (v1)
2 U& n5 Z/ ]9 S& I　　RULES (
　　v1[’err1’] = v1[’30’] + v1[’31’] -v1[’10’])
　　ORDER BY code, p_id;
& |* G' c# g( w, a　　YEAR CODE P_ID V1
. q8 s+ ?" R* i9 m# t- E　　2008 600001 err1 0
　　2007 600001 err1 0
　　2007 600002 err1 1
　　2008 600002 err1 1
# r8 p- x8 W4 ^8 r& G5 i0 _　　规则也可以是多个维度不同取值，本例假定不同年份之间比较，比如要求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)
9 J+ }4 p; z7 x　　DIMENSION BY (p_id,year)
2 j2 O! W7 y; E% I　　MEASURES (v1)
( Y8 N/ X0 \& ]: j: Q* V　　RULES (
. \2 ^3 c0 I& r/ J3 J. s　　v1[’err1’,2008] = v1[’30’,2007] + v1[’31’,2007] -v1[’10’,2008])
2 C/ \' o# K* F1 I1 L0 g　　ORDER BY code, p_id;
　　YEAR CODE P_ID V1
　　2008 600001 err1 0
5 t' [+ r( K; V7 t! J0 E- B/ `9 R　　2008 600002 err1 1
& U9 [/ u/ ]; _4 j　　如果年份很多，每个年份都是和上年比较，这种描述可以用CV()函数简化
　　SQL> insert into t603_2 select ’2006’ year,code,p_id,v1,v2 from t603_1;
　　SELECT year,code,
9 N0 ], m' B' U6 a" u+ I. I　　p_id, v1
　　FROM t603_2
6 i! \& _9 O3 p2 {3 r/ ^3 J5 d　　WHERE code IN (’600001’,’600002’)
　　MODEL RETURN UPDATED ROWS
　　PARTITION BY (code)
　　DIMENSION BY (p_id,year)
　　MEASURES (v1)
  ^8 E1 j+ n; D$ f7 @+ F　　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;
9 `9 }6 F4 b& ?, ]　　YEAR CODE P_ID V1
: u6 i2 o9 ]5 Z# [8 u# `4 L　　2007 600001 err1 0
/ O5 ~' p- O- v6 P　　2008 600001 err1 0
　　2007 600002 err1 1
　　2008 600002 err1 1
1 N0 w2 y" j7 g; S" a3 G0 _　　如果year是数值类型，还可以用for year from 2007 to 2009 increment 1的语法，如果是其他类型，还可以用在in子句带子查询的办法，
( \  w+ n" i0 m) W; j$ b0 h; i　　比如for year in (select year from t603_2)。
+ p" O) ~% K7 W+ s0 Q$ C　　但需要注意不能采用year in 的语法，year in的语法只能符号引用已经存在的单元格，而v1[’err1’,x]是新单元格。
　　单个年份的写法如下:
　　SELECT year,code,
. j* `0 Q& E0 z* y　　p_id, v1
　　FROM t603_2
　　WHERE code IN (’600001’,’600002’)
　　MODEL RETURN UPDATED ROWS
　　PARTITION BY (code)
& r0 [# @, V# T% j# e! V　　DIMENSION BY (p_id,year)
! I) r& \( W4 q7 k. E6 c" `' {　　MEASURES (v1)
9 n% C- Z: {. Z! f$ `7 f　　RULES (
7 x& n, w- k( ~3 k6 j# F5 s　　v1[’err1’,2008] = v1[’30’,CV()] + v1[’31’,CV()] -v1[’10’,CV()])
　　ORDER BY code, p_id;
  K6 ]) F2 l% r$ c% M　　YEAR CODE P_ID V1
　　2008 600001 err1 0
　　2008 600002 err1 1
　　而
　　SELECT year,code,
, Y6 T0 K5 X6 z2 E8 ]0 t. A　　p_id, v1
% Z) s2 ?% b$ e2 K( T" ^( f5 p　　FROM t603_2
- D2 w! A% U( y' |　　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行