Расчет формулы с помощью дерева CTE

Question

Данные

У меня есть следующие частичные данные

id    parent    multiplier    const
--    ------    ----------    -----
 1      NULL          1.10     1.00
 2         1          1.20     2.00
 3         1          1.30     3.00
 4         1          2.40     4.00
 5         2          2.50     5.00
 6         2          2.60     6.00
 7         2          2.70    17.00
 8         3          2.80    18.00
 9         3          3.90    19.00
10         3          3.10     7.00
11         8          3.20     8.00
12         8          3.30     9.00
13         8          3.40    10.00
14         9          4.50    11.00
15        10          4.60    21.00
15        10          4.70    22.00

Который может отображаться в дереве следующим образом

1 
+-- 2
|   +-- 5 
|   +-- 6
|   +-- 7 
|     
+-- 3
|   +-- 8 
|   |   +-- 11
|   |   +-- 12
|   |   +-- 13
|   |
|   +-- 9
|   |   +-- 14
|   |
|   +-- 10
|       +-- 15
|       +-- 16
|
+-- 4

SQL для создания структуры таблицы и данных

DECLARE @table TABLE (Id int, Parent int, multiplier decimal(6,3), Const decimal(6,3));

INSERT INTO @table
SELECT  1, NULL,  1.1,   1.00 UNION
SELECT  2,    1,  1.2,   2.00 UNION
SELECT  3,    1,  1.3,   3.00 UNION
SELECT  4,    1,  2.4,   4.00 UNION
SELECT  5,    2,  2.5,   5.00 UNION
SELECT  6,    2,  2.6,   6.00 UNION
SELECT  7,    2,  2.7,  17.00 UNION
SELECT  8,    3,  2.8,  18.00 UNION
SELECT  9,    3,  3.9,  19.00 UNION
SELECT 10,    3,  3.1,   7.00 UNION
SELECT 11,    8,  3.2,   8.00 UNION
SELECT 12,    8,  3.3,   9.00 UNION
SELECT 13,    8,  3.4,  10.00 UNION
SELECT 14,    9,  4.5,  11.00 UNION
SELECT 15,   10,  4.6,  21.00 UNION
SELECT 15,   10,  4.7,  22.00;

проблема

Мне нужно вычислить рекурсивный aX+b формула до корня для любого узла в дереве. Другими словами, мне нужно вычислить формулу для дочернего узла и переместить полученное значение до родительского как x и продолжайте вычисление, пока я не достигну root.

Например, расчет x=1250.00 за node 14 будет

1.10 * (1.30 *( 3.90 * (4.50 * 1250.00 + 11.00) + 19.00) + 3.00)  + 1.00 = 31463.442

В настоящее время я делаю это с использованием дерева CTE и C#, однако меня не устраивают его скорость и оптимизация.

Вопрос

Могу ли я сделать этот расчет на сервере SQL и просто вернуть значение? Если это возможно, с какой глубиной дерева я могу ориентироваться с помощью CTE?

Answer 1

Могу ли я сделать этот расчет на сервере SQL и просто вернуть значение?

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

with C as
(
  select T.Id,
         T.Parent,
         cast(T.multiplier * @x + T.Const as decimal(19, 3)) as x
  from @table as T
  where T.Id = 14
  union all
  select T.Id,
         T.Parent,
         cast(T.multiplier * C.x + T.Const as decimal(19, 3))
  from C 
    inner join @table as T
      on C.Parent = T.Id
)
select max(C.x) as Value
from C
option (maxrecursion 0);

Если это возможно, с какой глубиной дерева я могу ориентироваться с помощью CTE?

По умолчанию 100, но вы можете изменить это с maxrecursion, Когда используешь option (maxrecursion 0) нет предела

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

Чтобы исправить это, вы должны показать, что вы на самом деле делаете. Образец, который вы предоставили, получает хороший план, если у вас есть кластерный первичный ключ на Id,

Он ищет якорь и ищет каждую итерацию.