Le 2014-07-28 à 10:58, David G Johnston <david.g.johns...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
