CODEWITHSUNDEEP
postgresqlaggregate-functionsstandard-deviation
Hambone
Hambone

Reputation: 16397

Convert Postgres Aggregate Function to Inline Array

I've been asked how to do a standard deviation on a variable list of values within a row. For example:

select
  name, x, y, z, stddev (x, y, z)
from foo;

or

select
  order_no, a, b, c, d, e, f, stddev (a, b, c, d, e, f)
from foo;

So essentially just like min => least and max => greatest, I'd like a similar way to turn the aggregate stddev into a "normal" function.

I have been able to create a custom function to calculate standard deviation based on the standard formula, but I can't help but prefer to use the built-in function, if possible. I tried this:

CREATE OR REPLACE FUNCTION std_deviation(variadic inputs numeric[])
  RETURNS numeric AS
$BODY$
DECLARE
  result numeric;
BEGIN

  select stddev (unnest (inputs))
  into result;

  return result;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

And it complains:

ERROR: set-valued function called in context that cannot accept a set

There is no shortage of traffic on this error message, but I can't quite figure out how to apply the fix to my simple function.

Or, is there a better way to do this from the beginning?

Upvotes: 2

Views: 631

Answers (3)

Hambone
Hambone

Reputation: 16397

It turns out pgnumerics already has a function for this.

-- test=# select pgnumerics.stdev('{1345,1301,1368,1322,1310,1370,1318,1350,1303,1299}');
--       stdev
-- ------------------
--  27.4639157198435
-- (1 row)

CREATE OR REPLACE FUNCTION pgnumerics.stdev (
  X double precision []
) RETURNS double precision
AS $$
DECLARE
  s double precision;
  N integer;
  i integer;
  xx double precision;
  sx double precision;
BEGIN
  N := array_upper(X,1) - array_lower(X,1) + 1;
  xx:= 0.0;
  sx:= 0.0;
  for i in 1..N loop
    xx:= xx + X[i]*X[i];
    sx:= sx + X[i];
  end loop;
  s := sqrt((N*xx - sx*sx) / (N*(N-1.0)));
  return s;
END;
$$ LANGUAGE 'plpgsql';

http://pgnumerics.projects.pgfoundry.org/

Upvotes: 0

Pat Schenkel
Pat Schenkel

Reputation: 33

This seems to do the trick.

CREATE OR REPLACE FUNCTION public.std_deviation(VARIADIC inputs numeric[])
RETURNS numeric AS
$BODY$
DECLARE
result numeric;
BEGIN

with foo as (
select unnest (inputs) as bar
)
select stddev (bar)
into result
from foo;

return result;
end
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Upvotes: 1

pozs
pozs

Reputation: 36244

Set-returning functions (SRF) -- such as unnest -- in the SELECT clause is a PostgreSQL specific extension of the SQL standard. And usually doesn't worth to use it (because it's not what it looks like). Also, SRFs cannot be used within aggregate functions.

Use these SRF functions in the FROM clause instead (and use sub-selects where needed):

SELECT name, x, y, z, (SELECT stddev(v) FROM unnest(ARRAY[x, y, z]) v)
FROM foo

If you really want to write a function for that, use the SQL language (it's more clear & PostgreSQL can optimize their use):

CREATE OR REPLACE FUNCTION std_deviation(variadic inputs numeric[])
  RETURNS numeric AS
$BODY$
  SELECT stddev(v) FROM unnest(inputs) v
$BODY$
LANGUAGE SQL IMMUTABLE;

Upvotes: 4

Related Questions