Thread: 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.
Attachment
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