感谢 MODEL 语法功能 暗示 GregV 在Oracle论坛中 我可以非常简短而精确地编写此查询,而无需使用 色情 。凉!
MODEL
所以要轻松检查我的示例代码和至少差异 10克 Oracle db你只需要修改上面链接的原始脚本,方法如下:
/************************** * the original sample query base data ***************************/ ... -- all content before the last select of the original example-SQL /************************** * the original sample porno-query ***************************/ ,agg_porno as ( select descr, ... -- all the porno-query details from sum_data_lvl1 /* DESCR SUM AGG_LVL SUM_ID ---------------------------------- ---------- ------- ------ money available in 2013 33233235.3 1 MA money spent in 2013 4253235.3 1 MS money bound to contracts in 2013 34333500 1 MB money spent 2013 in % of available 12 2 MSP money bound 2013 in % of available 103 2 MBP */ ) /************************** * the new nice model-based query instead ***************************/ ,agg_model as ( select descr, trunc(s,1) as sum, agg_lvl, sum_id from sum_data_lvl1 model dimension by (sum_id) measures (descr, sum as s, agg_lvl) rules ( s['MSP'] = s['MS'] / s['MA'] * 100, s['MBP'] = s['MB'] / s['MA'] * 100 ) ) /* DESCR SUM AGG_LVL SUM_ID ---------------------------------- ---------- ------- ------ money available in 2013 33233235.3 1 MA money spent in 2013 4253235.3 1 MS money bound to contracts in 2013 34333500 1 MB money spent 2013 in % of available 12.7 2 MSP money bound 2013 in % of available 103.3 2 MBP */ select * from agg_porno where 1=0 -- change to 1=1 to see these results union all select * from agg_model where 1=1 -- change to 1=0 to hide these results