Thread: how to calculate standard deviation from a table

Pierre Hsieh wrote
> Hi
>
> This table just has a column which type is integer. There are one million
> data in this table. I wanna calculate standard deviation on each 50 data
> by
> order. It means SD1 is from data 1 to data 50, SD2 is from data 51 to
> 100.... Is there anyone who can give me some suggestions? Thanks
>
> Pierre

Integer division

David J.



--
View this message in context:
http://postgresql.nabble.com/how-to-calculate-standard-deviation-from-a-table-tp5835031p5835042.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Hi Pierre,

How do you know in which group each row belongs? If you don't care how
the rows are grouped, you can say this:

create table foo (v float);
insert into foo select random() from generate_series(1, 1000000) s(a);
select n % 50 g, stddev(v) from (select row_number() over () n, v from
foo) x group by g;

On the other hand if you have some way of ordering the rows you could say this:

create table foo (id integer, v float);
insert into foo select a, random() from generate_series(1, 1000000) s(a);
select (n - 1) / 50 g, stddev(v), count(*) from (select row_number()
over (order by id) n, v from foo) x group by g order by g;

Yours,
Paul

On Thu, Jan 22, 2015 at 7:18 AM, Pierre Hsieh <pierre.hsieh@gmail.com> wrote:
> Hi
>
> This table just has a column which type is integer. There are one million
> data in this table. I wanna calculate standard deviation on each 50 data by
> order. It means SD1 is from data 1 to data 50, SD2 is from data 51 to
> 100.... Is there anyone who can give me some suggestions? Thanks
>
> Pierre



--
_________________________________
Pulchritudo splendor veritatis.