Le 2014-07-28 à 10:58, David G Johnston <david.g.johnston@gmail.com> a écrit :
> 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.
Erwin Brandstetter posted a great answer on StackOverflow http://stackoverflow.com/a/24997565/7355
SELECT ARRAY(
SELECT sum(arr[rn])
FROM (
SELECT arr, generate_subscripts(arr, 1) rn
FROM tbl t) sub
GROUP BY rn
ORDER BY rn
);
I like the simplicity of the solution, vs creating a new aggregate function.
Thanks everyone!
François Beausoleil
