2

Let's say I have a query returning a table of production jobs, and in one column I have an array of output on each job for the past 7 days:

 sku | job | outputs
-----------------------------
 A1  | 123 | {2,4,6,5,5,5,5}
 A1  | 135 | {0,0,0,3,5,7,9}
 B3  | 109 | {3,2,3,2,3,2,3}
 C5  | 144 | {5,5,5,5,5,5,5}

How can I write a query that will group by SKU (product number) and sum the 7-day outputs by position? In this case you can see there are two production jobs for product A1: these should be consolidated into one row of the result:

 sku | outputs
--------------------------
 A1  | {2,4,6,8,10,12,14}
 B3  | {3,2,3,2,3,2,3}
 C5  | {5,5,5,5,5,5,5}
klin
  • 112,967
  • 15
  • 204
  • 232
workerjoe
  • 2,421
  • 1
  • 26
  • 49
  • Looks like duplicate of https://stackoverflow.com/questions/24997131/pairwise-array-sum-aggregate-function/24997565 – Hubbitus Apr 12 '21 at 22:33

1 Answers1

4

You should unnest the arrays with ordinality, calculate sums of elements in groups by sku and ordinality and finally aggregate the sums into arrays using ordinality in groups by sku:

select sku, array_agg(elem order by ordinality) as outputs
from (
    select sku, ordinality, sum(elem) as elem
    from jobs
    cross join unnest(outputs) with ordinality as u(elem, ordinality)
    group by 1, 2
    ) s
group by 1
order by 1

DbFiddle.

If you often need this functionality in various contexts, it may be reasonable to create a custom aggregate:

create or replace function sum_int_arrays(int[], int[])
returns int[] language sql immutable as $$
    select array_agg(coalesce(a, 0)+ b)
    from unnest($1, $2) as u(a, b)
$$;

create aggregate sum_int_array_agg(integer[]) (
    sfunc = sum_int_arrays,
    stype = int[]
);

select sku, sum_int_array_agg(outputs)
from jobs
group by 1
order by 1

DbFiddle.

klin
  • 112,967
  • 15
  • 204
  • 232
  • Worked very well. Took a while to figure out what was happening. I think the `sum_int_array_agg` function is a nicer approach because it isolates the problem from other things I'm doing in the real-world query. (In my real problem I actually have multiple columns of arrays so it took a while to adapt this.) – workerjoe Apr 24 '18 at 03:57