Hello,
I want to interpolate and extrapolate a quantity which i have given at certain times at certain other times.
More precisely, I do have
- one table with, for each device, a monotonically increasing parameter - for example an odometer value("January 2011: truck #1 has driven 100000 km")
- several other queries with, for each device, certain timespans (example "tire #5 was on truck #1 from 2011 to 2013").
I want to add the interpolated(linearly is sufficient, and, if possible, extrapolated) values to those queries(example "tire #5 was on truck #1 from 2011 to 2013, survived 220000 km")
Oracle Version is 11g, 11.2.0.4.0
Sample data:
with
sourcetable(deviceid, testdate, testvalue) as (
select 1, to_date('2011-01-01','yyyy-mm-dd'), 0 from dual
union all
select 1, to_date('2013-01-01','yyyy-mm-dd'), 100 from dual
union all
select 2, to_date('2011-01-01','yyyy-mm-dd'), 0 from dual
union all
select 2, to_date('2012-01-01','yyyy-mm-dd'), 100 from dual
),
samplequery(deviceid, subid, from_date, to_date) as (
select 1, 5, to_date('2011-01-01','yyyy-mm-dd'), to_date('2012-01-01','yyyy-mm-dd') from dual
union all
select 2, 6, to_date('2011-07-01','yyyy-mm-dd'), to_date('2013-01-01','yyyy-mm-dd') from dual
)
select ???
Desired output:
| deviceid | subid | from_date | to_date | start | end | distance |
| 1 | 5 | 2011-01-01 | 2012-01-01 | 0 | 50 | 50 |
| 2 | 6 | 2011-07-01 | 2013-01-01 | 50 | 200 | 150 |
Actual data is in structure very similar - about 10 queries where i'd like to add the interpolation, between 10 and 700 rows each (so, nothing big, altough the queries itself are already rather complex), and for the sourcetable around 2000 devices with around 1000 entries per device - sometimes hours apart, sometimes several months.
The problem seems to me not that exotic, but I haven't been able to find a good answer by googling - maybe i just need the right keyword to google. Can someone please point me towards a solution?