Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Суммирование данных в рекурсивном выражении. Возможно ли? / 9 сообщений из 9, страница 1 из 1
17.10.2019, 08:14
    #39877408
Harlan
Harlan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Суммирование данных в рекурсивном выражении. Возможно ли?
Дамы и господа, очень Вас прошу помочь затупившему коллеге!
Имеется следующая таблица, в которой перечислены узлы и детали из которых эти узлы состоят (узел может состоять как из деталей, так и из других узлов). Для каждой детали указан вес:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
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)


idparentnameweight1NULLNODE_102NULLNODE_2031NODE_1_1041NODE_1_205NULLNODE_3063ITEM_1_1_1574ITEM_1_2_1383ITEM_1_1_2693ITEM_1_1_31104ITEM_1_2_22115ITEM_3_17124ITEM_1_33135NODE_3_201413ITEM_3_2_181513ITEM_3_2_29
Я создал CTE, которое вывело дерево узлов и деталей:
Код: sql
1.
2.
3.
4.
5.
6.
7.
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

Код: plaintext
Получилось вот что:
Код: plaintext
id
parenthidlvlnamewgt1NULL/1/0NODE_1031/1/3/1 NODE_1_1063/1/3/6/2 ITEM_1_1_1583/1/3/8/2 ITEM_1_1_2693/1/3/9/2 ITEM_1_1_3141/1/4/1 NODE_1_20104/1/4/10/2 ITEM_1_2_22124/1/4/12/2 ITEM_1_3374/1/4/7/2 ITEM_1_2_132NULL/2/0NODE_205NULL/5/0NODE_30115/5/11/1 ITEM_3_17135/5/13/1 NODE_3_201413/5/13/14/2 ITEM_3_2_181513/5/13/15/2 ITEM_3_2_29
Но, это всем давно известно и никому не интересно. А интересно следующее: как для каждого узла вычислить его вес, который суммируется из веса деталей и подузлов входящих в этот узел?
==
А баги ползали и нагло шевелили усами... (с) Не моё.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.10.2019, 08:18
    #39877409
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Суммирование данных в рекурсивном выражении. Возможно ли?
Я бы просто строил CTE не от корней, а от листьев... тогда можно тащить и накапливать вес.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.10.2019, 09:28
    #39877432
Harlan
Harlan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Суммирование данных в рекурсивном выражении. Возможно ли?
Akina, идея хорошая, спасибо за неё, но требуется решить один вопрос: Если выборка нужна для одного узла, то как определить, какие детали в него входят (включая подузлы). А делать общую выборку по таблице в которой миллиард и ещё одна запись, довольно накладно. Или я не прав?
Делать два CTE, где первый определяет состав узла, а второй суммирует веса? Громоздко. Писать рекусивную хранимку, которая считает всё, что нужно? Громоздко и накладно...
Неужели CTE не умеют суммировать листья?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.10.2019, 09:35
    #39877435
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Суммирование данных в рекурсивном выражении. Возможно ли?
Harlan,

Это ?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
;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_wgt1NULL0/1/NODE_1202NULL0/2/NODE_20311/1/3/NODE_1_112411/1/4/NODE_1_285NULL0/5/NODE_324632/1/3/6/ITEM_1_1_15742/1/4/7/ITEM_1_2_13832/1/3/8/ITEM_1_1_26932/1/3/9/ITEM_1_1_311042/1/4/10/ITEM_1_2_221151/5/11/ITEM_3_171242/1/4/12/ITEM_1_331351/5/13/NODE_3_21714132/5/13/14/ITEM_3_2_1815132/5/13/15/ITEM_3_2_29
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.10.2019, 09:36
    #39877436
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Суммирование данных в рекурсивном выражении. Возможно ли?
HarlanЕсли выборка нужна для одного узла, то как определить, какие детали в него входят (включая подузлы).В первом CTE подберите все листья этого узла. Во втором от этих листьев вернитесь к узлу, считая вес. Какие сложности-то?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.10.2019, 09:37
    #39877437
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Суммирование данных в рекурсивном выражении. Возможно ли?
Альтернативно - получив все подузлы нужного узла, суммировать их вес.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.10.2019, 09:46
    #39877440
Harlan
Harlan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Суммирование данных в рекурсивном выражении. Возможно ли?
AkinaВ первом CTE подберите все листья этого узла. Во втором от этих листьев вернитесь к узлу, считая вес. Какие сложности-то?
Никаких, за исключением того, что не хотелось бы делать "лишнюю работу". Можно, конечно, соорудить хранимую процедуру, которая всё это посчитает, но зачем, если есть возможность CTE сделать нужные подсчёты? А есть они или нет - вот, собственно, в чём вопрос.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.10.2019, 09:48
    #39877441
Harlan
Harlan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Суммирование данных в рекурсивном выражении. Возможно ли?
courtЭто ?

Да. Очень похоже на то.
Спасибо!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.10.2019, 10:56
    #39877504
Harlan
Harlan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Суммирование данных в рекурсивном выражении. Возможно ли?
court,
Немного подправлю ваш запрос:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
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 для сохранения структуры дерева.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Суммирование данных в рекурсивном выражении. Возможно ли? / 9 сообщений из 9, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 458ms
Закрыть
start [/forum/topic.php?fid=46&tablet=1&tid=1687110]:
 0ms
get settings:
 10ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 148ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 34ms
get tp. blocked users:
 1ms
others: 234ms
total:  458ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]