Заполнить пропуски в данных, используя значение, пропорциональное расстоянию пропуска до данных из соседних строк?

Question

Заполнить пропуски в данных, используя значение, пропорциональное расстоянию пропуска до данных из соседних строк?

В какой-то момент скоро мне придется подготовить список цен на товары по дням. Детализация составляет 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() (или first?) OVER ROWS UNBOUNDED PRECEDING/FOLLOWING (с предложением null-last order), чтобы получить первые ненулевые previousding_date, follow_date, previousing_price и follow_price из необработанных данных
DATEDIFF фиктивная дата и предыдущая_дата, чтобы получить количество дней (фактически это расстояние, на которое мы находимся в промежутке, gap_progress) и расстояние в промежутке (follow_date - previousding_date)
получить следующую цену, предыдущую цену и расстояние гэпа для формулы (previousing_price + ((next_price - previousing_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 (день, DateFwd, DateBak)
gap_progress = DATEDIFF (день, дата, DateFwd)
next_price = PriceB
previousing_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

6

sql sql-server sql-server-2017

Источник

user1410664 18 сен '18 в 13:20

4 ответа

Другие вопросы по тегам sql sql-server sql-server-2017

user2270762 18 сен '18 в 13:46 2018-09-18 13:46 · Answer 1 · 2018-09-18 13:46

Ты можешь использовать 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 demo: http://rextester.com/QBL7472

ОБНОВЛЕНИЕ: Это, вероятно, медленно. Возможно, это помогает добавить and d.price is null к пунктам where в подзапросах, чтобы показать СУБД, что ей не нужно фактически искать другие записи, когда цена не равна нулю. Просто проверьте планы объяснения, чтобы увидеть, поможет ли это.

user1507566 18 сен '18 в 13:38 2018-09-18 13:38 · Answer 2 · 2018-09-18 13:38

Я бы поставил твою формулу 100 + (150 - 100 / 10) * 9) и т.д. в скалярный UDF и использовать его в постоянном вычисляемом столбце.

1

Источник

user1507566 18 сен '18 в 13:38

user806969 18 сен '18 в 14:34 2018-09-18 14:34 · Answer 3 · 2018-09-18 14:34

Это будет работать для sql-server-2012+ Test table:

DECLARE @t table

(Item char(5), Date date, Price decimal(9,1))

INSERT @t 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)

запрос

;WITH CTE as
(
  SELECT
    Item, Date, Price,
    lead(price) over(partition by Item order by Date) nextprice,
    lead(Date) over(partition by Item order by Date) nextDate
  FROM @t
), N(N) as
(
  SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))M(N)
), tally(N) as
(
  SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a,N b,N c,N d,N e,N f
)
SELECT 
  dateadd(d, coalesce(r, 0), Date) Date,
  Item, 
  CAST(price + coalesce((nextprice-price) * r 
    / datediff(d, date, nextdate), 0) as decimal(10,1)) Price
FROM CTE
OUTER APPLY
(
  SELECT top(coalesce(datediff(d, date, nextdate), 0)) 
    row_number() over (order by (select 1))-1 r
  FROM N
) z
ORDER BY item, date

Результат:

Date    Item    Price
2000-01-01  Bread   10.0
2000-01-02  Bread   9.5
2000-01-03  Bread   9.3
2000-01-04  Bread   9.1
2000-01-01  Sugar   100.0
2000-01-02  Sugar   105.0
2000-01-03  Sugar   110.0
2000-01-04  Sugar   115.0
2000-01-05  Sugar   120.0
2000-01-06  Sugar   125.0
2000-01-07  Sugar   130.0
2000-01-08  Sugar   135.0
2000-01-09  Sugar   140.0
2000-01-10  Sugar   145.0
2000-01-11  Sugar   150.0

user185647 18 сен '18 в 13:58 2018-09-18 13:58 · Answer 4 · 2018-09-18 13:58

Эти недостающие пропуски легче генерировать вместе с ценой за один раз

Итак, я начну с ваших исходных необработанных данных

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

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

; with
-- number is a tally table. here i use recursive cte to generate 100 numbers
number as
(
    select  n = 0
    union all
    select  n = n + 1
    from    number
    where   n < 99
),
-- a cte to get the Price of next date and also day diff
cte as
(
    select  *, 
            nextP = lead(P) over(partition by I order by D),
            cnt = datediff(day, D, lead(D) over(partition by I order by D)) - 1
    from    t
) 
select  I, 
        D = dateadd(day, n, D), 
        P = coalesce(c.P + (c.nextP - c.P) / ( cnt + 1) * n, c.P)
from    cte c
        cross join number n
where   n.n <= isnull(c.cnt, 0)

drop table t