sql – 使用与周围行数据的间隙距离成比例的值填充数据中的空白?

在某个时刻,我将不得不准备一个日期的物品价格清单.粒度是1天,当有一个项目的销售时,我将平均价格以获得当天的平均值.有些日子里没有销售,而且我很适合通过拉动销售的前一次和下次销售来使用足够的近似值,并且它们之间的每一天都有一个价格从一个线性转换到另一个的价格.

想象一下原始数据是:

Item   Date       Price
Bread  2000-01-01 10
Bread  2000-01-02 9.5
Bread  2000-01-04 9.1
Sugar  2000-01-01 100
Sugar  2000-01-11 150

我可以到这里:

Item   Date       Price
Bread  2000-01-01 10
Bread  2000-01-02 9.5
Bread  2000-01-03 NULL
Bread  2000-01-04 9.1
Sugar  2000-01-01 100
Sugar  2000-01-02 NULL
Sugar  2000-01-03 NULL
Sugar  2000-01-04 NULL
Sugar  2000-01-05 NULL
Sugar  2000-01-06 NULL
Sugar  2000-01-07 NULL
Sugar  2000-01-08 NULL
Sugar  2000-01-09 NULL
Sugar  2000-01-10 NULL
Sugar  2000-01-11 150

我想去的地方是:

Item   Date       Price
Bread  2000-01-01 10
Bread  2000-01-02 9.5
Bread  2000-01-03 9.3 --being 9.5 + ((9.1 - 9.5 / 2) * 1)
Bread  2000-01-04 9.1
Sugar  2000-01-01 100
Sugar  2000-01-02 105 --being 100 + (150 - 100 / 10) * 1)
Sugar  2000-01-03 110 --being 100 + (150 - 100 / 10) * 2)
Sugar  2000-01-04 115
Sugar  2000-01-05 120
Sugar  2000-01-06 125
Sugar  2000-01-07 130
Sugar  2000-01-08 135
Sugar  2000-01-09 140
Sugar  2000-01-10 145 --being 100 + (150 - 100 / 10) * 9)
Sugar  2000-01-11 150

到目前为止我尝试了什么?只考虑;我打算做以下事情:

>拉取原始数据
>加入数字/日历表以填充稀疏数据
> LAST_VALUE()(或第一个?)O行直接前进/后续(使用nulls-last order子句)从原始数据中获取第一个非null preceding_date,following_date,preceding_price和following_price
> DATEDIFF假日期和preceding_date获得若干天(这实际上是我们之间的差距,gap_progress)和间隙距离(following_date – preceding_date)
>获得公式的下一个价格,先前价格和缺口距离(preceding_price((next_price – preceding_price)/ gap_distance)* gap_progress)

然而,我想知道是否有更简单的方法,因为我有数百万的项目日,这并不觉得它会那么高效..

我发现很多问题的例子,其中来自上一行或下一行的数据被逐字描绘以填补空白,但我不记得看到这种情况,其中尝试了某种转换.也许这种技术可以通过向前运行的涂片,复制最近的值以及向后运行的涂抹来双重应用:

Item   Date       DateFwd    DateBak     PriceF PriceB
Bread  2000-01-01 2000-01-01 2000-01-01  10     10
Bread  2000-01-02 2000-01-02 2000-01-02  9.5    9.5
Bread  2000-01-03 2000-01-02 2000-01-04  9.5    9.1
Bread  2000-01-04 2000-01-04 2000-01-04  9.1    9.1
Sugar  2000-01-01 2000-01-01 2000-01-01  100    100
Sugar  2000-01-02 2000-01-01 2000-01-11  100    150
Sugar  2000-01-03 2000-01-01 2000-01-11  100    150
Sugar  2000-01-04 2000-01-01 2000-01-11  100    150
Sugar  2000-01-05 2000-01-01 2000-01-11  100    150
Sugar  2000-01-06 2000-01-01 2000-01-11  100    150
Sugar  2000-01-07 2000-01-01 2000-01-11  100    150
Sugar  2000-01-08 2000-01-01 2000-01-11  100    150
Sugar  2000-01-09 2000-01-01 2000-01-11  100    150
Sugar  2000-01-10 2000-01-01 2000-01-11  100    150
Sugar  2000-01-11 2000-01-11 2000-01-11  150    150

这些可能为公式提供必要的数据
(preceding_price((next_price – preceding_price)/ gap_distance)* gap_progress):

> gap_distance = DATEDIFF(day,DateFwd,DateBak)
> gap_progress = DATEDIFF(day,Date,DateFwd)
> next_price = PriceB
> preceding_price = PriceF

这是我知道可以访问的数据的DDL(原始数据与日历表结合)

CREATE TABLE Data
([I] varchar(5), [D] date, [P] DECIMAL(10,5))
;

INSERT Data
([I], [D], [P])
VALUES
('Bread', '2000-01-01', 10),
('Bread', '2000-01-02', 9.5),
('Bread', '2000-01-04', 9.1),
('Sugar', '2000-01-01', 100),
('Sugar', '2000-01-11', 150);

CREATE TABLE Cal([D] DATE);
INSERT Cal VALUES
('2000-01-01'),
('2000-01-02'),
('2000-01-03'),
('2000-01-04'),
('2000-01-05'),
('2000-01-06'),
('2000-01-07'),
('2000-01-08'),
('2000-01-09'),
('2000-01-10'),
('2000-01-11');

SELECT d.i as [item], c.d as [date], d.p as [price] FROM
cal c LEFT JOIN data d ON c.d = d.d
您可以使用OUTER APPLY以非空的价格获取上一行和下一行:

select
  d.item,
  d.date,
  case when d.price is null then
    prev.price + ( (next.price - prev.price) /
                   datediff(day, prev.date, next.date) *
                   datediff(day, prev.date, d.date)
                 )
  else
    d.price
  end as price
from data d
outer apply
(
    select top(1) *
    from data d2
    where d2.item = d.item and d2.date < d.date and d2.price is not null
    order by d2.date desc
) prev
outer apply
(
    select top(1) *
    from data d2
    where d2.item = d.item and d2.date > d.date and d2.price is not null
    order by d2.date
) next;

Rextester演示:http://rextester.com/QBL7472

更新:这可能很慢.也许有助于添加并且d.price对子查询中的where子句为null,以显示DBMS在价格不为空时不必实际查找其他记录.只需查看解释计划,看看是否有帮助.

本站公众号
   欢迎关注本站公众号,获取更多程序园信息
开发小院