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(dot)hsieh(at)gmail(dot)com
> <mailto:pierre(dot)hsieh(at)gmail(dot)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
> <pj(at)illuminatedcomputing(dot)com <mailto:pj(at)illuminatedcomputing(dot)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
> <rod(at)iol(dot)ie <mailto:rod(at)iol(dot)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
> > rod(at)iol(dot)ie <mailto:rod(at)iol(dot)ie>
> >
> >
> > --
> > Sent via pgsql-general mailing list
> (pgsql-general(at)postgresql(dot)org
> <mailto:pgsql-general(at)postgresql(dot)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)