postgres weighted standard deviation (stddev)

weighted_stddev.sql

CREATE OR REPLACE FUNCTION weighted_stddev_state(state numeric[], val numeric, weight numeric) RETURNS numeric[3] AS
$$
BEGIN
        IF weight IS NULL OR val IS NULL
        THEN RETURN state;
        ELSE RETURN ARRAY[state[1]+weight, state[2]+val*weight, state[3]+val^2*weight];
        END IF;
END;
$$
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION weighted_stddev_combiner(state numeric[], numeric, numeric) RETURNS numeric AS
$$
BEGIN
        RETURN sqrt((state[3]-(state[2]^2)/state[1])/(state[1]-1));
END;
$$
LANGUAGE plpgsql;


CREATE AGGREGATE weighted_stddev(var numeric, weight numeric)
(
        sfunc = weighted_stddev_state,
        stype = numeric[3],
        finalfunc = weighted_stddev_combiner,
        initcond = '{0,0,0}',
        finalfunc_extra
);
COMMENT ON AGGREGATE weighted_stddev(numeric, numeric) IS 'Usage: select weighted_stddev(var::numeric, weight::numeric) from X;';

Ça ressemble à du postgis