CODEWITHSUNDEEP
sqlpostgresql
Pat Schenkel
Pat Schenkel

Reputation: 33

Postgres Standard deviation of multiple columns

I am trying to calculate the standard deviation of multiple columns for each given ID.

I have a table that shows gives me demand over time and I need to calculate volatility of the demand.

SELECT id ::Text, n0 ::numeric, n1 ::numeric, n2 ::Numeric, n3 ::numeric, n4 ::numeric, n5 ::numeric, n6 ::numeric, n7 ::numeric  
FROM mytable

I would like to add another column that calculates the standard deviation from the values in in columns n0-n7 for each id.

Upvotes: 1

Views: 2878

Answers (3)

Inon
Inon

Reputation: 2376

Have you tried stddev_samp? Or do you mean the standard deviation between columns? If so, take a look at this SO question.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Hmmm. Probably the easiest way is to unpivot the table and re-aggregate. For instance:

select id, stddev(n)
from ((select id, n0 as n from mytable) union all
      (select id, n1 as n from mytable) union all
      (select id, n2 as n from mytable) union all
      (select id, n3 as n from mytable) union all
      (select id, n4 as n from mytable) union all
      (select id, n5 as n from mytable) union all
      (select id, n6 as n from mytable) union all
      (select id, n7 as n from mytable)
     ) t
group by id;

Note: you can choose between stddev_pop() or stddev_samp(). The latter would be the typical choice for this problem.

Upvotes: 2

klin
klin

Reputation: 121754

To use stddev_pop() aggregate function you should gather the values into an array and next unnest() the array:

select id, stddev_pop(val)
from (
    select id, unnest(array[n0, n1, n2, n3, n4, n5, n6, n7]) val
    from mytable
    ) sub
group by id;

Upvotes: 1

Related Questions