Обсуждение: Fwd: Ask for a question
Hi,
Would you please tell me whether PostgreSQL can execute the following tasks? If not, please also tell me which one can help me for that. Thanks
謝宗翰 | 台北富邦銀行 風險管理部 | 10686台北市大安區仁愛路四段169號12樓| (02) 27716699 分機 62853
Pierre Hsieh | Taipei Fubon Bank | Risk Management Department | +886 2 27716699 ext 62853
Вложения
On 21/01/2015 14:38, Pierre Hsieh wrote: > > > Hi, > > > > Would you please tell me whether PostgreSQL can execute the following > tasks? If not, please also tell me which one can help me for that. Thanks Not clear what you're asking, but if you just want to find the standard deviation of a sample then that's no problem: http://www.postgresql.org/docs/9.4/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE Hope this helps, Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
More bluntly maybe :
if you can do it in Excel,Cheers,
Rémi-C
2015-01-21 16:37 GMT+01:00 Raymond O'Donnell <rod@iol.ie>:
On 21/01/2015 14:38, Pierre Hsieh wrote:
>
>
> Hi,
>
>
>
> Would you please tell me whether PostgreSQL can execute the following
> tasks? If not, please also tell me which one can help me for that. Thanks
Not clear what you're asking, but if you just want to find the standard
deviation of a sample then that's no problem:
http://www.postgresql.org/docs/9.4/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE
Hope this helps,
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
This is not quite true. I don't believe there are any flight simulator easter-eggs hidden inside the Postgres code. :) On Wed, Jan 21, 2015 at 10:59 AM, Rémi Cura <remi.cura@gmail.com> wrote: > More bluntly maybe : > > if you can do it in Excel, > you can do it in Postgres. > > Cheers, > Rémi-C > > 2015-01-21 16:37 GMT+01:00 Raymond O'Donnell <rod@iol.ie>: >> >> On 21/01/2015 14:38, Pierre Hsieh wrote: >> > >> > >> > Hi, >> > >> > >> > >> > Would you please tell me whether PostgreSQL can execute the following >> > tasks? If not, please also tell me which one can help me for that. >> > Thanks >> >> >> Not clear what you're asking, but if you just want to find the standard >> deviation of a sample then that's no problem: >> >> >> http://www.postgresql.org/docs/9.4/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE >> >> Hope this helps, >> >> Ray. >> >> >> -- >> Raymond O'Donnell :: Galway :: Ireland >> rod@iol.ie >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > >
On 21/01/2015 16:06, Brian Dunavant wrote: > This is not quite true. I don't believe there are any flight > simulator easter-eggs hidden inside the Postgres code. :) No? Awww..... :-) Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
Hi guys,
![Inline image 1](/media/maillist_attaches/pgsql-general/2015/01/22/CADT0GevDjtGH546uZ5QVNbDHj1sMv7xvs52dCXqJqU2EMCDx2A@mail.gmail.com/image.png)
Thanks for your replies.
I certainly can use VBA and ADODB object in Excel to do it. Due to performance, I wanna try to do it by SQL command in PG. However, I am not expert in PG, so I need few help from your guys. Let me to describe my question clearly as following.
The final results which I wanna get are STDDEV(a1,a2,a3,...), STDDEV(b1,b2,b3,...), ..etc. I definitely know how to use standard deviation function in PG, but the critical problem for me is how to calculate a1, a2, a3, b1, b2, b3...etc. The rule to calculate them is a little complicated because it needs not only moving window function but also few logical rules. Please see details as following or attachment. I put some colors in rules. Hopefully, it's easier for you guys to read them. Thanks
I really need the helps from your guys. Please give me some suggestions. Thanks.
Pierre
![Inline image 1](/media/maillist_attaches/pgsql-general/2015/01/22/CADT0GevDjtGH546uZ5QVNbDHj1sMv7xvs52dCXqJqU2EMCDx2A@mail.gmail.com/image.png)
On Thu, Jan 22, 2015 at 12:42 AM, Raymond O'Donnell <rod@iol.ie> wrote:
On 21/01/2015 16:06, Brian Dunavant wrote:
> This is not quite true. I don't believe there are any flight
> simulator easter-eggs hidden inside the Postgres code. :)
No? Awww..... :-)
Вложения
On 21/01/2015 17:32, Pierre Hsieh wrote: > Hi guys, > > Thanks for your replies. > > I certainly can use VBA and ADODB object in Excel to do it. Due to > performance, I wanna try to do it by SQL command in PG. However, I am > not expert in PG, so I need few help from your guys. Let me to describe > my question clearly as following. > > The final results which I wanna get > are STDDEV(a1,a2,a3,...), STDDEV(b1,b2,b3,...), ..etc. I definitely know > how to use standard deviation function in PG, but the critical problem > for me is how to calculate a1, a2, a3, b1, b2, b3...etc. The rule to > calculate them is a little complicated because it needs not only moving > window function but also few logical rules. Please see details as > following or attachment. I put some colors in rules. Hopefully, it's > easier for you guys to read them. Thanks Sorry, either I'm being stupid or your description isn't clear. Can you show your table structure, ideally with some sample data, and what you hope to get from the query? Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
Hi Raymond,
Thanks for your reply. Please see detail as following. Thanks again.
Pierre
![Inline image 1](/media/maillist_attaches/pgsql-general/2015/01/22/CADT0GeuC=XJUtx-fzQ=LO5Kj74BZBVB=iQVZE_jpqp438fNwUQ@mail.gmail.com/image.png)
On Thu, Jan 22, 2015 at 1:48 AM, Raymond O'Donnell <rod@iol.ie> wrote:
On 21/01/2015 17:32, Pierre Hsieh wrote:
> Hi guys,
>
> Thanks for your replies.
>
> I certainly can use VBA and ADODB object in Excel to do it. Due to
> performance, I wanna try to do it by SQL command in PG. However, I am
> not expert in PG, so I need few help from your guys. Let me to describe
> my question clearly as following.
>
> The final results which I wanna get
> are STDDEV(a1,a2,a3,...), STDDEV(b1,b2,b3,...), ..etc. I definitely know
> how to use standard deviation function in PG, but the critical problem
> for me is how to calculate a1, a2, a3, b1, b2, b3...etc. The rule to
> calculate them is a little complicated because it needs not only moving
> window function but also few logical rules. Please see details as
> following or attachment. I put some colors in rules. Hopefully, it's
> easier for you guys to read them. Thanks
Sorry, either I'm being stupid or your description isn't clear. Can you
show your table structure, ideally with some sample data, and what you
hope to get from the query?
Вложения
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@iol.ie
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@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 > rod@iol.ie > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- _________________________________ Pulchritudo splendor veritatis.
On 01/21/2015 11:02 AM, Pierre Hsieh wrote:
If you have a four column table as defined in your spreadsheet, with column names A,B,C,DHi Raymond,Thanks for your reply. Please see detail as following. Thanks again.PierreOn Thu, Jan 22, 2015 at 1:48 AM, Raymond O'Donnell <rod@iol.ie> wrote:On 21/01/2015 17:32, Pierre Hsieh wrote:
> Hi guys,
>
> Thanks for your replies.
>
> I certainly can use VBA and ADODB object in Excel to do it. Due to
> performance, I wanna try to do it by SQL command in PG. However, I am
> not expert in PG, so I need few help from your guys. Let me to describe
> my question clearly as following.
>
> The final results which I wanna get
> are STDDEV(a1,a2,a3,...), STDDEV(b1,b2,b3,...), ..etc. I definitely know
> how to use standard deviation function in PG, but the critical problem
> for me is how to calculate a1, a2, a3, b1, b2, b3...etc. The rule to
> calculate them is a little complicated because it needs not only moving
> window function but also few logical rules. Please see details as
> following or attachment. I put some colors in rules. Hopefully, it's
> easier for you guys to read them. Thanks
Sorry, either I'm being stupid or your description isn't clear. Can you
show your table structure, ideally with some sample data, and what you
hope to get from the query?
select rowsum = B - (C*A) +D;
might get you the sums you need but I don't understand the grouping of these for the STDDEV function
Вложения
Hi Pierre, Looking at your Excel document I think I misinterpreted, and you are trying to take the stddev of each column separately (which makes a lot more sense!). In the case you can say this: select id, stddev(a), stddev(b), stddev(c) from foo group by id; Paul On Wed, Jan 21, 2015 at 10:15 AM, Paul Jungwirth <pj@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 <rod@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 >> rod@iol.ie >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > > > -- > _________________________________ > Pulchritudo splendor veritatis. -- _________________________________ Pulchritudo splendor veritatis.
Oh sorry, you should leave off the grouping: select stddev(a), stddev(b), stddev(c) from foo; Paul On Wed, Jan 21, 2015 at 10:24 AM, Paul Jungwirth <pj@illuminatedcomputing.com> wrote: > Hi Pierre, > > Looking at your Excel document I think I misinterpreted, and you are > trying to take the stddev of each column separately (which makes a lot > more sense!). In the case you can say this: > > select id, stddev(a), stddev(b), stddev(c) from foo group by id; > > Paul > > > On Wed, Jan 21, 2015 at 10:15 AM, Paul Jungwirth > <pj@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 <rod@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 >>> rod@iol.ie >>> >>> >>> -- >>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-general >> >> >> >> -- >> _________________________________ >> Pulchritudo splendor veritatis. > > > > -- > _________________________________ > Pulchritudo splendor veritatis. -- _________________________________ Pulchritudo splendor veritatis.
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](/media/maillist_attaches/pgsql-general/2015/01/22/CADT0Gev6cn2N45yAhUMOi9h66o_m_+x6D7BagAH50L1bqq3q=Q@mail.gmail.com/image.png)
![Inline image 1](/media/maillist_attaches/pgsql-general/2015/01/22/CADT0Gev6cn2N45yAhUMOi9h66o_m_+x6D7BagAH50L1bqq3q=Q@mail.gmail.com/image.png)
On Thu, Jan 22, 2015 at 2:15 AM, Paul Jungwirth <pj@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 <rod@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
> rod@iol.ie
>
>--> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
_________________________________
Pulchritudo splendor veritatis.
Вложения
updated rule
![Inline image 1](/media/maillist_attaches/pgsql-general/2015/01/22/CADT0GetX8v-=ysFiMMw1uucDhhtbZT0nJ0Dwso7n7kuOutOkOw@mail.gmail.com/image.png)
![Inline image 1](/media/maillist_attaches/pgsql-general/2015/01/22/CADT0GetX8v-=ysFiMMw1uucDhhtbZT0nJ0Dwso7n7kuOutOkOw@mail.gmail.com/image.png)
On Thu, Jan 22, 2015 at 2:28 AM, Pierre Hsieh <pierre.hsieh@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. ThanksOn Thu, Jan 22, 2015 at 2:15 AM, Paul Jungwirth <pj@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 <rod@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
> rod@iol.ie
>
>--> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
_________________________________
Pulchritudo splendor veritatis.
Вложения
On 01/21/2015 11:31 AM, Pierre Hsieh wrote:
You need to define a window function which captures 250 rows, seqentially then apply the arithmetic stddev(b - (d * (current-window-position % 250) + c)updated ruleOn Thu, Jan 22, 2015 at 2:28 AM, Pierre Hsieh <pierre.hsieh@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. ThanksOn Thu, Jan 22, 2015 at 2:15 AM, Paul Jungwirth <pj@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 <rod@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
> rod@iol.ie
>
>--> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
_________________________________
Pulchritudo splendor veritatis.