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

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

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

SQL> select * from service_data;SPECIAL_SERVICE_LADY FC_PLACE    SERVICE_DATEPAY-------------------- ------------------------------ ------------ ----------fanbinbin     hangzhou    2011100liuyifei     hangzhou    2011       1000kongweiyi     hangzhou    2012       1000kongweiyi     hangzhou    2013      10000kongweiyi     beijing    2012      10000liuyifei     beijing    2012100liuyifei     beijing    2013       2000fanbinbin     beijing    2013       2000fanbinbin     beijing    2012       2000fanbinbin     hangzhou    2012       2000fanbinbin     hangzhou    2013       2000SPECIAL_SERVICE_LADY FC_PLACE    SERVICE_DATEPAY-------------------- ------------------------------ ------------ ----------kongweiyi     beijing    2013      10000liuyifei     hangzhou    2013       2000liuyifei     hangzhou    2012       300014 rows selected.SQL> 

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

select fc_place,special_service_lady,service_date,payfrom service_datawhere fc_place in('hangzhou','beijing')modelpartition 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_DATEPAY------------------------------ -------------------- ------------ ----------beijing        fanbinbin    2012       2000beijing        fanbinbin    2013       2000beijing        fanbinbin    2014       4000hangzhou       fanbinbin    2011100hangzhou       fanbinbin    2012       2000hangzhou       fanbinbin    2013       2000hangzhou       fanbinbin    2014       4000beijing        kongweiyi    2012      10000beijing        kongweiyi    2013      10000beijing        kongweiyi    2014      20000hangzhou       kongweiyi    2012       1000FC_PLACE       SPECIAL_SERVICE_LADY SERVICE_DATEPAY------------------------------ -------------------- ------------ ----------hangzhou       kongweiyi    2013      10000hangzhou       kongweiyi    2014      11000beijing        liuyifei     2012100beijing        liuyifei     2013       2000beijing        liuyifei     2014       2100hangzhou       liuyifei     2011       1000hangzhou       liuyifei     2012       3000hangzhou       liuyifei     2013       2000hangzhou       liuyifei     2014       500020 rows selected.SQL>