Thread: Calculating product from rows - (aggregate product )

Calculating product from rows - (aggregate product )

From
Allan Kamau
Date:
Hi

I would like to calculate a product of a field's values of a relation,
this function may multiply each value and give the result as a single
float number.

For example:


CREATE table imaginary(id INTEGER NOT NULL, some_field FLOAT
NULL,primary key(id));

INSERT INTO imarginary(1,0.333);INSERT INTO imarginary(2,0.667);INSERT
INTO imarginary(3,0.4);


SELECT prod(some_field) FROM imarginary;


would give 0.0888444 (which is 0.333*0.667*0.4)


Is there an already existing function that does this.


Allan.


Re: Calculating product from rows - (aggregate product )

From
Allan Kamau
Date:
At the moment I have two probable solutions, the first makes use of
cursors and requires looping though each record, the other a
not-so-elegant solution (and may be unfavourable for large datasets)
makes use of arrays and  the EXECUTE command  (in plpgsql).

The second solution is as follows.

DROP table imaginary;

CREATE temp table imaginary(id INTEGER NOT NULL, some_field FLOAT
NULL,primary key(id));

INSERT INTO imaginary(id,some_field)VALUES(1,0.333);INSERT INTO
imaginary(id,some_field)VALUES(2,0.667);INSERT INTO
imaginary(id,some_field)VALUES(3,0.4);INSERT INTO
imaginary(id,some_field)VALUES(4,null);


SELECT array_to_string(ARRAY(SELECT a.some_field FROM imaginary a),'*');


--within plpgsql execute the following

EXECUTE 'SELECT '||SELECT array_to_string(ARRAY(SELECT a.some_field FROM
imaginary a),'*') INTO _my_aggregated_product;


Allan.





Allan Kamau wrote:
> Hi
>
> I would like to calculate a product of a field's values of a relation,
> this function may multiply each value and give the result as a single
> float number.
>
> For example:
>
>
> CREATE table imaginary(id INTEGER NOT NULL, some_field FLOAT
> NULL,primary key(id));
>
> INSERT INTO imarginary(1,0.333);INSERT INTO imarginary(2,0.667);INSERT
> INTO imarginary(3,0.4);
>
>
> SELECT prod(some_field) FROM imarginary;
>
>
> would give 0.0888444 (which is 0.333*0.667*0.4)
>
>
> Is there an already existing function that does this.
>
>
> Allan.
>
>


Re: Calculating product from rows - (aggregate product )

From
"A. Kretschmer"
Date:
In response to Allan Kamau :
> Hi
>
> I would like to calculate a product of a field's values of a relation,
> this function may multiply each value and give the result as a single
> float number.
>
> For example:
>
>
> CREATE table imaginary(id INTEGER NOT NULL, some_field FLOAT
> NULL,primary key(id));
>
> INSERT INTO imarginary(1,0.333);INSERT INTO imarginary(2,0.667);INSERT
> INTO imarginary(3,0.4);
>
>
> SELECT prod(some_field) FROM imarginary;
>
>
> would give 0.0888444 (which is 0.333*0.667*0.4)
>
>
> Is there an already existing function that does this.

No, you need a own aggregate function, but it is easy:

test=# CREATE FUNCTION multiply_aggregate(float,float) RETURNS float AS
' select $1 * $2; ' language sql IMMUTABLE STRICT; CREATE AGGREGATE
multiply (basetype=float, sfunc=multiply_aggregate, stype=float,
initcond=1 ) ;
CREATE FUNCTION
CREATE AGGREGATE
test=*# create table float_test(a float);
CREATE TABLE
test=*# copy float_test from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 0.333
>> 0.4
>> 0.8
>> \.
test=*# select multiply(a) from float_test;
 multiply
----------
  0.10656
(1 row)



Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: Calculating product from rows - (aggregate product )

From
Jasen Betts
Date:
On 2009-05-04, Allan Kamau <allank@sanbi.ac.za> wrote:
> Hi
>
> I would like to calculate a product of a field's values of a relation,
> this function may multiply each value and give the result as a single
> float number.
>
> For example:
>
>
> CREATE table imaginary(id INTEGER NOT NULL, some_field FLOAT
> NULL,primary key(id));
>
> INSERT INTO imarginary(1,0.333);INSERT INTO imarginary(2,0.667);INSERT
> INTO imarginary(3,0.4);
>
>
> SELECT prod(some_field) FROM imarginary;
>
>
> would give 0.0888444 (which is 0.333*0.667*0.4)
>
>
> Is there an already existing function that does this.

here's one way to cheat: logarythms.

select exp(sum(ln( thiscolumn ))) from foo;

 :^)


Re: Calculating product from rows - (aggregate product )

From
"A. Kretschmer"
Date:
In response to Jasen Betts :
> > Is there an already existing function that does this.
>
> here's one way to cheat: logarythms.
>
> select exp(sum(ln( thiscolumn ))) from foo;
>
>  :^)

nice ;-)


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: Calculating product from rows - (aggregate product )

From
Dennis Brakhane
Date:
On Mon, May 4, 2009 at 12:53 PM, Jasen Betts <jasen@xnet.co.nz> wrote:
> select exp(sum(ln( thiscolumn ))) from foo;

Keep in mind that it won't work when the table containts negative
numbers, though (or zeros, but since in this case the product is also
zero, it doesn't matter)

Re: Calculating product from rows - (aggregate product )

From
Martijn van Oosterhout
Date:
On Mon, May 04, 2009 at 10:42:01AM +0200, A. Kretschmer wrote:
> No, you need a own aggregate function, but it is easy:
>
> test=# CREATE FUNCTION multiply_aggregate(float,float) RETURNS float AS
> ' select $1 * $2; ' language sql IMMUTABLE STRICT; CREATE AGGREGATE
> multiply (basetype=float, sfunc=multiply_aggregate, stype=float,
> initcond=1 ) ;
> CREATE FUNCTION
> CREATE AGGREGATE

Note, you don't have to create your own function here, since there's a
builtin called float8mul. So the following will work:

CREATE AGGREGATE multiply (basetype=float8, sfunc=float8mul, stype=float8, initcond=1);

The rest is the same.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Attachment

Re: Calculating product from rows - (aggregate product )

From
Tom Lane
Date:
Dennis Brakhane <brakhane@googlemail.com> writes:
> On Mon, May 4, 2009 at 12:53 PM, Jasen Betts <jasen@xnet.co.nz> wrote:
>> select exp(sum(ln( thiscolumn ))) from foo;

> Keep in mind that it won't work when the table containts negative
> numbers, though (or zeros, but since in this case the product is also
> zero, it doesn't matter)

Its numerical stability probably leaves something to be desired, too...
but it is a cute solution.

            regards, tom lane