您可以通过联接表然后使用条件聚合来完成此操作：

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

假设使用大小写透视时估计表将始终生成一行...

SELECT i.name, i.col0, i.col1, (pest.mcol0*i.col0+pest.mcol1*i.col1) as score
FROM Initial
CROSS JOIN 
(select 
  max(case name when 'COL0' then estimate end) as mcol0, 
  max(case name when 'Col1' then estimate end) as mcol1 
 FROM estimates) Pest -- pivot Estimate

您可以通过将表连接为以下形式来执行此操作：

SELECT i.NAME,i.COL0,i.COL1,
       (MAX(i.COL0) * MAX(CASE WHEN e.name = 'COL0' THEN estimate end) +
        MAX(i.COL1) * MAX(CASE WHEN e.name = 'COL1' THEN estimate end)) AS SCORE
FROM INITIAL i CROSS JOIN ESTIMATES E GROUP BY i.NAME,i.COL0,i.COL1 ORDER BY NAME;

请将此SQLFiddle链接作为参考

对于未来的用户，可以使用postgresql实现矢量点积here：

CREATE OR REPLACE FUNCTION public.dot_product(IN vector1 double precision[], IN vector2 double precision[])
    RETURNS double precision    
AS $BODY$
BEGIN
    RETURN(SELECT sum(mul) FROM (SELECT v1e*v2e as mul FROM unnest(vector1, vector2) AS t(v1e,v2e)) AS denominator);
END;
$BODY$ LANGUAGE 'plpgsql';