在SQL Developer/Oracle 10g中插值缺失的值

Question

在SQL Developer/Oracle 10g中插值缺失的值

3

我有一个表格，包含stop_id、sched_time和act_time，我想要填补我已经拥有的实际时间的空白（使用线性插值），基于预定时间（因此保留停靠点之间的相对时间）。所以我想要从下面这样的东西开始：

  stop_id  |  sched_time  |  act_time  |  actual
------------------------------------------------
  001      |  13:47:00    |  13:45:00  |  TRUE
  002      |  13:50:00    |  null      |  FALSE
  003      |  13:52:00    |  13:53:00  |  TRUE
  004      |  13:59:00    |  null      |  FALSE
  005      |  14:01:00    |  null      |  FALSE
  006      |  14:04:00    |  14:04:00  |  TRUE

转化为如下内容：

转化为这样的格式：

  stop_id  |  sched_time  |  act_time
-------------------------------------
  001      |  13:47:00    |  13:45:00
  002      |  13:50:00    |  13:49:48
  003      |  13:52:00    |  13:53:00
  004      |  13:59:00    |  13:59:25
  005      |  14:01:00    |  14:01:15
  006      |  14:04:00    |  14:04:00

如果要求插值方法尊重各站点之间的原始时间间隔过于困难，对act_time列进行简单线性插值可能是一个不错的起点，因为站点之间的时间差异并不太大。

提前感谢您！

注意：第一个 act_time 可以在第一个 sched_time 之前，并且可能有多个连续的行没有实际时间。

- Pete Ludlow

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Hambone · Accepted Answer

这是一种“第三好”的解决方案，因为一旦你有了实际时间，它会跟踪你比计划提前或延迟多少，并将其应用于最接近的计划时间而没有实际时间：

with q1 as (
  select
    t.stop_id, sched_time, act_time,
    nvl2(act_time, t.sched_time - t.act_time, null) ahead,
    sum (nvl2(act_time, 1, 0)) over
      (partition by 1 order by stop_id) as actual_count
  from schedule t
)
select
  stop_id, sched_time,
  act_time,
  nvl (act_time, sched_time - min (ahead) over
    (partition by actual_count)) as act_time2
from q1

搜索结果可能不完全符合您的要求，但是这可能是您可以基于此构建的内容：

STOP_ID   SCHED_TIME  ACT_TIME  ACT_TIME2
001       13:47       13:45     13:45
002       13:50                 13:48
003       13:52       13:53     13:53
004       13:59                 14:00
005       14:01                 14:02
006       14:04       14:04     14:04

-- 2014年7月24日编辑--

假设您已将时间转换为整数，如您所建议的（30秒=1），我稍微尝试了一下。这是一个可怕的解决方案，但我认为它实现了您的建议。我不确定它是否比您的过程循环更快。我很好奇它是否如此。Oracle的分析函数非常好用，但您可以看到我确实在使用它们来实现您所描述的功能：

with q1 as (
  select
    t.stop_id, t.sched_time, t.act_time,
    sum (nvl2(act_time, 1, 0)) over 
        (partition by 1 order by stop_id) as group_id,
    lead (sched_time) over (order by stop_id) as next_sched
  from schedule2 t
), q2 as (
  select
    stop_id, sched_time, act_time, group_id, next_sched,
    next_sched - sched_time as elapsed,
    row_number() over (partition by group_id order by stop_id) as stops,
    min (act_time) over (partition by group_id) as min_time,
    min (sched_time) over (partition by group_id) as min_sched
  from q1
), q3 as (
  select
    stop_id, sched_time, act_time, group_id, stops, min_time,
    min_sched, next_sched,
    sum (elapsed) over (partition by group_id order by stop_id) as elapsed,
    max (stops) over (partition by group_id) as grp_stops,
    lead (min_time, 1) over (order by stop_id) as next_grp_actual,
    lead (min_sched, 1) over (order by stop_id) as next_grp_sched
  from q2
), q4 as (
  select
    stop_id, sched_time, act_time, stops, grp_stops,
    min_time, lag (elapsed, 1, 0) over
      (partition by group_id order by stop_id) as elapsed,
    max (next_grp_sched) over (partition by group_id) - min_sched
        as time_btw_sched,
    max (next_grp_actual) over (partition by group_id) - min_time
        as time_btw_actuals
  from q3
)
select 
  stop_id, sched_time, act_time,
  nvl (act_time, min_time + (elapsed / time_btw_sched) * 
      time_btw_actuals) as act_time2
from q4

这是你的示例所得到的结果：

id     sched   actual  actual (calc)
001    1654    1650    1650
002    1660            1659.6
003    1664    1666    1666
004    1678            1678.83333333333
005    1682            1682.5
006    1688    1688    1688

我认为这个任务可以用编程语言进行封装，这样会更加简洁高效。虽然我只熟练掌握C#和Perl两种语言，但是它们都能很好地完成这个任务。