Comparing rows vs array elements postgres

Question

I have a table A with n rows (200+) and different numeric columns.
I have a table B with m rows (100K+) and a column called multipliers, which is of type array (REAL[]). For every row in B, this array's length is n, ie. a multiplier for every numeric variable in A. The array is sorted to match the alphabetical order of the id field in A

Table A

id | values_1 | values_2
---|----------|-------------
1  | 11.2     | 10.2
2  | 21.9     | 12.5
3  | 30.0     | 26.0
4  | 98.0     | 11.8

Table B

id      | multipliers
--------|-------------
dafcec  | {2,3,4,9}
nkjhbn  | {0,0,1,5}
ccseff  | {1,2,0,5}
ddeecc  | {0,0,0,1}

I need a query that returns the SUM( multipliers * values_1 ).

Like this:

b.id    | sum(b.multipliers*a.value_1)
--------|----------------------------------
dafcec  | 2*11.2 + 3*21.9 + 4*30.0 + 9*98.0
nkjhbn  | 0*11.2 + 0*21.9 + 1*30.0 + 5*98.0
ccseff  | 1*11.2 + 2*21.9 + 0*30.0 + 5*98.0
ddeecc  | 0*11.2 + 0*21.9 + 0*30.0 + 1*98.0

I have tried with different subquerys, LATERAL joins and UNNEST, but I can't get a working result.
Any hints? Thanks!

score 0 · Answer 1 · answered Jan 14 '18 at 19:51

the easiest, but I believe expensive way to sum array would be:

t=# with b as (select id,unnest(multipliers) u from b)
select distinct id, sum(u)over (partition by id) from b;
    id    | sum
----------+-----
 ccseff   |   8
 nkjhbn   |   6
 ddeecc   |   1
 dafcec   |  18
(4 rows)

and no fast alternative comes to my mind here...

further - If I get it right you want a cartesian product - all against all, then:

t=# with b as (select id,unnest(multipliers) u from b)
, ag as (select distinct id, sum(u)over (partition by id) from b)
select ag.sum * v1, a.id aid, ag.id idb
from ag
join a on true;
 ?column? | aid |   idb
----------+-----+----------
     89.6 |   1 | ccseff
    175.2 |   2 | ccseff
      240 |   3 | ccseff
      784 |   4 | ccseff
     67.2 |   1 | nkjhbn
    131.4 |   2 | nkjhbn
      180 |   3 | nkjhbn
      588 |   4 | nkjhbn
     11.2 |   1 | ddeecc
     21.9 |   2 | ddeecc
       30 |   3 | ddeecc
       98 |   4 | ddeecc
    201.6 |   1 | dafcec
    394.2 |   2 | dafcec
      540 |   3 | dafcec
     1764 |   4 | dafcec
(16 rows)

Thanks for your help! I edited the question to give an overview of the solution I am looking to achieve. In the end I need one `b.id` per row and one result for `sum(b.multipliers * a.values_1)`. I hope the edit helps to clarify it!! — illpack, Jan 14 '18 at 20:21

score 0 · Answer 2 · answered Jan 14 '18 at 23:18

Solved it.
It just needs to pack the values into an array and unpack them so they are comparable. It worked for me. The ORDER BY makes sure the packing occurs in the desired order.

   SELECT id, SUM (field * multiplier) result FROM 
    (
    with c as (
        SELECT array_agg( values_1 ORDER BY name ASC) val1
        from A
    ) 

    , ag as (
        select
            distinct id,
            multipliers
        from B
        )

    SELECT
        ag.id, 
        unnest(c.val1) field,
        unnest(ag.multipliers) multiplier
    FROM
        c, ag
    ) s
GROUP BY id

Comparing rows vs array elements postgres

2 Answers2