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

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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,10000) 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.johnston@gmail.com>: