[GENERAL] how to calculate standard deviation from a table
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
Re: [GENERAL] how to calculate standard deviation from a table
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, 100) 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, 100) 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.hs...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to calculate standard deviation from a table
Are you sur you don't want a moving windows (stddev on 0 to 50 , then stdev on 1 to 51) .. If you don't want moving windows your query would look like DROP TABLE IF EXISTS your_data; CREATE TABLE your_data AS SELECT s as gid , random() as your_data_value FROM generate_series(1,1) as s ; SELECT min(gid) as min_gid, max(gid) as max_gid, stddev(your_data_value) as your_stddev FROM your_data GROUP BY (gid-1)/50 ORDER BY min_gid ASC Please note that min(gid) as min_gid, max(gid) as max_gid and ORDER BY min_gid ASC are just there to help you understand the result Cheers, Rémi-C 2015-01-22 16:49 GMT+01:00 David G Johnston david.g.johns...@gmail.com: 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
