Рекурсивные вычисления заполнения с CTE или чем-то еще эффективным

Пожалуйста, помогите мне с идеями (желательно CTE), чтобы решить эту проблему как можно эффективнее.

введите здесь описание изображения

Итак... В показанной таблице ячейки в столбце «Значение», выделенные красным цветом, представляют собой известные значения, а выделенные зеленым цветом значения, которые необходимо рассчитать с помощью формул, показанных рядом с ними. Я пытаюсь понять, возможно ли это вообще с CTE.

Это как последнее известное значение и соответствующий ему интервал; следующее известное значение и соответствующий интервал; и интервал, для которого рассчитывается значение; все они используются для поиска значения, которое затем будет использоваться таким же образом для следующего неизвестного значения.


recursion tsql ssis sql-server-2008 common-table-expression
person TiredMaster    schedule 14.09.2012    source источник
comment
Я думаю, что линейная интерполяция, вы должны искать это.   -  person Paciv    schedule 14.09.2012
comment
@podiluska У меня есть циклы в .NET для этого, а также шаблон Excel, в котором будут ВСЕ формулы, чтобы сделать то же самое. Поскольку интервал является постоянным, и их соответствующие значения будут известны для некоторых конкретных интервалов, я всегда думал, что Excel легко сделать. Но я пытаюсь использовать подход, подобный процедуре, но, по возможности, без циклов. Кроме того, проблема с Excel заключается в необходимости добавлять формулы по мере добавления новых строк каждый месяц.   -  person TiredMaster    schedule 14.09.2012
comment
@Paciv Неизвестные значения должны быть рассчитаны, но не оценены. Кроме того, я думаю, что решение может быть намного проще, чем такие вычислительные затраты на интерполяцию. Тем не менее, если вы думаете, что это то, что нужно искать, пожалуйста, дайте мне наводку. Спасибо.   -  person TiredMaster    schedule 14.09.2012
comment
Всегда ли вы знаете первое и последнее значение для каждого магазина?   -  person William Salzman    schedule 15.09.2012
comment
Ваши формулы Excel представляют собой точную линейную интерполяцию окружающих существующих значений, поэтому я указал, что вы должны искать это ключевое слово и, возможно, найдете что-то полезное, вместо того, чтобы пытаться изобретать колесо в чистом SQL. Я не искал это для вас, поэтому это комментарий, а не ответ.   -  person Paciv    schedule 17.09.2012

Ответы (2)


arrow_upward
2
arrow_downward

Вот решение.

Надеюсь, поможет. :)

;with testdata(store,shipntrvl,value)
as
(
select 'abc', 1, 0.56
union all
select 'abc', 5, null
union all
select 'abc', 10, 0.63
union all
select 'abc', 15, null
union all
select 'abc', 20, null
union all
select 'abc', 25, null
union all
select 'abc', 30, 0.96
union all
select 'xyz', 1, 0.36
union all
select 'xyz', 5, 0.38
union all
select 'xyz', 10, null
union all
select 'xyz', 15, 0.46
union all
select 'xyz', 20, null
union all
select 'xyz', 25, null
union all
select 'xyz', 30, 0.91
)
,calc
as
(
select  *
        ,ROW_NUMBER() OVER(partition by store order by shipntrvl) as row_no
from testdata
)
,extra
as
(
select  *
        ,(select    top 1 row_no 
          from      calc c2 
          where     c2.row_no < c1.row_no 
            and     c1.value is null 
            and     c2.value is not null 
            and     c1.store = c2.store 
          order by c2.row_no desc) as prev_nr
        ,(select    top 1 row_no 
          from      calc c2 
          where     c2.row_no > c1.row_no 
            and     c1.value is null 
            and     c2.value is not null 
            and     c1.store = c2.store 
          order by c2.row_no asc) as next_nr
from    calc c1
)

select  c.store
        ,c.shipntrvl
        ,c.value
        ,isnull(c.value, 
            (cnext.value-cprev.value)/
            (cnext.shipntrvl-cprev.shipntrvl)*
            (c.shipntrvl-cprev.shipntrvl)+cprev.value
        ) as calculated_value
from    calc c
join    extra
    on  extra.row_no = c.row_no
    and extra.store = c.store
join    calc cnext
    on  cnext.row_no = case when c.value is null 
                            then extra.next_nr 
                            else c.row_no 
                            end
    and c.store = cnext.store
join    calc cprev
    on  cprev.row_no = case when c.value is null 
                            then extra.prev_nr 
                            else c.row_no 
                            end
    and c.store = cprev.store
person Johan    schedule 17.09.2012
comment
Мне нравится ваше предложение, оно элегантнее моего. Отличная работа! +1 - person William Salzman; 17.09.2012

arrow_upward
1
arrow_downward

Вот что у меня получилось (storevalue - это начальная таблица в вашем примере)

with knownvalues as (
select store, shipNtrvl,value
from storevalue where Value is not null
), valueranges as
 (
select 
k.store, 
k.ShipNtrvl as lowrange, 
MIN(s.ShipNtrvl) as highrange,
(select value from storevalue where store = k.store and ShipNtrvl = MIN(s.shipNtrvl))-
(select value from storevalue where store = k.store and ShipNtrvl = k.ShipNtrvl) as     term1,
MIN(s.ShipNtrvl) - k.ShipNtrvl as term2,min(k.Value) as lowval
from knownvalues k
join storevalue s on s.Value is not null and s.store= k.store and s.ShipNtrvl >     k.ShipNtrvl
group by k.store, k.shipntrvl
)
select s.store,s.ShipNtrvl,v.term1/v.term2*(s.ShipNtrvl-v.lowrange)+ v.lowval as value
from storevalue s join valueranges v on v.store = s.store and s.ShipNtrvl between   v.lowrange and v.highrange
where s.Value is null
union 
select * from storevalue where value is not null

Просто измените выбор на обновление, чтобы записать значения в таблицу.

person William Salzman    schedule 15.09.2012