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.
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.
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;
Have you tried stddev_samp? Or do you mean the standard deviation between columns? If so, take a look at this SO question.
