Каков наилучший способ группировки, агрегирования и суммирования данных дерева?

Дана таблица самоссылки

Item 
-------------
Id (pk)
ParentId (fk)

Со связанной таблицей связанных значений

ItemValue
-------------
ItemId (fk)
Amount

И некоторые примерные данные

Item                       ItemValues 
Id      ParentId           ItemId      Amount
--------------------       ----------------------
1       null               1           10
2       1                  3           40
3       1                  3           20
4       2                  4           10
5       2                  5           30
6       null
7       6
8       7

Мне нужен sproc, чтобы взять Item.Id и вернуть прямых дочерних элементов с суммами всех ItemValue.Amounts для них, их дочерних элементов и их дочерних элементов на всем пути вниз по дереву.

Например, если передать 1, дерево будет 2, 3, 4, 5 прямым потомком будет 2, 3 вывод будет

 ItemId    Amount
 ------------------
 2         40     (values from ItemIds 4 & 5)
 3         60     (values from ItemId 3)

Какие подходы следует применять для достижения такого поведения?

Я рассматриваю возможность использования CTE, но мне интересно, есть ли лучший/быстрый подход.


sql sql-server tsql hierarchical sql-server-2005
person Bob    schedule 23.10.2009    source источник
comment
SQL Server 2005, я тоже переназначу   -  person Bob    schedule 23.10.2009

Ответы (3)


arrow_upward
6
arrow_downward

Такой рекурсивный CTE будет работать, если ваша иерархия не слишком глубока:

declare @ParentId int;
set @ParentId = 1;

;with 
  Recurse as (
    select 
      a.Id as DirectChildId
    , a.Id
    from Item a 
    where ParentId = @ParentId
    union all
    select
      b.DirectChildId
    , a.Id
    from Item a 
    join Recurse b on b.Id = a.ParentId
    )
select
  a.DirectChildId, sum(b.Amount) as Amount
from Recurse a
left join ItemValues b on a.Id = b.ItemId
group by
  DirectChildId;

Метод без CTE потребует некоторой формы итерации, на основе курсора или иным образом. Поскольку это хранимый процесс, это возможно, и если есть много данных для рекурсии, он, вероятно, будет лучше масштабироваться, если вы соответствующим образом нарежете данные.

Если кластеризованный индекс находится в Id, добавьте некластеризованный индекс в ParentId. В качестве покрывающего индекса он удовлетворит начальный поиск без поиска по закладкам. Затем кластеризованный индекс поможет с рекурсивным соединением.

Если вместо этого кластеризованный индекс уже находится в ParentId, добавьте некластеризованный индекс в Id. Вместе они будут практически эквивалентны вышеперечисленным. Для ItemValues ​​вам может понадобиться индекс для (ItemId) INCLUDE (Amount), если фактическая таблица шире этого.

person Peter Radocchia    schedule 23.10.2009
comment
предполагая, что ваша иерархия не слишком глубока, это не так, но в чем была бы проблема, если бы это произошло? - person Bob; 23.10.2009
comment
Глубина рекурсии по умолчанию ограничена 100 в SQL Server. Вы можете указать до 32767 с подсказкой запроса MAXRECURSION, но я предполагаю, что она не масштабируется линейно с объемом выше определенного порога. Однако это не проблема для вашей ситуации, поскольку вы будете указывать одного родителя за раз. Пока вы индексируете его правильно и глубина рекурсии разумна (менее 100?), производительность не должна быть проблемой. - person Peter Radocchia; 23.10.2009
comment
Вы можете перейти к бесконечной рекурсии с помощью CTE. Установите MAXRECURSION = 0. Это написано в BOL. - person gbn; 23.10.2009

arrow_upward
0
arrow_downward

Не могли бы вы хранить свои данные, как в модели вложенного множества (вот ссылка на MySQL , но эти идеи являются общими для всех баз данных)? Если это так, то операции по поиску искомого значения будут довольно простыми.

person Brian Fisher    schedule 23.10.2009

arrow_upward
0
arrow_downward

Это должно обрабатываться в базе данных? Я бы предложил внести необходимые данные в ваш BLL и выполнить рекурсию там.

person Rip Rowan    schedule 23.10.2009