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

Откуда:
Сообщений: 6
Добрый день, господа.

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

Суть задачи:

Есть иерархический справочник ресурсов организации - дерево. Узлы - подразделения. Листья - сотрудники.

Нужно построить отчет по оргструктуре в котором в каждой строке указывалось бы общее количество сотрудников на всех низлежащих уровнях.

Пример:

Директор - 10 человек
---Отдел продаж - 5 человек
------сотрудник - 1 человек
------сотрудник - 1 человек
------сотрудник - 1 человек
------сотрудник - 1 человек
------сотрудник - 1 человек
---Отдел закупок - 5 человек
------Отдел по работе с поставщиками - 2 человека
---------сотрудник - 1 человек
---------сотрудник - 1 человек
------Отдел логистики - 3 человека
---------сотрудник - 1 человек
---------сотрудник - 1 человек
---------сотрудник - 1 человек

Реально ли сделать такое средствами SQL? Или нужно логику обхода дерева отдельно писать?
28 авг 09, 22:21    [7592962]     Ответить | Цитировать Сообщить модератору
 Re: агрегация тоталов по иерархии. подходы к решению задачи  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Heroic,

Если иерархия не очень большая, то проще сделать табличную функцию по типу рекурсии. А ее джойнить в CTE, если, конечно, это дозволено
28 авг 09, 23:24    [7593027]     Ответить | Цитировать Сообщить модератору
 Re: агрегация тоталов по иерархии. подходы к решению задачи  [new]
Roman S. Golubin
Member

Откуда: 140002
Сообщений: 11541
Heroic,

Смотри

a) Хороший обзор по методам хранения деревьев
b) hierarchyid от microsoft
29 авг 09, 00:08    [7593077]     Ответить | Цитировать Сообщить модератору
 Re: агрегация тоталов по иерархии. подходы к решению задачи  [new]
Heroic
Member

Откуда:
Сообщений: 6
По поводу СТЕ - мне идея понравилась, но корректно изобразить иерархию не получается...

CNE действительно выводит иерархию, но делает это своеобразным способом кусками уровней заданной глубины.

Пример, чтобы было ясно о чем я:

1 109 109 NULL Chief Executive Officer
2 109-6 6 109 Marketing Manager
2 109-12 12 109 Vice President of Engineering
2 109-42 42 109 Information Services Manager
2 109-140 140 109 Chief Financial Officer
2 109-148 148 109 Vice President of Production
2 109-273 273 109 Vice President of Sales
3 109-273-268 268 273 North American Sales Manager
3 109-273-284 284 273 European Sales Manager
3 109-273-288 288 273 Pacific Sales Manager
4 109-273-288-290 290 288 Sales Representative
4 109-273-284-285 285 284 Sales Representative
4 109-273-284-286 286 284 Sales Representative
4 109-273-284-289 289 284 Sales Representative
4 109-273-268-275 275 268 Sales Representative
4 109-273-268-276 276 268 Sales Representative
4 109-273-268-277 277 268 Sales Representative
4 109-273-268-278 278 268 Sales Representative
4 109-273-268-279 279 268 Sales Representative
4 109-273-268-280 280 268 Sales Representative
4 109-273-268-281 281 268 Sales Representative
4 109-273-268-282 282 268 Sales Representative
4 109-273-268-283 283 268 Sales Representative
4 109-273-268-287 287 268 Sales Representative
3 109-148-21 21 148 Production Control Manager
3 109-148-44 44 148 Master Scheduler
3 109-148-200 200 148 Quality Assurance Manager
3 109-148-218 218 148 Facilities Manager

Как сделать так чтобы раскрытие шло напротив каждого элемента я если честно не понял пока...
29 авг 09, 16:15    [7593621]     Ответить | Цитировать Сообщить модератору
 Re: агрегация тоталов по иерархии. подходы к решению задачи  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Heroic,
USE [tempdb]
GO

IF OBJECT_ID('tree') IS NOT NULL
	DROP TABLE [tree]
GO

CREATE TABLE [tree](
	[id] INT NOT NULL PRIMARY KEY,
	[idParent] INT NULL FOREIGN KEY REFERENCES tree(id),
	[name] VARCHAR(20) NOT NULL
)
GO

INSERT INTO [tree] ([id],[idParent],[name])
SELECT 1, NULL, 'Boss' UNION ALL
SELECT 2, 1, 'Nach1' UNION ALL
SELECT 3, 1, 'Nach2' UNION ALL
SELECT 4, 2, 'worker1_1' UNION ALL
SELECT 5, 2, 'worker1_2' UNION ALL
SELECT 6, 2, 'worker1_3' UNION ALL
SELECT 7, 2, 'worker1_4' UNION ALL
SELECT 8, 3, 'worker2_1' UNION ALL
SELECT 9, 3, 'worker2_2' UNION ALL
SELECT 10, 3, 'worker2_3'
GO

