postgresql - How can I calculate a dot product in SQL -

this similar this post, different enough i'm asking again. proposed solutions impractically slow in case.

note: working large datasets in redshift.

i trying calculate "scores" statistical model, requires calculate dot product every row in data set. in particular, create new column in table "score" multiplies values each column in first table pre-specified value column.

for example, have following tables:

initial

+--------+------+-------+ |  name  | col0 | col1  | +--------+------+-------+ | al     | 1    | 4     | | bill   | 2    | 5     | | cathy  | 3    | 6     | +--------+------+-------+ 

estimates

+--------+----------+ |  name  | estimate | +--------+----------+ | col0   | 5        | | col1   | 10       | +--------+----------+ 

and want final table,

final

+--------+------+-------+-------+ |  name  | col0 | col1  | score | +--------+------+-------+-------+ | al     | 1    | 4     |  45   | | bill   | 2    | 5     |  60   | | cathy  | 3    | 6     |  75   | +--------+------+-------+-------+ 

for final table, values each column in initial table multiplied different number depending on values listed in estimates table example, score al derived 1 * 5 + 4 * 10 = 45. 5 , 10 come estimates table.

currently, creating score column writing sql code manually writes in numbers estimates.

you can joining tables , using conditional aggregation:

select i.name,        max(i.col0) * max(case when e.name = 'col0' estimate end) col0,        max(i.col1) * max(case when e.name = 'col1' estimate end) col1,        (max(i.col0) * max(case when e.name = 'col0' estimate end) +         max(i.col1) * max(case when e.name = 'col1' estimate end)        ) score        initial cross join      estimates e group i.name;