weighted_statistics

pig build pkg pg_weighted_statistics;		# build rpm/deb

pig repo add pgsql -u   # add both repo and update cache

pig install pg_weighted_statistics;		# install via package name, for the active PG version
pig install weighted_statistics;		# install by extension name, for the current active PG version

pig install weighted_statistics -v 18;   # install for PG 18
pig install weighted_statistics -v 17;   # install for PG 17
pig install weighted_statistics -v 16;   # install for PG 16
pig install weighted_statistics -v 15;   # install for PG 15
pig install weighted_statistics -v 14;   # install for PG 14

CREATE EXTENSION weighted_statistics;

CREATE EXTENSION weighted_statistics;

-- Weighted mean
SELECT weighted_mean(ARRAY[1.0, 2.0, 3.0], ARRAY[0.2, 0.3, 0.5]);
-- 2.3

-- Weighted quantiles
SELECT weighted_quantile(ARRAY[10.0, 20.0, 30.0], ARRAY[0.3, 0.4, 0.3], ARRAY[0.25, 0.5, 0.75]);
-- {15.0, 20.0, 25.0}

-- Sparse data (auto-adds implicit zeros when sum(weights) < 1.0)
SELECT weighted_mean(ARRAY[10, 20], ARRAY[0.2, 0.3]);
-- 8.0  (equivalent to weighted_mean(ARRAY[10, 20, 0, 0], ARRAY[0.2, 0.3, 0.25, 0.25]))

-- Multiple statistics
SELECT weighted_mean(vals, weights),
       weighted_std(vals, weights, 1),
       weighted_quantile(vals, weights, ARRAY[0.05, 0.95])
FROM (SELECT array_agg(val) AS vals, array_agg(weight) AS weights FROM data) t;