Обсуждение: Calculating product from rows - (aggregate product )

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.

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.
>
>

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

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;

 :^)

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

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)

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.

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