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

Откуда: Новосибирск
Сообщений: 75
Дамы и господа, очень Вас прошу помочь затупившему коллеге!
Имеется следующая таблица, в которой перечислены узлы и детали из которых эти узлы состоят (узел может состоять как из деталей, так и из других узлов). Для каждой детали указан вес:
CREATE TABLE tree1 (id int not null,
parent int,
[name] varchar(20) not null default '',
[weight] int not null default ((0)),
primary key (id))
GO
INSERT INTO tree1 (id, parent, [name], [weight]) VALUES 
(1, NULL, 'NODE_1', 0),
(2, NULL, 'NODE_2', 0),
(3, 1, 'NODE_1_1', 0),
(4, 1, 'NODE_1_2', 0),
(5, NULL, 'NODE_3', 0),
(6, 3, 'ITEM_1_1_1', 5),
(7, 4, 'ITEM_1_2_1', 3),
(8, 3, 'ITEM_1_1_2', 6),
(9, 3, 'ITEM_1_1_3', 1),
(10, 4, 'ITEM_1_2_2', 2),
(11, 5, 'ITEM_3_1', 7),
(12, 4, 'ITEM_1_3', 3),
(13, 5, 'NODE_3_2', 0),
(14, 13, 'ITEM_3_2_1', 8),
(15, 13, 'ITEM_3_2_2', 9)

idparentnameweight
1NULLNODE_10
2NULLNODE_20
31NODE_1_10
41NODE_1_20
5NULLNODE_30
63ITEM_1_1_15
74ITEM_1_2_13
83ITEM_1_1_26
93ITEM_1_1_31
104ITEM_1_2_22
115ITEM_3_17
124ITEM_1_33
135NODE_3_20
1413ITEM_3_2_18
1513ITEM_3_2_29

Я создал CTE, которое вывело дерево узлов и деталей:
WITH s (id, parent, lvl, hid, [name], wgt) AS (
	SELECT t.id, t.parent parent, 0, CAST('/'+CAST(t.id AS VARCHAR)+'/' AS hierarchyid), t.[name], t.[weight] FROM tree1 t WHERE t.parent IS NULL
	UNION ALL
	SELECT t.id, t.parent parent, s.lvl + 1, CAST(CAST(s.hid AS varchar) + CAST(t.id as varchar) + '/' as hierarchyid), t.[name], t.[weight] FROM tree1 t INNER JOIN s ON t.parent=s.id
)

SELECT id, parent, CAST(hid as varchar) hid, lvl, replicate('    ', lvl)+[name] [name], wgt FROM s ORDER BY hid 
Получилось вот что:
id
parenthidlvlnamewgt
1NULL/1/0NODE_10
31/1/3/1 NODE_1_10
63/1/3/6/2 ITEM_1_1_15
83/1/3/8/2 ITEM_1_1_26
93/1/3/9/2 ITEM_1_1_31
41/1/4/1 NODE_1_20
104/1/4/10/2 ITEM_1_2_22
124/1/4/12/2 ITEM_1_33
74/1/4/7/2 ITEM_1_2_13
2NULL/2/0NODE_20
5NULL/5/0NODE_30
115/5/11/1 ITEM_3_17
135/5/13/1 NODE_3_20
1413/5/13/14/2 ITEM_3_2_18
1513/5/13/15/2 ITEM_3_2_29

Но, это всем давно известно и никому не интересно. А интересно следующее: как для каждого узла вычислить его вес, который суммируется из веса деталей и подузлов входящих в этот узел?
==
А баги ползали и нагло шевелили усами... (с) Не моё.
17 окт 19, 08:14    [21996021]     Ответить | Цитировать Сообщить модератору
 Re: Суммирование данных в рекурсивном выражении. Возможно ли?  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20522
Я бы просто строил CTE не от корней, а от листьев... тогда можно тащить и накапливать вес.
17 окт 19, 08:18    [21996024]     Ответить | Цитировать Сообщить модератору
 Re: Суммирование данных в рекурсивном выражении. Возможно ли?  [new]
Harlan
Member

Откуда: Новосибирск
Сообщений: 75
Akina, идея хорошая, спасибо за неё, но требуется решить один вопрос: Если выборка нужна для одного узла, то как определить, какие детали в него входят (включая подузлы). А делать общую выборку по таблице в которой миллиард и ещё одна запись, довольно накладно. Или я не прав?
Делать два CTE, где первый определяет состав узла, а второй суммирует веса? Громоздко. Писать рекусивную хранимку, которая считает всё, что нужно? Громоздко и накладно...
Неужели CTE не умеют суммировать листья?
17 окт 19, 09:28    [21996067]     Ответить | Цитировать Сообщить модератору
 Re: Суммирование данных в рекурсивном выражении. Возможно ли?  [new]
court
Member

Откуда:
Сообщений: 2015
Harlan,

Это ?

;WITH s (id, parent, lvl, hid, [name], wgt) AS (
	SELECT t.id, t.parent parent, 0, CAST('/'+CAST(t.id AS VARCHAR)+'/' AS hierarchyid), t.[name], t.[weight] FROM tree1 t WHERE t.parent IS NULL
	UNION ALL
	SELECT t.id, t.parent parent, s.lvl + 1, CAST(CAST(s.hid AS varchar) + CAST(t.id as varchar) + '/' as hierarchyid), t.[name], t.[weight] FROM tree1 t INNER JOIN s ON t.parent=s.id
)

--SELECT id, parent, CAST(hid as varchar) hid, lvl, replicate('    ', lvl)+[name] [name], wgt FROM s ORDER BY hid 
select 
	s1.id, s1.parent, s1.lvl, CAST(s1.hid AS VARCHAR(max)), s1.[name], sum(s2.wgt)
from s s1 inner join s s2 on CAST(s2.hid AS VARCHAR(max)) like CAST(s1.hid AS VARCHAR(max)) + '%'
group by
	s1.id, s1.parent, s1.lvl, s1.hid, s1.[name]


idparentlvlhidnamesum_wgt
1NULL0/1/NODE_120
2NULL0/2/NODE_20
311/1/3/NODE_1_112
411/1/4/NODE_1_28
5NULL0/5/NODE_324
632/1/3/6/ITEM_1_1_15
742/1/4/7/ITEM_1_2_13
832/1/3/8/ITEM_1_1_26
932/1/3/9/ITEM_1_1_31
1042/1/4/10/ITEM_1_2_22
1151/5/11/ITEM_3_17
1242/1/4/12/ITEM_1_33
1351/5/13/NODE_3_217
14132/5/13/14/ITEM_3_2_18
15132/5/13/15/ITEM_3_2_29
17 окт 19, 09:35    [21996075]     Ответить | Цитировать Сообщить модератору
 Re: Суммирование данных в рекурсивном выражении. Возможно ли?  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20522
Harlan
Если выборка нужна для одного узла, то как определить, какие детали в него входят (включая подузлы).
В первом CTE подберите все листья этого узла. Во втором от этих листьев вернитесь к узлу, считая вес. Какие сложности-то?
17 окт 19, 09:36    [21996076]     Ответить | Цитировать Сообщить модератору
 Re: Суммирование данных в рекурсивном выражении. Возможно ли?  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20522
Альтернативно - получив все подузлы нужного узла, суммировать их вес.
17 окт 19, 09:37    [21996078]     Ответить | Цитировать Сообщить модератору
 Re: Суммирование данных в рекурсивном выражении. Возможно ли?  [new]
Harlan
Member

Откуда: Новосибирск
Сообщений: 75
Akina
В первом CTE подберите все листья этого узла. Во втором от этих листьев вернитесь к узлу, считая вес. Какие сложности-то?

Никаких, за исключением того, что не хотелось бы делать "лишнюю работу". Можно, конечно, соорудить хранимую процедуру, которая всё это посчитает, но зачем, если есть возможность CTE сделать нужные подсчёты? А есть они или нет - вот, собственно, в чём вопрос.
17 окт 19, 09:46    [21996086]     Ответить | Цитировать Сообщить модератору
 Re: Суммирование данных в рекурсивном выражении. Возможно ли?  [new]
Harlan
Member

Откуда: Новосибирск
Сообщений: 75
court
Это ?

Да. Очень похоже на то.
Спасибо!
17 окт 19, 09:48    [21996087]     Ответить | Цитировать Сообщить модератору
 Re: Суммирование данных в рекурсивном выражении. Возможно ли?  [new]
Harlan
Member

Откуда: Новосибирск
Сообщений: 75
court,
Немного подправлю ваш запрос:
WITH s (id, parent, lvl, hid, [name], wgt) AS (
	SELECT t.id, t.parent parent, 0, CAST('/'+CAST(t.id AS VARCHAR)+'/' AS hierarchyid), t.[name], t.[weight] FROM tree1 t WHERE t.parent IS NULL
	UNION ALL
	SELECT t.id, t.parent parent, s.lvl + 1, CAST(CAST(s.hid AS varchar) + CAST(t.id as varchar) + '/' as hierarchyid), t.[name], t.[weight] FROM tree1 t INNER JOIN s ON t.parent=s.id
)

select 
	s1.id, s1.parent, s1.lvl, CAST(s1.hid AS VARCHAR(max)) hid, replicate('    ', s1.lvl)+s1.[name] name, sum(s2.wgt) wgt
from s s1 inner join s s2 on s2.hid.IsDescendantOf(s1.hid) = 1
group by
	s1.id, s1.parent, s1.lvl, s1.hid, s1.[name]
order by s1.hid

Т.е. вместо приведения поля hid к строке и использования LIKE взял метод s2.hid.IsDescendantOf(s1.hid), который возвращает 1, если s1.hid является родителем s2.hid.
Ну и добавил order by s1.hid для сохранения структуры дерева.
17 окт 19, 10:56    [21996212]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить