Postgres (Amazon RDS) how to calculate weighted average

Question

I am using an Amazon RDS Postgres database (9.4.4), and I would like to calculate the weighted mean of some data.

I have found the following extension which looks perfect for the job. https://github.com/Kozea/weighted_mean

However I am now unsure of how to install the extension, as my initial research shows that only 'supported' extensions are allowed.

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport

What options do i have for using this extension. I don't want to have to re-invent the wheel, and I am not familiar with installing any kind of function/extension within Postgres.

Thanks

Answer 1

Why you just don't issue a simple query like this:

select 
  case when sum(quantity) = 0 then 
   0 
  else 
   sum(unitprice * quantity) / sum(quantity) 
  end 
from sales;

of course you need to change your fields (unitprice, quantity)

Answer 2

The SUM(value*weight)/SUM(weight) answer is fine, but if you need to reuse it constantly it is more convenient to create an aggregate function.

CREATE FUNCTION avg_weighted_step(state numeric[], value numeric, weight numeric)
RETURNS numeric[]
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN array[state[1] + value*weight, state[2] + weight];
END;
$$ IMMUTABLE;


CREATE FUNCTION avg_weighted_finalizer(state numeric[])
RETURNS numeric
LANGUAGE plpgsql
AS $$
BEGIN
    IF state[2] = 0 THEN
        RETURN null;
    END IF;
    RETURN state[1]/state[2];
END;
$$ IMMUTABLE;


CREATE AGGREGATE avg_weighted(value numeric, weight numeric) (
    sfunc = avg_weighted_step, 
    stype = numeric[],
    finalfunc = avg_weighted_finalizer,
    initcond = '{0,0}' );

Example Usage:

$ table tmp;
v  w 
-- -
 1 2
10 1
(2 rows)

$ select avg_weighted(v, w) from tmp;
   avg_weighted    
------------------
4.0000000000000000
(1 row)

$ select avg_weighted(v, w) from tmp where v is null;
avg_weighted 
------------
           ∅
(1 row)