WITH cte AS (
	SELECT
		[id],
		[idParent],
		[name]
	FROM [tree]
	WHERE NOT EXISTS (SELECT * FROM [tree] tc WHERE tc.[idParent] = tree.[id])
	UNION ALL
	SELECT
		t.[id],
		t.[idParent],
		t.[name]
	FROM [tree] t
		JOIN [cte] ON t.[id] = [cte].idParent
)
SELECT
	[cte].id,
	[cte].idParent,
	[cte].[name],
	COUNT(*)
FROM cte
GROUP BY cte.[id], cte.[idParent], cte.[name]
GO
Попробуйте такой запрос. Порядок следования элементов древа неправильный, надо подкрутить. Это Вы уже как-нибудь сами, а мне надо бежать. :)

ЗЫ. Пользуйтесь тегом SRC, не стесняйтесь.
29 авг 09, 16:39    [7593677]     Ответить | Цитировать Сообщить модератору
 Re: агрегация тоталов по иерархии. подходы к решению задачи  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Мдя... сыроватый вышел запрос. Зачем я строил рекурсию снизу вверх - ума не приложу. Но план какой-то был точно
29 авг 09, 16:41    [7593681]     Ответить | Цитировать Сообщить модератору
 Re: агрегация тоталов по иерархии. подходы к решению задачи  [new]
Heroic
Member

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

Вот и здесь такая же ошибка.

В результате:

1 NULL Boss 7
2 1 Nach1 4
3 1 Nach2 3
4 2 worker1_1 1
5 2 worker1_2 1
6 2 worker1_3 1
7 2 worker1_4 1
8 3 worker2_1 1
9 3 worker2_2 1
10 3 worker2_3 1

А правильно (как нужно точнее):

1 NULL Boss 7
2 1 Nach1 4
4 2 worker1_1 1
5 2 worker1_2 1
6 2 worker1_3 1
7 2 worker1_4 1
3 1 Nach2 3
8 3 worker2_1 1
9 3 worker2_2 1
10 3 worker2_3 1
29 авг 09, 17:19    [7593733]     Ответить | Цитировать Сообщить модератору
 Re: агрегация тоталов по иерархии. подходы к решению задачи  [new]
Heroic
Member

Откуда:
Сообщений: 6
Походу CTE здесь бессилен. Придется старым добрым T-SQL пользоваться:

http://msdn.microsoft.com/en-us/library/aa172799(SQL.80).aspx
29 авг 09, 17:20    [7593734]     Ответить | Цитировать Сообщить модератору
 Re: агрегация тоталов по иерархии. подходы к решению задачи  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Heroic
Походу CTE здесь бессилен.
Просто Вы не умеете его готовить. Такие вещи обходятся использованием path в рекурсии. Просто у меня не было времени доработать запрос.
29 авг 09, 18:55    [7593831]     Ответить | Цитировать Сообщить модератору
 Re: агрегация тоталов по иерархии. подходы к решению задачи  [new]
Heroic
Member

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

Возможно, но из десятка примеров, вытащенных из инета так не умел ни один. А это достаточно важная и очевидная фича....
29 авг 09, 20:47    [7593919]     Ответить | Цитировать Сообщить модератору
 Re: агрегация тоталов по иерархии. подходы к решению задачи  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Heroic
Senya_L,

Возможно, но из десятка примеров, вытащенных из инета так не умел ни один. А это достаточно важная и очевидная фича....
Я, к сожалению, не нашел среди строковых функций аналога LPAD. Найдете - полдела будет сделано.
29 авг 09, 21:42    [7593997]     Ответить | Цитировать Сообщить модератору
 Re: агрегация тоталов по иерархии. подходы к решению задачи  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Senya_L
Я, к сожалению, не нашел среди строковых функций аналога LPAD. Найдете - полдела будет сделано.
STR
?
30 авг 09, 11:32    [7594601]     Ответить | Цитировать Сообщить модератору
 Re: агрегация тоталов по иерархии. подходы к решению задачи  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
iap
Senya_L
Я, к сожалению, не нашел среди строковых функций аналога LPAD. Найдете - полдела будет сделано.
STR
?
Именно! Не совсем аналог LPAD, но сойдет для данной задачи.
Тогда запрос выглядит следующим образом:
USE [tempdb]
GO

