Why is there no "product()" aggregate function in SQL? [duplicate]

Question

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

Racquelracquet

because it's not something you often want to do. Can you give an example of your intended usage?? – Danette

Just got curious.. Coming to think of it, yeah, there aren't many usages to make it a built-in one.. Still, it will be very interesting to work out a user defined function. – Racquelracquet

An example where PRODUCT as an aggregate function would be useful: rate of returns. To aggregate rates of return for several periods into a total rate of return, you need to add 1, multiply them and substract 1 at the end: r = (1 + r1) * (1 + r2) * ... * (1 + rn) - 1. – Permanent

Answer 1

If you have exponential and log functions available, then:

PRODUCT(TheColumn) = EXP(SUM(LN(TheColumn)))

Eindhoven

+(power(exp(1), 3.14159) - 3.14159)/20 – Draper

To get reasonable performance, make sure TheColumn is of type BINARY_DOUBLE, or use CAST(TheColumn AS BINARY_DOUBLE) on the fly. I've noticed a 100x performance improvement in a simple test – It

Answer 2

One can make a user-defined aggregate in SQL 2005 and up by using CLR. In Postgresql, you can do it in Postgres itself, likewise with Oracle

Mauretta

Answer 3

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

Accretion

Answer 4

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'
);

Colombi

Great solution! In order to evaluate it, I used the following query: "select sum(a), product(a) from generate_series(1,5) a;" – Miriam

Answer 5

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.

Amelina

Answer 6

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: https://mcmap.net/q/391523/-is-there-a-product-function-like-there-is-a-sum-function-in-oracle-sql
                                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
*/

Sudiesudnor

Here I posted a question how to maybe do what I did in a better way (maybe even avoiding the product()-functionality): https://mcmap.net/q/1329988/-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle/1915920 – Sudiesudnor

As I thought there exists a much nicer way to solve it and thus the product() functionality is again becoming very rare :-) ... https://mcmap.net/q/1329988/-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle – Sudiesudnor

Answer 7

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

Subservience

Why Down vote, please explain? – Subservience

What the OP seems to want is an equivalent of the PRODUCT function in Excel or on the TI-89, to multiply together all of the values in a column. Your answer doesn't do that. – Eindhoven

how do you do a 2.3*4.5*6.1*...*5.3 or PIPIPI*...*PI (with let's say 100 factors)? – Fortunato