pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSelect when table have missing data
2+ messages / 2 participants
[nested] [flat]
* Select when table have missing data
@ 2007-03-02 09:17 Lars Gregersen <lars.gregersen@it.dk>
2007-03-09 01:30 ` Re: Select when table have missing data Rodrigo De León <rdeleonp@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Lars Gregersen @ 2007-03-02 09:17 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Select when table have missing data
2007-03-02 09:17 Select when table have missing data Lars Gregersen <lars.gregersen@it.dk>
@ 2007-03-09 01:30 ` Rodrigo De León <rdeleonp@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Rodrigo De León @ 2007-03-09 01:30 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-03-09 01:30 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-02 09:17 Select when table have missing data Lars Gregersen <lars.gregersen@it.dk>
2007-03-09 01:30 ` Rodrigo De León <rdeleonp@gmail.com>
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