On 01/21/2015 11:31 AM, Pierre Hsieh wrote:
updated rule
Inline image 1
On Thu, Jan 22, 2015 at 2:28 AM, Pierre Hsieh <pierre.hs...@gmail.com
<mailto:pierre.hs...@gmail.com>> wrote:
Thanks for your reply.
Let me to describe the purpose for this calculation roughly.
Column B is for the price of stock.
Column C & D are the slope and interception of linear regression
from Column B.
The final result which I need is the standard deviation on the
difference between stock price and the implied price from linear
regression by each 250 historical data(moving window)
Hopefully, it's clear for you to understand this calculation. Thanks
Inline image 1
On Thu, Jan 22, 2015 at 2:15 AM, Paul Jungwirth
<p...@illuminatedcomputing.com <mailto:p...@illuminatedcomputing.com>>
wrote:
Hi Pierre,
It looks like you're saying that each row has an id plus three
numeric
columns, and you want the stddev calculated from the three numeric
columns? In that case you could do this:
create table foo (id integer, a float, b float, c float);
insert into foo values (1, 2,3,4);
insert into foo values (2, 2,3,4);
select id, stddev(x) from (select id, unnest(array[a,b,c]) x
from foo)
bar group by id;
id | stddev
----+--------
1 | 1
2 | 1
(2 rows)
But if that's correct, then I think your table is badly
structured for
a relational database. It might be better to have just two
columns: an
id and *one* numeric value. Or perhaps an id and an array of
numeric
values if you really want all values in one row.
At a higher level, if you are really taking the stddev of a
sample of
size 3, you should reconsider applying statistical analysis to
your
problem at all.
I hope this helps!
Paul
On Wed, Jan 21, 2015 at 10:09 AM, Raymond O'Donnell
<r...@iol.ie <mailto:r...@iol.ie>> wrote:
> On 21/01/2015 18:02, Pierre Hsieh wrote:
>> Hi Raymond,
>>
>> Thanks for your reply. Please see detail as following.
Thanks again.
>
> Can you describe *in words* what sort of calculation you
want to do?
>
> Ray.
>
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> r...@iol.ie <mailto:r...@iol.ie>
>
>
> --
> Sent via pgsql-general mailing list
(pgsql-general@postgresql.org
<mailto:pgsql-general@postgresql.org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
_________________________________
Pulchritudo splendor veritatis.
You need to define a window function which captures 250 rows,
seqentially then apply the arithmetic stddev(b - (d *
(current-window-position % 250) + c)