Geek Answers Handbook

Filling in the gaps in the returned request

I have a database that has the following columns.

Id    Date              Value
1    12/01/2010 09:30   127.31
1    12/01/2010 09:31   133.41
1    12/01/2010 09:32   147.54
1    12/01/2010 09:34   155.66

Essentially, I store values ​​corresponding to timestamps. However, if the value is 0, I do not store it (its a very large database and 0 happens often, so we decided not to include these lines to save space). In the above example, the 12/01/2010 09:33value is 0, so it is not saved. However, when the user queries the database, he does something like

select * from table where Id = '1' and Date > to_date('12/01/2010', 'MM/DD/YYYY')`

and I need to fill in the blanks and specify timestamps with 0 values. How can i do this?

+5
source share
1 answer
select 
   nvl(b.id,1) as id, 
   alldates.Date as Date, 
   nvl(b.value,0) as value
from 
  (select level/1440 + to_date('12/01/2010', 'MM/DD/YYYY') as Date
        from dual
        connect by level < 1440 --one day
   ) alldates
  left join
  (select * from table where Id = '1' and Date > to_date('12/01/2010', 'MM/DD/YYYY') b
  on alldates.Date = b.Date

UPDATED (response to comment):

select 
   nvl(b.id,1) as id, 
   alldates.Date as Date, 
   nvl(b.value,0) as value,
   nvl(b.value, lag(b.value) over (order by b.Date nulls last)) last_valid_value
from 
  (select level/1440 + to_date('12/01/2010', 'MM/DD/YYYY') as Date
        from dual
        connect by level < 1440 --one day
   ) alldates
  left join
  (select * from table where Id = '1' and Date > to_date('12/01/2010', 'MM/DD/YYYY') b
  on alldates.Date = b.Date
+8

More articles:


All Articles