Combinations of two elements using multiple columns with postgresql-CodePudding

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?

CodePudding user response：

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)