François Beausoleil wrote
> Hi all,
>
> NOTE: Also posted to StackOverflow:
> http://stackoverflow.com/questions/24997131/pairwise-array-sum-aggregate-function
>
> I have a table with arrays as one column, and I want to sum the array
> elements together:
>
>> create table regres(a int[] not null);
>> insert into regres values ('{1,2,3}'), ('{9, 12, 13}');
>> select * from regres;
> a
> -----------
> {1,2,3}
> {9,12,13}
>
> I want the result to be:
>
> {10, 14, 16}
>
> that is: {1 + 9, 2 + 12, 3 + 13}.
>
> Does such a function already exist somewhere? The intagg extension looked
> like a good candidate, but such a function does not already exist.
>
> The arrays are expected to be between 24 and 31 elements in length, all
> elements are NOT NULL, and the arrays themselves will also always be NOT
> NULL. All elements are basic int. There will be more than two rows per
> aggregate.
>
> My implementation target is:
>
> PostgreSQL 9.1.13 on x86_64-unknown-linux-gnu, compiled by gcc
> (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
This should be doable with pl/pgsql, CREATE FUNCTION, and CREATE AGGREGATE
You basically want to write a "sum" function that accepts an array input.
You will need to explode both the stored state and the incoming data, add
them together, then reconstruct a new array to put back into the state. The
final function would just return the current contents of state.
With a different language you may be able to optimize by using a native
array capabilities of the language but with pl/pgsql I think you will have
to do the explode/rebuild.
Another implementation possibility is to keep track of the number of input
rows but simply concatenate the new data onto the end of the old data. In
the final function you would break apart (unnest) the array into a single
long set and then use division/modulo to identify which items belong
together and add them. You then convert the result back into an array and
return. This should save considerable array exploding time.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Pairwise-array-sum-aggregate-function-tp5813036p5813044.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
