Model的使用方法

行变列

select
  b1,b2,b3
from
  model_test
model
RETURN UPDATED ROWS
dimension by(A1,A2,A3)
measures(
  '   ' as b1,
  '   ' as b2,
  '   ' as b3,
  A4
)
rules(
  b1[null,null,null]=A4['01','1','1'],
  b2[null,null,null]=A4['02','1','1'],
  b3[null,null,null]=A4['03','1','1']
);

列变行

select
a, n, cn
from
(
select 
'a' as a,
'1' as a1,
'2' as a2,
'3' as a3,
'4' as a4,
'5' as a5,
'6' as a6,
'7' as a7,
'8' as a8,
'9' as a9,
'10' as a10,
'11' as a11,
'12' as a12
from dual
)t
MODEL
RETURN UPDATED ROWS
PARTITION BY (t.a as a)
DIMENSION BY (0 AS n)
MEASURES (
'xx' as cn,
t.a1 as a1,
t.a2 as a2,
t.a3 as a3,
t.a4 as a4,
t.a5 as a5,
t.a6 as a6,
t.a7 as a7,
t.a8 as a8,
t.a9 as a9,
t.a10 as a10,
t.a11 as a11,
t.a12 as a12)
RULES UPSERT ALL
(
cn[1] =   a1[0],
cn[2] =   a2[0],
cn[3] =   a3[0],
cn[4] =   a4[0],
cn[5] =   a5[0],
cn[6] =   a6[0],
cn[7] =   a7[0],
cn[8] =   a8[0],
cn[9] =   a9[0],
cn[10] =  a10[0],
cn[11] =  a11[0],
cn[12] =  a12[0]
)
order by n