areal apportionment of SUM with weight

areal sum.sql

/*
	generic areal apportionment of SUM
*/

--table1.id
--table1.geom
--table1.value

--table2.geom
--table2.weight

WITH dat AS (
	SELECT
		table1.id,
		-- sum( value * ( % of contributing area ) )
		SUM( table2.value * ( 
			ST_Area(ST_Intersection(table1.geom,table2.geom)) / ST_Area(table1.geom)
		) ) AS sumval
	FROM table1 JOIN table2 ON ST_Intersects(table1.geom,table2.geom)
	--exclude missing data
	--WHERE table2.value != -999
	GROUP BY table1.id
)
UPDATE table1 SET value = dat.sumval
FROM dat WHERE dat.id = table1.id