Обсуждение: Select when table have missing data
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
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 ddatefrom generate_series(0,9) s(x) ) d ------------------------------ Extrapolate for case 2. Regards.
Hi all, Is it possible to create views that take parameters? Basically, I have to create some very complex historical reports,and as of now I am using temporary tables to store intermediate query results. I calculate values based on the contentof these temporary tables and use them in the final result. I would like to eliminate the need for temporary tablesand use views instead, as the data copy between the temp tables is taking quite some time. Thanks, Kashmira
Kashmira Patel (kupatel) wrote: > Hi all, Is it possible to create views that take parameters? > Basically, I have to create some very complex historical reports, and > as of now I am using temporary tables to store intermediate query > results. I calculate values based on the content of these temporary > tables and use them in the final result. I would like to eliminate > the need for temporary tables and use views instead, as the data copy > between the temp tables is taking quite some time. You can write a function (in SQL even) that would basically be a parameterised view. However, views don't "store" anything, so whether you see an improvement in speed will depend on how long it takes to do your calculations. -- Richard Huxton Archonet Ltd