如何基于 (n-1) 级数据 (Oracle) 最好地计算 n 级聚合数据

Question

在此答案中（包含所有简化和记录的可执行示例代码+有用的注释）

我在那里做了一个技巧来计算下表的最后两行：

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 聚合级别技巧上填充大小写和 product（）-over（）"，我们称之为 punurofiwicapoonalt-trick （naah ...仍然要复杂）...听起来像 - 是的 - 让我们称之为色情把戏;O）（...好。。。别名卡蓬特技，如果你不喜欢它））

Answer 1

感谢 Oracle 论坛中 GregV 的MODEL语法功能提示，我可以非常简短和精确地编写这个查询，而无需色情内容。凉！

因此，要轻松检查与我的示例代码和至少 10g 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