Oracle10g的MODELSQL进行行间计算

会计考友

　以产品产量表为例，一个工厂(用code表示)生产多种产品(用p_id表示)，每种产品具有生产量(v1)和销售量(v2)
　　产品代码具有审核关系，比如’10’=’30’+’31’，其中’10’代表大类，’30’和’31’代表’10’大类下的小类。
; n* E: U+ ~7 ], Y2 k6 w; G9 ^: [( y　　SQL> create table t603 (code varchar(10),p_id varchar(7),v1 number(10),v2 number(10));
& W$ u( K2 K. h: B' g　　Table created.
1 Z1 o; Z% f4 g1 v6 u　　SQL> insert into t603 values(’600001’,’30’,1,1);
　　SQL> insert into t603 values(’600001’,’31’,1,1);
. U/ Y( ~3 ^: E& _! g# h　　SQL> insert into t603 values(’600001’,’10’,2,2);
　　SQL> insert into t603 values(’600002’,’10’,3,2);
: d: L  X3 m! a, i; b　　SQL> insert into t603 values(’600002’,’31’,2,1);
4 o, q7 _; A. R; A" _  N　　SQL> insert into t603 values(’600002’,’30’,2,1);
　　SQL> commit;
1 {" O9 L1 t: ?( r% w" m+ O& L: F　　Commit complete.
$ u" x# w; B4 p0 i　　SQL> select * from t603;
　　CODE P_ID V1 V2
　　600001 30 1 1
* W! d5 h' d; d8 g2 M　　600001 31 1 1
　　600001 10 2 2
　　600002 10 3 2
　　600002 31 2 1
$ p  x! d( i1 s( X　　600002 30 2 1
　　6 rows selected.
2 Z; r+ L! I( V6 S4 P　　SELECT code,
! p: A4 ?7 [& `6 E. {　　p_id, v1
. c! N( r! h- q　　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’])
2 U" a) g3 J& s# @" K3 I: L　　ORDER BY code, p_id;
　　其中rule表示计算规则，’err1’表示这条审核关系的代号，它的值等于P_ID为’30’的v1值+P_ID为’31’的v1值-P_ID为’10’的v1值
& ~4 s2 n) j! Z7 C　　PARTITION BY (code)表示按工厂分区，即审核在一个工厂内的产品
3 |4 w! H) z0 c) `2 e$ Y7 G# O+ p　　MODEL 关键字后面的 RETURN UPDATED ROWS 子句将结果限制为在该查询中创建或更新的那些行。使用该子句是使结果集只包含新计算的值，在本例中就是审核结果
　　CODE P_ID V1
　　600001 err1 0
　　600002 err1 1
9 Z3 h5 ^2 N1 u* X　　如果返回值=0，表示v1[’30’] + v1[’31’] =v1[’10’]审核通过，否则，审核不通过
　　SELECT code,
　　p_id, v1,v2
　　FROM t603
　　WHERE code IN (’600001’,’600002’)
0 ]  N/ v% A! H% U) Q, A8 i1 W　　MODEL RETURN UPDATED ROWS
2 C0 k# n# |1 ]- V. k2 m. {/ m0 r, F　　PARTITION BY (code)
　　DIMENSION BY (p_id)
- [, P2 J4 K8 `5 ~2 F　　MEASURES (v1,v2)
　　RULES (
& F5 D& e& N3 O0 u+ W' W  o; q　　v1[’err1’] = v1[’30’] + v1[’31’] -v1[’10’],
　　v2[’err1’] = v2[’30’] + v2[’31’] -v2[’10’])
6 f" e6 K7 U: r! w# [　　ORDER BY code, p_id;
2 |, _1 L+ z) c1 ]! o8 O　　CODE P_ID V1 V2
　　600001 err1 0 0
　　600002 err1 1 0
　　如果表格中包含多个维度的数据，比如时间，多个维度都可以编写规则,比如2008年的审核关系
　　SELECT year,code,
5 P$ o5 C3 c) ?7 }　　p_id, v1
" F6 Z2 o. a% k8 Y　　FROM t603_1
# N# h: z. k& y- C　　WHERE code IN (’600001’,’600002’)
! r/ Y- y4 E" E: g) R　　MODEL RETURN UPDATED ROWS
' b6 E: t) V+ v9 Z$ E$ u3 a　　PARTITION BY (code)
　　DIMENSION BY (p_id,year)
　　MEASURES (v1)
/ I  D0 a' K8 l7 a: t3 V; U% V　　RULES (
2 Z; ^1 \7 R( V; {* o' R4 i  a/ b　　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
% A3 o  I8 Z2 V2 X( A7 V% w　　2008 600002 err1 1
3 ]6 s2 S0 R  A( P　　如果维度不影响规则，也可以只分区，而沿用原来的规则
/ v  r; |9 \* z$ e! V6 b　　SELECT year,code,
. @4 V& e; ]. T0 A　　p_id, v1
　　FROM t603_1
" M. g# u- @- t3 M3 B  M　　WHERE code IN (’600001’,’600002’)
　　MODEL RETURN UPDATED ROWS
　　PARTITION BY (code,year)
　　DIMENSION BY (p_id)
　　MEASURES (v1)
　　RULES (
' Y0 i2 u' j0 T$ R* s7 }3 \; O. W　　v1[’err1’] = v1[’30’] + v1[’31’] -v1[’10’])
　　ORDER BY code, p_id;
　　YEAR CODE P_ID V1
( F2 I/ Z4 {9 v　　2008 600001 err1 0
- ~, B" B8 ^& t　　2008 600002 err1 1
　　SQL> create table t603_2 as select * from t603_1;
. E: F3 J# {, L9 ~) Q　　Table created.
  }# P& j9 e4 F% z8 m, i" ~( Y　　SQL> insert into t603_2 select ’2007’ year,code,p_id,v1,v2 from t603_1;
9 I' C, I: f9 B4 c' b8 p　　6 rows created.

会计考友

　如果表中具有多个年份的数据，每个年份的审核结果都能显示
　　SELECT year,code,
　　p_id, v1
8 S2 b' w6 w( x2 A/ J/ F　　FROM t603_2
　　WHERE code IN (’600001’,’600002’)
　　MODEL RETURN UPDATED ROWS
　　PARTITION BY (code,year)
1 h1 t' W* m# P- X4 H* i9 b　　DIMENSION BY (p_id)
　　MEASURES (v1)
9 ^$ X  B; u" r& q+ I　　RULES (
, t5 S8 u$ Z) ^) l+ `8 V　　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
" [  A$ i) Q( K8 d0 I  d　　2007 600002 err1 1
7 N' a* Z& K" W: U+ I　　2008 600002 err1 1
/ S. O* }# O5 b* ^! V* v　　规则也可以是多个维度不同取值，本例假定不同年份之间比较，比如要求2008年的’10’=2007年的’30’+’31’
7 i! w( E; \+ [$ w' z; e* k　　SELECT year,code,
8 o0 ?) n5 U/ @5 [$ T! K$ ]　　p_id, v1
　　FROM t603_2
　　WHERE code IN (’600001’,’600002’)
　　MODEL RETURN UPDATED ROWS
- u  t/ U! j, X5 \, I　　PARTITION BY (code)
2 R* {5 h) z2 v& M　　DIMENSION BY (p_id,year)
" _5 |* V+ j' K5 v! u0 l. m2 e6 ^4 ]　　MEASURES (v1)
　　RULES (
, h  t/ K2 S" Y3 e2 B7 x　　v1[’err1’,2008] = v1[’30’,2007] + v1[’31’,2007] -v1[’10’,2008])
% G1 {" c! O# M8 M) ^1 H　　ORDER BY code, p_id;
9 L  b3 ~0 q3 q: B8 g* _　　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)
8 z; `0 P# z* ?　　DIMENSION BY (p_id,year)
　　MEASURES (v1)
3 W2 u$ @$ Q) i* n9 w! U　　RULES (
　　v1[’err1’,for year in( 2007 ,2008)] = v1[’30’,CV(year)-1] + v1[’31’,CV(year)-1] -v1[’10’,CV(year)])
* h! v% H: @. X* x/ `' n　　ORDER BY code, p_id;
　　YEAR CODE P_ID V1
　　2007 600001 err1 0
+ u" [- w8 m, g2 `1 U3 [! a　　2008 600001 err1 0
) w# v& ~$ n! z5 `, O* c　　2007 600002 err1 1
. t6 e2 o2 ?) K; X$ [　　2008 600002 err1 1
( w- @% G; ?% v* B% P, t$ |　　如果year是数值类型，还可以用for year from 2007 to 2009 increment 1的语法，如果是其他类型，还可以用在in子句带子查询的办法，
　　比如for year in (select year from t603_2)。
7 d* s+ n7 q6 U& T, P. M　　但需要注意不能采用year in 的语法，year in的语法只能符号引用已经存在的单元格，而v1[’err1’,x]是新单元格。
  v6 [7 `# v6 B　　单个年份的写法如下:
- I  E" k7 Z8 }; i4 m　　SELECT year,code,
　　p_id, v1
2 t& O% a2 G* X- n& o/ z4 i　　FROM t603_2
　　WHERE code IN (’600001’,’600002’)
. v4 z4 `& H) _　　MODEL RETURN UPDATED ROWS
! U8 V* u# ]: k: g　　PARTITION BY (code)
+ C6 {2 P4 V! c7 ^　　DIMENSION BY (p_id,year)
) o' A8 O0 \% w  }+ S8 {　　MEASURES (v1)
. H4 K4 f8 e" c) Y　　RULES (
8 r' E4 t* `$ `: `8 o4 S　　v1[’err1’,2008] = v1[’30’,CV()] + v1[’31’,CV()] -v1[’10’,CV()])
* y5 O# i0 _" Q0 F# ~3 A9 r　　ORDER BY code, p_id;
　　YEAR CODE P_ID V1
# N0 A: U* a7 y0 X! @　　2008 600001 err1 0
& L: f5 _  Y; ~6 o0 F+ G5 _" h4 G　　2008 600002 err1 1
5 k1 b$ S- F3 `' U: V" q　　而
　　SELECT year,code,
　　p_id, v1
　　FROM t603_2
　　WHERE code IN (’600001’,’600002’)
　　MODEL RETURN UPDATED ROWS
　　PARTITION BY (code)
4 z) E2 B& |6 z% m$ x% g2 d$ b　　DIMENSION BY (p_id,year)
　　MEASURES (v1)
　　RULES (
　　v1[’err1’,year in (’2008’)] = v1[’30’,CV()] + v1[’31’,CV()] -v1[’10’,CV()])
% Y8 V! X; V. G4 [& {　　则返回0行