Re: [SQL] Question regarding modelling for time series
Sergey, thank you very much for your hints, I will play a bit with that and maybe come back to the list. Just for clarification, I attached some more explanation and examples below. 2012/9/5 Sergey Konoplev : > I am not quite understand what is meant here. Could you please provide > more explanation and some examples. Imagine, the time series is published monthly, at the first day of a month with the value for the previous month, such like: Unemployment; release: 2011/12/01; reporting: 2011/11/01; value: 1 Unemployment; release: 2011/11/01; reporting: 2011/10/01; value: 2 Unemployment; release: 2011/10/01; reporting: 2011/09/01; value: 3 Unemployment; release: 2011/09/01; reporting: 2011/08/01; value: 4 Now, imagine, that on every release, the value for the previous month is revised, such like: Unemployment; release: 2011/12/01; reporting: 2011/11/01; value: 1 Unemployment; release: 2011/12/01; reporting: 2011/10/01; value: 2.5 Unemployment; release: 2011/11/01; reporting: 2011/10/01; value: 2 Unemployment; release: 2011/11/01; reporting: 2011/09/01; value: 3.5 Unemployment; release: 2011/10/01; reporting: 2011/09/01; value: 3 Unemployment; release: 2011/10/01; reporting: 2011/08/01; value: 4.5 Unemployment; release: 2011/09/01; reporting: 2011/08/01; value: 4 Unemployment; release: 2011/09/01; reporting: 2011/07/01; value: 5.5 So, what I have now is a time series from 2011/07/01 to 2011/11/01. The most recent observation (release) ex-post is: [1] Unemployment; release: 2011/12/01; reporting: 2011/11/01; value: 1 Unemployment; release: 2011/12/01; reporting: 2011/10/01; value: 2.5 Since the data is not revised further than one month behind, the whole series ex-post would look like that: [3] Unemployment; release: 2011/12/01; reporting: 2011/11/01; value: 1 Unemployment; release: 2011/12/01; reporting: 2011/10/01; value: 2.5 Unemployment; release: 2011/11/01; reporting: 2011/09/01; value: 3.5 Unemployment; release: 2011/10/01; reporting: 2011/08/01; value: 4.5 Unemployment; release: 2011/09/01; reporting: 2011/07/01; value: 5.5 Whereas, the "known-to-market"-series would look like that: [2] Unemployment; release: 2011/12/01; reporting: 2011/11/01; value: 1 Unemployment; release: 2011/11/01; reporting: 2011/10/01; value: 2 Unemployment; release: 2011/10/01; reporting: 2011/09/01; value: 3 Unemployment; release: 2011/09/01; reporting: 2011/08/01; value: 4 That are the series I want to get from the db. --Alex -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Question regarding modelling for time series
On Wed, Sep 5, 2012 at 11:39 AM, Alex Grund wrote: > Since the data is not revised further than one month behind, the whole > series ex-post would look like that: [3] > Unemployment; release: 2011/12/01; reporting: 2011/11/01; value: 1 > Unemployment; release: 2011/12/01; reporting: 2011/10/01; value: 2.5 > Unemployment; release: 2011/11/01; reporting: 2011/09/01; value: 3.5 > Unemployment; release: 2011/10/01; reporting: 2011/08/01; value: 4.5 > Unemployment; release: 2011/09/01; reporting: 2011/07/01; value: 5.5 As I understand 3 it is just a UNION of 1 and 2. > > Whereas, the "known-to-market"-series would look like that: [2] > > Unemployment; release: 2011/12/01; reporting: 2011/11/01; value: 1 > Unemployment; release: 2011/11/01; reporting: 2011/10/01; value: 2 > Unemployment; release: 2011/10/01; reporting: 2011/09/01; value: 3 > Unemployment; release: 2011/09/01; reporting: 2011/08/01; value: 4 > > That are the series I want to get from the db. > > > --Alex -- Sergey Konoplev a database and software architect http://www.linkedin.com/in/grayhemp Jabber: [email protected] Skype: gray-hemp Phone: +79160686204 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need to Iterate the record in plpgsql
Hi Pavel, Thanks for the links, but due to the project schedules I have changed procedure logic to use array in iteration instead of records. However, the trigger logic is really good but it seems slow as you said. The blog is really good source to understand plpgsql in better way. Thanks & Regards, Ramkumar -Original Message- From: Pavel Stehule [mailto:[email protected]] Sent: Tuesday, September 04, 2012 11:52 AM To: Yelai, Ramkumar IN BLR STS Cc: [email protected] Subject: Re: [SQL] Need to Iterate the record in plpgsql Hello http://okbob.blogspot.cz/2010/12/iteration-over-record-in-plpgsql.html http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#Iteration_over_RECORD_variable_inside_trigger Regards Pavel Stehule 2012/8/31 Yelai, Ramkumar IN BLR STS : > Hi All, > > I am facing a issue in Iterating the RECORD. > > The problem is, I would like to iterate the RECORD without using sql > query, but as per the syntax I have to use query as shown below. > > FOR target IN query LOOP > statements > END LOOP [ label ]; > > In my procedure, I have stored one of the procedure output as record, > which I am later using in another iteration. Below is the example > > > CREATE OR REPLACE FUNCTION test2() > > Rec1 RECORD; > Rec2 RECORD; > Rec3 RECORD; > > SELECT * INTO REC1 FROM test(); > > FOR REC2 IN ( select * from test3()) > LOOP > FOR REC3 IN REC2 --- this syntax does not allowed by Postgresql > LOOP > > END LOOP > END LOOP > > As per the example, How can I iterate pre stored record. > > Please let me know if you have any suggestions. > > Thanks & Regards, > Ramkumar > > > > > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
