这可能会被简化一点，但得到你想要的答案，我相信。稍微有点棘手的是获得非空值之间的天数（即你正在填充差距的大小），然后是在该间隙内的位置：

-- CTE for sample data
with your_table (emp, test_date, value) as (
            select 'A', date '2001-01-01', null from dual
  union all select 'A', date '2001-01-02', 100 from dual
  union all select 'A', date '2001-01-03', null from dual
  union all select 'A', date '2001-01-04', 80 from dual
  union all select 'A', date '2001-01-05', null from dual
  union all select 'A', date '2001-01-06', null from dual
  union all select 'A', date '2001-01-07', 75 from dual
)
-- actual query
select emp, test_date, value,
  coalesce(value,
    (next_value - prev_value) -- v3-v1
    / (count(*) over (partition by grp) + 1) -- d3-d1
    * row_number() over (partition by grp order by test_date desc) -- d2-d1, indirectly
    + prev_value -- v1
  ) as interpolated
from (
  select emp, test_date, value,
    last_value(value ignore nulls)
      over (partition by emp order by test_date) as prev_value,
    first_value(value ignore nulls)
      over (partition by emp order by test_date range between current row and unbounded following) as next_value,
    row_number() over (partition by emp order by test_date) -
      row_number() over (partition by emp order by case when value is null then 1 else 0 end, test_date) as grp
  from your_table
)
order by test_date;

E TEST_DATE       VALUE INTERPOLATED
- ---------- ---------- ------------
A 2001-01-01                        
A 2001-01-02        100          100
A 2001-01-03                      90
A 2001-01-04         80           80
A 2001-01-05              76.6666667
A 2001-01-06              78.3333333
A 2001-01-07         75           75

我使用了last_value和first_value，而不是lead和lag，但这两种方法都可以工作。（我想，在大数据集上，超前/滞后可能会更快）。grp的计算是Tabibitosan。

可以使用lag(ignore nulls)。您不指定如何进行插值，但线性插值将是：

select emp, test_date,
       coalesce(test_value,
                ( next_tv * (next_td - test_date) +
                  prev_tv * (test_date - prev_td)
                ) / (next_td - prev_td)
               ) as imputed_value
from (select t.*,
             lag(test_value ignore nulls) over (partition by emp order by test_date) as prev_tv,
             lag(case when test_value is not null then test_date end ignore nulls) over (partition by emp order by test_date) as prev_td,
             lead(test_value ignore nulls) over (partition by emp order by test_date) as next_tv,
             lead(case when test_value is not null then test_date end ignore nulls) over (partition by emp order by test_date) as next_td
      from t
     ) t

下面是使用ASC插值对SQLImpala进行修复

select emp, test_date, value,
  coalesce(value,
    (next_value - prev_value) -- v3-v1
    / (count(*) over (partition by grp) + 1) -- d3-d1
    * row_number() over (partition by grp order by test_date desc) -- d2-d1, indirectly
    + prev_value -- v1
  ) as interpolated
from (
  select emp, test_date, value,
    last_value(value ignore nulls)
      over (partition by emp order by test_date) as prev_value,
    first_value(value ignore nulls)
      over (partition by emp order by test_date range between current row and unbounded following) as next_value,
    row_number() over (partition by emp order by test_date) -
      row_number() over (partition by emp order by case when value is null then 1 else 0 end, test_date) as grp
  from test.table
) t

order by test_date