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
