在这个答案中(带有所有可执行的简化和记录的示例代码+有用的注释)
我在那里做了一种技巧来计算下表的最后两行:
DESCR SUM
---------------------------------- ----------
money available in 2013 33233235.3
money spent in 2013 4253235.3
money bound to contracts in 2013 34333500
money spent 2013 in % of available 12
money bound 2013 in % of available 103
有人知道做这些操作的更好的方法(性能,代码量,易理解性)吗?
(比起使用应用的“提供联合的空行,在n + 1聚合级别上添加case和product()-over()-技巧”,我们将其称为punurofiwicapoonalt -trick(naah ...仍然很复杂)..听起来像-是的-让我们称其为色情-特技; O)(...好的...别名capoon-特技,如果您不喜欢它的话))
借助Oracle论坛中GregV的MODEL
语法功能提示,我可以编写非常短而精确的查询,而无需色情。凉爽的!
因此,要轻松检查我的示例代码和至少10g的Oracle数据库的区别,您只需按以下方法修改上面链接的原始脚本即可:
/**************************
* 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
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句