Thread: Question regarding modelling for time series

Question regarding modelling for time series

From
Alex Grund
Date:
Hi there,

I want to use a database for storing economic time series.

An economic time series can be thought of as something like this:

NAME            | RELEASE_DATE | REPORTING_DATE | VALUE
----------------+--------------+----------------+-------
Unemployment US | 2011/01/01   | 2010/12/01     | xxx
Unemployment US | 2011/02/01   | 2011/01/01     | xxx
Unemployment US | 2011/03/01   | 2011/02/01     | xxx

The release date is the date on which the data provider published the
value and the reporting date is the date to which the value refers
(read: In Dec, 2010 the unemployment was X but this has not been known
until 2011/01/01).

However, that's not the whole story. On each "release date" not only
ONE value is released but in some cases the values for previous
reporting_dates are changed.

So, the table could read like this:

NAME            | RELEASE_DATE | REPORTING_DATE | VALUE
----------------+--------------+----------------+-------
Unemployment US | 2011/01/01   | 2010/12/01     | xxx
Unemployment US | 2011/01/01   | 2010/11/01     | xxx
Unemployment US | 2011/01/01   | 2010/10/01     | xxx

Unemployment US | 2011/02/01   | 2010/10/01     | xxx
Unemployment US | 2011/02/01   | 2010/11/01     | xxx
Unemployment US | 2011/02/01   | 2010/12/01     | xxx
Unemployment US | 2011/02/01   | 2011/01/01     | xxx

[...]

So, there are now mainly three questions to be answered:

1) "get me the time series [reporting_date, value] of unemployment as
it is now seen", so give all reporting_date,value tuples with the most
recent release_date.

2) "get me the time series [reporting_date, value] as it was
published/known to the market", so that means, in this case, give this
list:
Unemployment US | 2011/01/01   | 2010/12/01     | xxx
Unemployment US | 2011/02/01   | 2011/01/01     | xxx
Unemployment US | 2011/03/01   | 2011/02/01     | xxx

3) the same as (1) but with one enhancement: if the most recent
release has a history of N month, but all releases has a history of
N+X month, the time series from the most recent release should be
delivered plus the older values (in terms of reporting_dates) from the
second most recent release plus the more older values from the third
most recent release and so on.


So, I thought of a relational data base model like that:

TABLE 'ts' (TimeSeries)
PK:id | name

TABLE 'rs' (ReleaseStages)
PK:id | FK:ts_id | release_date

TABLE 'r' (Releases)
PK:id | FK:rs_id | reporting_date | value

Is this an appropriate model?

If yes, how could I answer the three questions above in terms of
SQL/Stored Procedures?

If no, what would you suggest?


And: If the datasets grow further, will be an RDBMS the right model
for time series storage? Any ideas on what else I could use?



Thank you very much!


--Alex



Re: Question regarding modelling for time series

From
Sergey Konoplev
Date:
On Wed, Sep 5, 2012 at 12:16 AM, Alex Grund <st.helldiver@googlemail.com> wrote:
> So, I thought of a relational data base model like that:

It is worth to make like this

TABLE 'ts' (TimeSeries)
PK:id | name

TABLE 'r' (Releases)
PK:id | FK:ts_id | release_date | reporting_date | value

It is a little more redundant but easier to work with data.

> 1) "get me the time series [reporting_date, value] of unemployment as
> it is now seen", so give all reporting_date,value tuples with the most
> recent release_date.

SELECT * FROM r
WHERE   ts_id = 123 AND   release_date = (SELECT max(release_date) FROM r)
ORDER BY reporting_date;

> 2) "get me the time series [reporting_date, value] as it was
> published/known to the market", so that means, in this case, give this
> list:
> Unemployment US | 2011/01/01   | 2010/12/01     | xxx
> Unemployment US | 2011/02/01   | 2011/01/01     | xxx
> Unemployment US | 2011/03/01   | 2011/02/01     | xxx

If I understand it correct it will look like

SELECT DISTINCT ON (release_date) * FROM r
WHERE ts_id = 123
ORDER BY release_date, reporting_date DESC;

> 3) the same as (1) but with one enhancement: if the most recent
> release has a history of N month, but all releases has a history of
> N+X month, the time series from the most recent release should be
> delivered plus the older values (in terms of reporting_dates) from the
> second most recent release plus the more older values from the third
> most recent release and so on.

I am not quite understand what is meant here. Could you please provide
more explanation and some examples.

> And: If the datasets grow further, will be an RDBMS the right model
> for time series storage? Any ideas on what else I could use?

It will be. You will probably need some more optimization/partitioning
depending on the data distribution however it can be done later.

>
>
>
> Thank you very much!
>
>
> --Alex
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql



-- 
Sergey Konoplev

a database and software architect
http://www.linkedin.com/in/grayhemp

Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204



Re: Question regarding modelling for time series

From
Alex Grund
Date:
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 <gray.ru@gmail.com>:
> 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



Re: Question regarding modelling for time series

From
Sergey Konoplev
Date:
On Wed, Sep 5, 2012 at 11:39 AM, Alex Grund <st.helldiver@googlemail.com> 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: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204