sql - Oracle SQL 分析查询

Question

SQL> select LAST_UPDATED_DATE, ODOMETER from demo;LAST_UPDA   ODOMETER--------- ----------05-OCT-18   47174.7708-OCT-1812-OCT-18   50246.3715-OCT-1819-OCT-18   53743.1121-OCT-1822-OCT-1825-OCT-18   58789.228 rows selected.

我需要确定里程表值为空的值，这必须使用 SQL 来完成。我想做的方式是——

1. 获取里程表的前一个和下一个非空值以及天数之间的差异，我可以使用它来计算每天行驶的平均距离。

例如，在这种情况下，(50246.37 - 47174.77)/(12-OCT-18 - 05-OCT-18) = ~ 439

1. 现在有了每天的平均值，计算天数之间的差值并将其乘以平均值。

例如，(08-OCT-18 - 05-OCT-18) = 3 天，3 天 439 * 3 = 1317。因此，08-Oct-18 的值可以是 47174.77+1317 = 48491.77

现在，我需要为此编写 SQL 代码方面的帮助。

如有任何帮助，我们将不胜感激。

Answer 1

这就是我的做法。它可能有助于其他类似的问题(线性插值)，其中“值”不能假设为随时间增加。对于里程表，这个假设是完全合理的，戈登利诺夫的解决方案更简单；我为“数量”可能随时间下降或上升的其他应用程序提供此解决方案。

with
  sample_data(last_updated_date, odometer) as (
    select to_date('05-OCT-18', 'dd-MON-rr'), 47174.77 from dual union all
    select to_date('08-OCT-18', 'dd-MON-rr'), null     from dual union all
    select to_date('12-OCT-18', 'dd-MON-rr'), 50246.37 from dual union all
    select to_date('15-OCT-18', 'dd-MON-rr'), null     from dual union all
    select to_date('19-OCT-18', 'dd-MON-rr'), 53743.11 from dual union all
    select to_date('21-OCT-18', 'dd-MON-rr'), null     from dual union all
    select to_date('22-OCT-18', 'dd-MON-rr'), null     from dual union all
    select to_date('25-OCT-18', 'dd-MON-rr'), 58789.22 from dual
  )
, prep(last_updated_date, odometer, prev_date, next_date, prev_odo, next_odo) as (
    select last_updated_date, odometer,
           case when odometer is null
                then max(nvl2(odometer, last_updated_date, null))
                     over (order by last_updated_date) end,
           case when odometer is null
                then min(nvl2(odometer, last_updated_date, null))
                     over (order by last_updated_date 
                     rows between 1 following and unbounded following) end,
           last_value(odometer ignore nulls) over (order by last_updated_date),
           first_value(odometer ignore nulls) over (order by last_updated_date 
                                rows between 1 following and unbounded following)
    from   sample_data
  )
select   last_updated_date,
         nvl( odometer,
              round(prev_odo + (next_odo - prev_odo) * 
                   (last_updated_date - prev_date) / (next_date - prev_date), 2)
            ) as odometer
from     prep
order by last_updated_date
;

输出

LAST_UPDATED_DATE   ODOMETER
----------------- ----------
05-OCT-18           47174.77
08-OCT-18           48491.17
12-OCT-18           50246.37
15-OCT-18           51744.97
19-OCT-18           53743.11
21-OCT-18           55425.15
22-OCT-18           56266.17
25-OCT-18           58789.22