Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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. All arrays will have the same number of elements, in a query. Different queries will have different number of elements.

My implementation target is: PostgreSQL 9.1.13

like image 223
François Beausoleil Avatar asked Jul 28 '14 14:07

François Beausoleil


People also ask

What is sum aggregate function?

Computes the sum of an expression over a group of rows. SUM returns a DOUBLE PRECISION value for a floating-point expression. Otherwise, the return value is the same as the expression data type.

What are the 5 aggregate functions?

There are five aggregate functions, which are: MIN, MAX, COUNT, SUM, and AVG.

Is sum a valid aggregate function?

What Is an Aggregate Function in SQL? An aggregate function in SQL performs a calculation on multiple values and returns a single value. SQL provides many aggregate functions that include avg, count, sum, min, max, etc.

How do you sum an element in an array in Python?

STEP 1: Declare and initialize an array. STEP 2: The variable sum will be used to calculate the sum of the elements. Initialize it to 0. STEP 3: Loop through the array and add each element of the array to the variable sum as sum = sum + arr[i].


1 Answers

General solutions for any number of arrays with any number of elements. Individual elements or the the whole array can be NULL, too:

Simpler in 9.4+ using WITH ORDINALITY

SELECT ARRAY (
   SELECT sum(elem)
   FROM  tbl t
       , unnest(t.arr) WITH ORDINALITY x(elem, rn)
   GROUP BY rn
   ORDER BY rn
   );

See:

  • PostgreSQL unnest() with element number

Postgres 9.3+

This makes use of an implicit LATERAL JOIN

SELECT ARRAY (
   SELECT sum(arr[rn])
   FROM   tbl t
        , generate_subscripts(t.arr, 1) AS rn
   GROUP  BY rn
   ORDER  BY rn
   );

See:

  • What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

Postgres 9.1

SELECT ARRAY (
   SELECT sum(arr[rn])
   FROM  (
      SELECT arr, generate_subscripts(arr, 1) AS rn
      FROM   tbl t
      ) sub
   GROUP  BY rn
   ORDER  BY rn
   );

The same works in later versions, but set-returning functions in the SELECT list are not standard SQL and were frowned upon by some. Should be OK since Postgres 10, though. See:

  • What is the expected behaviour for multiple set-returning functions in SELECT clause?

db<>fiddle here
Old sqlfiddle

Related:

  • Is there something like a zip() function in PostgreSQL that combines two arrays?
like image 200
Erwin Brandstetter Avatar answered Oct 06 '22 09:10

Erwin Brandstetter