Re: Pairwise array sum aggregate function?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 07/28/2014 07:09 AM, 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?

You might try PL/R:


create table regres(grp int, a int[] not null);
insert into regres values
  (1,'{1, 2, 3}'),
  (1,'{9, 12, 13}'),
  (1,'{4, 2, 3}'),
  (2,'{4, 5, 6}'),
  (2,'{7, 8, 9}')
;


create or replace function plr_pairwise_sum(a int[], b int[]) returns
int[] as $$
  if (is.null(a))
    return(b)
  else
    return(a + b)
$$ language plr;

CREATE AGGREGATE pairwise_sum (int[])
(
  sfunc = plr_pairwise_sum,
  stype = int[]
);

select grp, pairwise_sum(a) from regres group by grp;
 grp | pairwise_sum
- -----+--------------
   1 | {14,16,19}
   2 | {11,13,15}
(2 rows)


HTH,

Joe

- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJT1mxmAAoJEDfy90M199hlvUgQAKWM46GMNsTmOn1VSbEx7FKq
cbolbtGkxuxcuv9bAf/PEEJlQ8RFYKPm7CiBX0V1etKx9brUF2eSnOdwk2vf7irt
T15f9dMfgz7Gh9Ehwa23Qut2PBcqIP4OQ+GCYtxCMn+OnwxrETMkNDFXwxw4P4ZI
IfluCfSdZXfDfh/3VGzNJX8dvCc6iLOTYjCCS7TY4RErgzO+rbdNS+zBbYP+uEo2
e8LuWJMR0Lllh7tEJjLR7aOWPKuy5ytIKgiaWHPHi5kL05VxMNcJc69upcWuOmy6
ITnMA2k3s+QWTNaBUTSONSz7d6v1N5uW2JiOy7tE4KfPsc+rPTa/DbII2W3/rzEX
kc3+EgeemW36Z1kMXnBS1JbrdktcXxDAF9MgnA754chE408+hmwwCynaZ3DpJO+g
1R2ui9f11sZXPRbI39egjO2nAd1QCyk5dXDuZ9l2iDmuv1pBO48Bg+orYExSogXZ
D0/Qbe5DHztw7HUkMve57b6h32dedZN4U2/2kDRjzs47C4v9FnhG88IVl5vizEaX
dxBHNIMG6YlI764koDWXH4NsPVOeL15JoUCln5b9DixoTXSHjdjxFWp+tKRiP8ih
me1lL06BBNR7grP877zkB/ld8F4mbMSROFiOH8vUKKKkXYLCBxNyI1sdgg+Q+Wr3
sN1RckyaWHAVVaWRJmkf
=8783
-----END PGP SIGNATURE-----