Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Подсчет суммы в дереве  [new]
TreeNeeded
Guest
Доброго времени суток!

Я в MS-SQL не давно. Задача поставлена сформировать суммы затрат по материалам для каждого компонента дерева.
Может кто подскажет в какую сторону смотреть или ссылки какие-то на данную тему.
Думаю про обратную рекурсию, но не знаю, как реализовать.
Задача такая.
Формируются сейчас виртуальное представление, структура следующая (уменьшенная естественно):
declare @tab table(GroupID varchar(20), ParentID varchar(20), ID varchar(20), LevelNo int, Amount float, Costs float)

insert @tab

select 'Группа1', 'Группа1', 'Группа1', 0, 1, 5.34 union all
select 'Группа1', 'Группа1', 'Узел1', 1, 2, 3.1 union all
select 'Группа1', 'Группа1', 'Узел2' , 1, 3, 0.45 union all
select 'Группа1', 'Узел1', 'Деталь 1', 2, 5, 0.65 union all
select 'Группа1', 'Деталь 1', 'Деталь 10', 3, 4, 0.345 union all
select 'Группа1', 'Деталь 1', 'Деталь 11', 3, 6, 0.475 union all

select 'Группа2', 'Группа2', 'Группа2', 0, 1, 4.67 union all
select 'Группа2', 'Группа2', 'Узел 5', 1, 4, 2.5 union all
select 'Группа2', 'Узел 5' 'Деталь 50', 2, 4, 1.25 union all
select 'Группа2','Деталь 50', 'Деталь 51', 3, 7, 1.33

GroupID - учетная группа, она же корень дерева, уровень 0
ParentID - предок элемента затрат (для уровня 0, то же что и GroupID , не моя структура то что не null, так должно быть)
ID - элемент затрат
LevelNo - уровень вложенности от 0 и выше (макс вложенность до 20)
Amount - кол-во элементов затрат в предке
Costs - затраты данного элемента

Необходимо посчитать сумму затрат для каждого элемента дерева в отдельном столбце (лучше без временных таблиц, но если производительность повысится, могу результаты виртуального представления сбросить во временную таблицу, как тут в примере и формировать другие временные таблицы при необходимости).
Результат ожидается такой (SumCosts формируемый столбец, привожу расчет а не конечные значения):

GroupID ParentID ID LevelNo Amount Costs SumCosts
Группа1 Группа1 Группа1 0 1 5.34 5.34 + 2 * (3.1 + 5 * (0.65 + 0.475 * 6 + 0.345 * 4)) + 3 * 0.45
Группа1 Группа1 Узел1 1 2 3.1 3.1 + 5 * (0.65 + 0.475 * 6 + 0.345 * 4)
Группа1 Группа1 Узел2 1 3 0.45 0.45
Группа1 Узел1 Деталь 1 2 5 0.65 0.65 + 0.475 * 6 + 0.345 * 4
Группа1 Деталь 1 Деталь 10 3 4 0.345 0.345
Группа1 Деталь 1 Деталь 11 3 6 0.475 0.475
...
Т.е если последний элемент, то SumCosts = Costs, предыдущий = кол-во в нем состоящих элементов * на их полные затраты ( с учетом ниже находящихся в дереве) и все это с учетом GroupID.

Объем данных порядка 1 млн. записей, время не очень критично но хотелось бы не более 2 минут.
Данные сливаются из другой системы и в MS-SQL формируются пару раз в день, при перекачке такое формирование не организовать(т.е залиты структуры элементов (спецификаций), отдельно затраты, теперь нужно по учетным группам все рассчитать).

Надеюсь внятно объяснил проблему. Буду рад любым комментариям и предложенным решениям.

За ранее, большое спасибо.
24 ноя 11, 21:31    [11655090]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет суммы в дереве  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31987
TreeNeeded
Буду рад любым комментариям и предложенным решениям.
Есть 2 варианта - CTE или цикл по уровням с временной таблицей.

Для большого объёма наверное лучьше предварительно перелить во временную таблицу, так как ваше "виртуальное представление" наверняка не индексированное.

Описание способов.