IF OBJECT_ID('tree') IS NOT NULL
	DROP TABLE [tree]
GO

CREATE TABLE [tree](
	[id] INT NOT NULL PRIMARY KEY,
	[idParent] INT NULL FOREIGN KEY REFERENCES tree(id),
	[name] VARCHAR(20) NOT NULL
)
GO

INSERT INTO [tree] ([id],[idParent],[name])
SELECT 1, NULL, 'Boss' UNION ALL
SELECT 2, 1, 'Nach1' UNION ALL
SELECT 3, 1, 'Nach2' UNION ALL
SELECT 4, 2, 'worker1_1' UNION ALL
SELECT 5, 2, 'worker1_2' UNION ALL
SELECT 6, 2, 'worker1_3' UNION ALL
SELECT 7, 2, 'worker1_4' UNION ALL
SELECT 8, 3, 'worker2_1' UNION ALL
SELECT 9, 3, 'worker2_2' UNION ALL
SELECT 10, 3, 'worker2_3'
GO

WITH cte AS (
	SELECT
		[id],
		[idParent],
		[name],
		CAST(STR(id) AS VARCHAR(100)) AS [path],
		CASE 
			WHEN EXISTS(SELECT * FROM tree ch WHERE ch.[idParent] = tree.[id]) THEN 0
			ELSE 1
		END AS N
	FROM [tree]
	WHERE [idParent] IS NULL
	UNION ALL
	SELECT
		t.[id],
		t.[idParent],
		t.[name],
		CAST(cte.PATH + STR(t.id) AS VARCHAR(100)) AS [path],
		CASE 
			WHEN EXISTS(SELECT * FROM tree ch WHERE ch.[idParent] = t.[id]) THEN 0
			ELSE 1
		END AS N
	FROM [tree] t
		JOIN [cte] ON t.[idParent] = [cte].id
)
SELECT
	t.id,
	t.idParent,
	t.[name],
--	t.PATH,
	(SELECT SUM(N) FROM cte WHERE [path] LIKE t.[path] + '%') AS EmpCount
FROM cte t
ORDER BY t.path
GO
Результаты:
1	NULL	Boss          	7
2	1	Nach1        	4
4	2	worker1_1	1
5	2	worker1_2	1
6	2	worker1_3	1
7	2	worker1_4	1
3	1	Nach2        	3
8	3	worker2_1	1
9	3	worker2_2	1
10	3	worker2_3	1
Вроде бы то, что и требовалось?

PS. Автору: так что не спешите делать выводы о границах применимости SQL. Их и самые отъявленные гуру не знают
30 авг 09, 13:02    [7594662]     Ответить | Цитировать Сообщить модератору
 Re: агрегация тоталов по иерархии. подходы к решению задачи  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
В порядке изврата (iap, привет;))
WITH cte AS (
	SELECT
		[id],
		[idParent],
		[name],
		CAST(STR(id) AS VARCHAR(100)) AS [path],
		CASE 
			WHEN EXISTS(SELECT * FROM tree ch WHERE ch.[idParent] = tree.[id]) THEN 0
			ELSE 1
		END AS N
	FROM [tree]
	WHERE [idParent] IS NULL
	UNION ALL
	SELECT
		t.[id],
		t.[idParent],
		t.[name],
		CAST(cte.PATH + STR(t.id) AS VARCHAR(100)) AS [path],
		CASE 
			WHEN EXISTS(SELECT * FROM tree ch WHERE ch.[idParent] = t.[id]) THEN 0
			ELSE 1
		END AS N
	FROM [tree] t
		JOIN [cte] ON t.[idParent] = [cte].id
)
SELECT
	t.id,
	t.idParent,
	t.[name],
	t.PATH,
	N
INTO #tmp
FROM cte t

CREATE CLUSTERED INDEX IX_tmp ON #tmp([path])

SELECT
	t.id,
	t.idParent,
	t.[name],
	(SELECT SUM(N) FROM #tmp WHERE [path] LIKE t.[path] + '%') AS EmpCount
FROM #tmp t
ORDER BY [path]

DROP TABLE #tmp
Интересно: на больших объемах выигрыш будет за счет временной та
30 авг 09, 13:36    [7594692]     Ответить | Цитировать Сообщить модератору
 Re: агрегация тоталов по иерархии. подходы к решению задачи  [new]
Heroic
Member

Откуда:
Сообщений: 6
Спасибо большое.

Я правда за это время на T-SQL все сделал, но на будущее пригодится.
30 авг 09, 16:14    [7594835]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить