Organizational Research By

Surprising Reserch Topic

why is there no product aggregate function in sql


why is there no product aggregate function in sql  using -'sql,oracle,aggregate-functions'

When there are Sum(), min(), max(), avg(), count() functions, can someone help understand why there is no product() built-in function. And what will be the most efficient user-implementation of this aggregate function ?

Thanks,
Trinity
    

asked Sep 15, 2015 by VetWills
0 votes
5 views



Related Hot Questions

5 Answers

0 votes

I'll focus on the question why it's not a standard function.

  • Aggregate function are basic statistical functions and product is not
  • Applied to common numerical data, the result will be in most cases out of range (overflow) so it is of little general use
answered Sep 15, 2015 by Rayford16B25
0 votes

You can simulate product() using cursors. If you let us know which database platform you're using, then we might be able to give you some sample code.

answered Sep 15, 2015 by AlmaCrocker
0 votes

I can confirm that it is indeed rare to use a product() aggregate function, but I have a quite valid example, especially working with highly aggregated data that must be presented to users in a report.

It utilizes the exp(sum(ln( multiplyTheseColumnValues ))) "trick" as mentioned in another post and other internet sources.

The report (which should care about the display, and contain as least data calculation logic as possible, to provide better maintainability and flexibility) is basically displaying this data along with some graphics:

 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

(In real life its a bit more complex and used in state budget scenarios.)

It aggregates quite some complex data found in the first 3 rows. I do not want to calculate the percentage values of the following rows (4th and 5th) by:

  • doing it in the quite dumb (as it should be) report (which just takes any number of such rows with a descripiton descr and a number sum) with some fancy logic (using JasperReports, BIRT Reports or alike)
  • neither do I want to calculate the underlying data (money available, money spent, money bound) multiple times (since these are quite expensive operations) just to calculate the percentage values

So I used another trick involving the use of the product()-functionality. (If somebody does know of a better way to achive this considering the above mentioned restrictions, I would be happy to know :-) )

The whole simplified example is available as one executable SQL below. Maybe it could help convice some Oracle guys that this functionality is not as rare, or not worth providing, as it may seem at first thoughts.

with 
    -- we have some 10g database without pivot/unpivot functionality

    -- what is interesting for various summary reports
    sum_data_meta as (  
        select            'MA' as sum_id, 'money available in 2013' as descr,   1 as agg_lvl  from dual
        union all select  'MS',           'money spent in 2013',                1             from dual
        union all select  'MB',           'money bound to contracts in 2013',   1             from dual
        union all select  'MSP',          'money spent 2013 in % of available', 2             from dual
        union all select  'MBP',          'money bound 2013 in % of available', 2             from dual
    )
    /* select * from sum_data_meta
     SUM_ID DESCR                              AGG_LVL
     ------ ---------------------------------- -------
     MA     money available in 2013                  1
     MS     money spent in 2013                      1
     MB     money bound to contracts in 2013         1
     MSP    money spent 2013 in % of available       2
     MBP    money bound 2013 in % of available       2
    */

    -- 1st level of aggregation with the base data (the data actually comes from complex (sub)SQLs)
    ,sum_data_lvl1_base as (
        select            'MA' as sum_id,  33233235.3 as sum  from dual
        union all select  'MS',            4253235.3          from dual
        union all select  'MB',            34333500           from dual
    )
    /* select * from sum_data_lvl1_base
     SUM_ID SUM
     ------ ----------
     MA     33233235.3
     MS      4253235.3
     MB     34333500.0
 */

    -- 1st level of aggregation with enhanced meta data infos
    ,sum_data_lvl1 as (
        select
            m.descr,
            b.sum,
            m.agg_lvl,
            m.sum_id
        from sum_data_meta m
            left outer join sum_data_lvl1_base b on (b.sum_id=m.sum_id)
    )
    /* select * 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.0       1 MB
     money spent 2013 in % of available          -       2 MSP
     money bound 2013 in % of available          -       2 MBP
    */

select
    descr,

    case  
        when agg_lvl < 2 then  sum
        when agg_lvl = 2 then  -- our level where we have to calculate some things based on the previous level calculations < 2
            case 
                when sum_id = 'MSP' then  
                    -- we want to calculate MS/MA by tricky aggregating the product of 
                    -- (MA row:) 1/33233235.3 * (MS:) 4253235.3/1 * (MB:) 1/1 * (MSP:) 1/1 * (MBP:) * 1/1
                    trunc(  -- cut of fractions, e.g. 12.7981 => 12
                        exp(sum(ln(  -- trick simulating product(...) as mentioned here: a/404761/1915920
                                case when sum_id = 'MS' then sum else 1 end
                            / case when sum_id = 'MA' then sum else 1 end
                        )) over ())  -- "over()" => look at all resulting rows like an aggregate function  
                        * 100  -- % display style
                  )
                when sum_id = 'MBP' then
                    -- we want to calculate MB/MA by tricky aggregating the product as shown above with MSP 
                    trunc(
                        exp(sum(ln(
                                case when sum_id = 'MB' then sum else 1 end
                            / case when sum_id = 'MA' then sum else 1 end
                        )) over ())  
                        * 100
                  )
                else -1  -- indicates problem
            end
        else null  -- will be calculated in a further step later on
    end as sum,

    agg_lvl,
    sum_id
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
*/
answered Sep 15, 2015 by IngeMacdermo
0 votes

It's probably left out because most people don't need it and it can be defined easily in most databases.

Solution for PostgreSQL:

CREATE OR REPLACE FUNCTION product_sfunc(state numeric, factor numeric)
RETURNS numeric AS $$
    SELECT $1 * $2
$$ LANGUAGE sql;

CREATE AGGREGATE product (
    sfunc = product_sfunc,
    basetype = numeric,
    stype = numeric,
    initcond = '1'
);
answered Sep 15, 2015 by JefMonteneg
0 votes

Since the Product is noting but the multiple of SUM, so in SQL they didnot introduce the Product aggregate function

For example: 6 * 4 can be acheived by

either adding 6, 4 times to itself like 6+6+6+6

or

adding 4, 6 times to itself like 4+4+4+4+4+4

thus giving the same result

answered Sep 15, 2015 by JewAviles

...