How to best calculate n-level aggregation data based on (n-1)-level data (Oracle)

Question

In this answer (with all executable simplified and documented sample code + helpful comments)

I did a kind of a trick there to calculate the last two rows of the following table:

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

Does anybody know of a better (performance, amount of code, understandability) way to do these kind of operations?

(Than to use the applied "provide unioned null row, fill with case and product()-over() on n+1 aggregation level-trick", let's call it punurofiwicapoonalt-trick (naah ... still to complicated) ... sounds like - yeah - let's call it just porno-trick ;O) (... okay ... alias capoon-trick if you do not like it))

Harbard

it is much more appropriate for dba.stackexchange.com – Harbard

Answer 1

Thanks to the MODEL syntax feature hint from GregV in the Oracle forum I could write this query really very short and precise without the need for porno. Cool!

So to easily check out the difference with my sample code and an at least 10g Oracle db you just need to modify the original script linked above the following way:

        /**************************
         * 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

Harbard