sql - 使用与周围行数据的间隙距离成比例的值来填充数据间隙？

Question

很快的某个时候，我将不得不准备一份几天内元素的价格 list 。粒度为 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()(或第一个？)OVER ROWS UNBOUNDED PRECEDING/FOLLOWING(带有 nulls-last order 子句)，从原始数据中获取第一个非空 previous_date、following_date、preceding_price 和 following_price
• DATEDIFF 假日期和 previous_date 来获取天数(这实际上是我们跨过间隙的距离，gap_progress)和间隙距离(following_date - previous_date)
• 根据公式 (preceding_price + ((next_price - previous_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 - previous_price)/gap_distance) * gap_progress):

• gap_distance = DATEDIFF(日、DateFwd、DateBak)
• gap_progress = DATEDIFF(日、日期、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

Answer 1

您可以使用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

更新:这可能很慢。也许将 and d.price is null 添加到子查询中的 where 子句会有所帮助，以向 DBMS 表明当价格不为空时，它不必实际查找其他记录。只需检查解释计划看看是否有帮助。