Combinations of two elements using multiple columns with postgresql

Question

I want to get the pondered value of different combinations of only 4 variables for each row. The thing is, I have a database like this with different possible weights w for each inc_id

id|inc_id | sem_90 | sem_85 | sem_80 | t_90 | t_85 |  t_80  | time | total | w1 | w2 |  w3 | w4
1      A    0.01     0.08     0.09      0     0       0.001   0.99   0.006   0   0.1   0.01  0.08
2      A    0.01     0.08     0.09      0     0       0.001   0.99   0.006   0   0.1   0.02  0.07
3      B    ...
4      B    ...
5      C    ...

and I need to create a new column with a pondered value for each weight in inc_id like: (sem_90 * w1) + (t_90 * w2) + (time * w3) + (total * w4) but for all the possible combinations of the sem_ and t_ variables for each inc_id like:

(sem_90 * w1) + (t_85 * w2) + (time * w3) + (total * w4)

(sem_90 * w1) + (t_80 * w2) + (time * w3) + (total * w4)

etc

So my final data should look like this

inc_id | combination  | w1 | w2 |  w3  | w4 | pondered_value | 
A        sem_90 - t90   0   0.1   0.01  0.08     0.0147     
A        sem_90 - t85   0   0.1   0.01  0.08     0.0147     
A        sem_90 - t80   0   0.1   0.01  0.08     0.0148
A        sem_85 - t90   0   0.1   0.01  0.08     0.0147
A             ...
A        sem_90 - t90   0   0.1   0.02  0.07     0.024
A        sem_90 - t85   0   0.1   0.02  0.07     0.024 
A            ...        
B        sem_90 - t_90 ...

Is this possible to do this with a query in a postgre database?

Bill Jetzer · Accepted Answer · 2022-07-12 20:18:57Z

1

You can use lateral values() table joins to multiply the source rows so you get one row per sem_* + t* combination.

Something like this:

select src.inc_id
      ,sem.lbl || ' - ' || t.lbl as combination
      ,src.w1,src.w2,src.w3,src.w4
      ,sem.val * src.w1 + t.val * src.w2 + src."time" * src.w3 + src.total * src.w4
from sometable src -- change "sometable" to the name of your table
     cross join lateral (values ('sem_90',sem_90),('sem_85',sem_85),('sem_80',sem_80)) sem(lbl,val)
     cross join lateral (values (  't_90',  t_90),(  't_85',  t_85),(  't_80',  t_80))   t(lbl,val)

edited Jul 12, 2022 at 20:18

answered Jul 12, 2022 at 20:09

Bill Jetzer

1,1356 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

panky_is Over a year ago

It worked!!! Thanks a lot, I didn't kwon the lateral joins existed

Collectives™ on Stack Overflow

Combinations of two elements using multiple columns with postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related