From: Rodrigo De León <rdeleonp@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Select when table have missing data
Date: Thu, 8 Mar 2007 20:30:26 -0500
Message-ID: <a55915760703081730x44817d50k1a1c8b501be0ab7d@mail.gmail.com> (raw)
In-Reply-To: <1172827053.569467.79430@p10g2000cwp.googlegroups.com>
References: <1172827053.569467.79430@p10g2000cwp.googlegroups.com>
On 2 Mar 2007 01:17:33 -0800, Lars Gregersen <lars.gregersen@it.dk> wrote:
> I have a table that contains historical exchange rates:
> date_time | timestamp
> xrate | real
>
> There is a maximum of one entry per day, but data are missing on
> weekends and holidays. For these missing dates I must use the value
> from the previous day (e.g. since data for a Sunday is missing I must
> use the value from the Friday just before the weekend).
>
> I have two questions:
>
> 1) Since historical exchange rates are not supposed to change I
> thought about creating a new table with data for all the missing dates
> (calculated using some procedural language). However, I would be much
> happier if there was a way to do this using SQL in a SELECT statement.
> Do you have any hints for this?
>
> 2)
> I have a number of other tables where data may be missing for
> different reasons. These data may be on a daily or an hourly basis.
> When a user selects a range of data from e.g. the 1st of January to
> the 1st of February I would like to be able to return a full set of
> data where all missing entries are returned as NULL. Is there a smart
> way to do this using SQL?
>
> Any hints or references you may have on the subject of handling
> missing data in time series data are very welcome.
>
> If there is a smarter way to set up tables for handling this type of
> data then please enlighten me.
>
> Thanks
>
> Lars
generate_series() is your friend:
------------------------------
create table t(
date_time timestamp
, xrate real
);
insert into t values ('2007-3-7',0.23);
insert into t values ('2007-3-8',0.1);
insert into t values ('2007-3-9',0.2);
-- no '2007-3-10'
-- no '2007-3-11'
insert into t values ('2007-3-12',0.3);
insert into t values ('2007-3-13',0.4);
-- no '2007-3-14'
insert into t values ('2007-3-15',0.99);
-- no '2007-3-16'
select d.*, (
select xrate
from t
where date_time = (
select max(date_time)
from t
where date_time <= d.ddate
)
) as xrate
from (
select ('2007-3-7'::date+s.x)::timestamp as ddate
from generate_series(0,9) s(x)
) d
------------------------------
Extrapolate for case 2.
Regards.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rdeleonp@gmail.com
Subject: Re: Select when table have missing data
In-Reply-To: <a55915760703081730x44817d50k1a1c8b501be0ab7d@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox