Oracle database 使用SQL转换数据 使用sql model 子句 理论试验

sql model 子句中多维数组的3个要素：分区，维，度量

1、先从现有表中生成新表，查看：

SQL> select * from service_data;

SPECIAL_SERVICE_LADY FC_PLACE			    SERVICE_DATE	PAY
-------------------- ------------------------------ ------------ ----------
fanbinbin	     hangzhou				    2011	100
liuyifei	     hangzhou				    2011       1000
kongweiyi	     hangzhou				    2012       1000
kongweiyi	     hangzhou				    2013      10000
kongweiyi	     beijing				    2012      10000
liuyifei	     beijing				    2012	100
liuyifei	     beijing				    2013       2000
fanbinbin	     beijing				    2013       2000
fanbinbin	     beijing				    2012       2000
fanbinbin	     hangzhou				    2012       2000
fanbinbin	     hangzhou				    2013       2000

SPECIAL_SERVICE_LADY FC_PLACE			    SERVICE_DATE	PAY
-------------------- ------------------------------ ------------ ----------
kongweiyi	     beijing				    2013      10000
liuyifei	     hangzhou				    2013       2000
liuyifei	     hangzhou				    2012       3000

14 rows selected.

SQL> 

2、希望做的处理，分别统计在同一place，同一lady下，不同year的cost，并预计2014的cost为前两年的cost和

select fc_place,special_service_lady,service_date,pay
from service_data
where fc_place in('hangzhou','beijing')
model
partition by (fc_place) dimension by (special_service_lady,service_date)
measures(pay)
rules
(
pay['fanbinbin',2014] = pay['fanbinbin',2012] + pay['fanbinbin',2013],
pay['kongweiyi',2014] = pay['kongweiyi',2012] + pay['kongweiyi',2013],
pay['liuyifei',2014] = pay['liuyifei',2012] + pay['liuyifei',2013])
order by special_service_lady,fc_place,service_date;

3、输出：

FC_PLACE		       SPECIAL_SERVICE_LADY SERVICE_DATE	PAY
------------------------------ -------------------- ------------ ----------
beijing 		       fanbinbin		    2012       2000
beijing 		       fanbinbin		    2013       2000
beijing 		       fanbinbin		    2014       4000
hangzhou		       fanbinbin		    2011	100
hangzhou		       fanbinbin		    2012       2000
hangzhou		       fanbinbin		    2013       2000
hangzhou		       fanbinbin		    2014       4000
beijing 		       kongweiyi		    2012      10000
beijing 		       kongweiyi		    2013      10000
beijing 		       kongweiyi		    2014      20000
hangzhou		       kongweiyi		    2012       1000

FC_PLACE		       SPECIAL_SERVICE_LADY SERVICE_DATE	PAY
------------------------------ -------------------- ------------ ----------
hangzhou		       kongweiyi		    2013      10000
hangzhou		       kongweiyi		    2014      11000
beijing 		       liuyifei 		    2012	100
beijing 		       liuyifei 		    2013       2000
beijing 		       liuyifei 		    2014       2100
hangzhou		       liuyifei 		    2011       1000
hangzhou		       liuyifei 		    2012       3000
hangzhou		       liuyifei 		    2013       2000
hangzhou		       liuyifei 		    2014       5000

20 rows selected.

SQL>