Цикл с временной таблицей: 1758 или 247293
CTE: 10770969
24 ноя 11, 23:08    [11655371]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет суммы в дереве  [new]
TreeNeeded
Guest
Спасибо за ссылки.
Пока еще в них не разобрался, первая сортировка, но не подсчет суммы,
ссылки на временные таблицы, первым взглядом там id - первичный ключ, у меня не так:
Узел1 может состоять из Узла10 и Детали12
Узел2 состоит из Узла10 и Узла15
Узел10 состоит из Детали20 и Детали 21

итог: в моей таблице будет (не все, просто хочу подчеркнуть про повторяющиеся записи на уровне ParentID - ID)
тут я привожу пример до уровня 2, а на 3 уровне все одинаково, такое надо не суммировать, а брать только уникальные по уровням.
Уровень 2 ----Уровень 3
Узел1 - Деталь12
Узел1 - Узел10 - Деталь20
Узел1 - Узел10 - Деталь21
Узел2 - Узел10 - Деталь20
Узел2 - Узел10 - Деталь21
Узел2 - Узел15...
Постараюсь понять и разобраться по данным ссылкам, пока голова не варит что-то (завтра со свежей головой), я пока в T-SQl не очень.
24 ноя 11, 23:51    [11655511]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет суммы в дереве  [new]
TreeNeeded
Guest
Хотел еще уточнить, есть поле уникальный полный путь к каждому элементу (FullPath) внутри грппы GroupID, начинающийся с единицы (корень группы)

declare @tab table(GroupID varchar(20), ParentID varchar(20), ID varchar(20), LevelNo int, 
  Amount float, Costs float, FullPath varchar(100));

insert @tab

select 'Группа1', 'Группа1',   'Группа1',   0, 1, 5.34,  '1'         union all
select 'Группа1', 'Группа1',   'Узел1',     1, 2, 3.1,   '1.1'       union all
select 'Группа1', 'Группа1',   'Узел2' ,    1, 3, 0.45,  '1.2'       union all
select 'Группа1', 'Узел1',     'Деталь 1',  2, 5, 0.65,  '1.1.1'     union all
select 'Группа1', 'Деталь 1',  'Деталь 10', 3, 4, 0.345, '1.1.1.1'   union all
select 'Группа1', 'Деталь 1',  'Деталь 11', 3, 6, 0.475, '1.1.1.2'   union all
select 'Группа1', 'Узел2',     'Деталь 1',  2, 5, 0.65,  '1.2.1'     union all
select 'Группа1', 'Деталь 1',  'Деталь 10', 3, 4, 0.345, '1.2.1.1'   union all
select 'Группа1', 'Деталь 1',  'Деталь 11', 3, 6, 0.475, '1.2.1.2'   union all
select 'Группа1', 'Деталь 11', 'Деталь 111', 4, 2, 0.33, '1.2.1.2.1' union all

select 'Группа2', 'Группа2',   'Группа2',   0, 1, 4.67,  '1'         union all
select 'Группа2', 'Группа2',   'Узел 5',    1, 4, 2.5,   '1.1'       union all
select 'Группа2', 'Узел 5',    'Деталь 50', 2, 4, 1.25,  '1.1.1'     union all
select 'Группа2', 'Деталь 50', 'Деталь 51', 3, 7, 1.33,  '1.1.1.1';
25 ноя 11, 08:48    [11656154]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет суммы в дереве  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
TreeNeeded
есть поле уникальный полный путь к каждому элементу (FullPath) внутри грппы GroupID
[src]select 'Группа1', 'Деталь 11', 'Деталь 111', 4, 2, 0.33, '1.2.1.2.1'[src]
Позырте в сторону HierarchyID, для общего развития. Вам в сам раз.
При помощи Parse можно FullPath сразу превратить в HierarchyID.

Тогда можно одним нерекурсивным треугольным запросом, но не думаю что будет эффективно (для данной задачи).
25 ноя 11, 18:31    [11661567]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет суммы в дереве  [new]
TreeNeeded
Guest
Да, спасибо, думал сам об этом сегодня, начитался.
Решил попробовать прямую рекурсию через процедуру (там вложенность была 32, меня устраивает) сразу с подсчетом сумм, а не сте. Посмотрю какие затраты на формирование и какое время, если устроит, то оставлю, если нет, то попробую ваш вариант (у меня там по группам еще разбито, поэтому не стал данный тип данных использовать).
25 ноя 11, 21:02    [11662115]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить