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

Откуда:
Сообщений: 3
Имеем дерево построенное на основе Child Parent

table tree. PID указывает на ID являющееся родителем для ID.
ID PID

table amounts. ID является ссылкой на дерево (table tree).
ID Amount

Задача сделать эффективную агрегацию сумм Amount для родителей. Задача по факту сводится к тому чтобы прибавить Amount конечной (не имеющей детей) записи ко всем родителям. Amount родителей считаем всегда нулевым. Таблица переформируется каждый раз. Поэтому у родителей там ноль.

Варианты реализации.

1.
Триггер. На таблицу вешаем триггер который обновляет родительскую запись (получается рекурсивное обновление вверх). Затем просто устанавливаем значение амаунт для детей.

2.
Курсор. Наверное самый тупой и медленный способ. Прокатываемся по всем детям (детьми называю запись не имеющую дочерних. т.е. по факту это запись изначально имеющая данные) и для каждой дочерней записи используя рекурсивный WITH прокатываемся по родителям добавляя Amount.

3.
Бился вчера над созданием единого запроса который бы за один запрос обновил амаунты всем родителям всех детей. Данный запрос ожидается наиболее эффективным. Для этого создана функция которая возвращает таблицу родителей для дочерней записи. Хотел использовать в джойне из рекурсивного виз. Цель была получить список всех детей. Для каждой дочерней записи используя табличную функцию получения родителей произвести CROSS JOIN с родителями и размноженную для каждого родителя запись детей добавить ко всем родителям сразу. Получаю ошибку
The multi-part identifier "cte.ID" could not be bound. Причина походу в том, что не получается вызвать табличную функцию для каждой дочерней записи.

Как будет эффективней?
13 окт 17, 09:24    [20866219]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная агрегация на родительских ветках суммы дочерних элементов  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
tibidoh
Имеем дерево построенное на основе Child Parent
...
Получаю ошибку
The multi-part identifier "cte.ID" could not be bound. Причина походу в том, что не получается вызвать табличную функцию для каждой дочерней записи.
Как будет эффективней?

Эффективней будет код предоставить в том числе. Гадать на кофейной гуще, что у вас там сходу не получается здесь не любят.
13 окт 17, 10:22    [20866419]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная агрегация на родительских ветках суммы дочерних элементов  [new]
Владислав Колосов
Member

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

ничего не мешает писать рутовые ID в каждой строке таблицы. Потом обновите детями ID корня одним запросом.
13 окт 17, 10:51    [20866596]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная агрегация на родительских ветках суммы дочерних элементов  [new]
tibidoh
Member

Откуда:
Сообщений: 3
Вот примерный тестовый код:

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tree') BEGIN

CREATE TABLE [dbo].[tree](
    [ID]      [int] NOT NULL,
    [PID]     [int] NOT NULL
)

CREATE TABLE [dbo].[amounts](
    [ID]      [int] NOT NULL,
	[Amount]  [int] NOT NULL DEFAULT(0)
)

-- 0
-- 1
--   3
--     4
--     5
-- 2
--   6
--     7
--       8
--       9
--     10
--      11

INSERT INTO tree(ID, PID) VALUES(0, 0)
INSERT INTO tree(ID, PID) VALUES(1, 0)
INSERT INTO tree(ID, PID) VALUES(2, 0)
INSERT INTO tree(ID, PID) VALUES(3, 1)
INSERT INTO tree(ID, PID) VALUES(4, 3)
INSERT INTO tree(ID, PID) VALUES(5, 3)
INSERT INTO tree(ID, PID) VALUES(6, 2)
INSERT INTO tree(ID, PID) VALUES(7, 6)
INSERT INTO tree(ID, PID) VALUES(8, 7)
INSERT INTO tree(ID, PID) VALUES(9, 7)
INSERT INTO tree(ID, PID) VALUES(10, 6)
INSERT INTO tree(ID, PID) VALUES(11, 10)

END

GO

DECLARE @amounts_child TABLE
(
    [ID]      [int] NOT NULL,
    [PID]     [int] NOT NULL,
	[Amount]  [int] NOT NULL DEFAULT(0)
)

INSERT INTO @amounts_child(ID, PID, Amount)
	SELECT ID, PID, (ABS(CHECKSUM(NewId())) % 14) * 1000 FROM tree WHERE ID NOT IN (SELECT PID FROM tree)

SELECT * FROM @amounts_child

DECLARE @amounts TABLE
(
    [ID]      [int] NOT NULL,
	[Amount]  [int] NOT NULL DEFAULT(0)
)

INSERT @amounts
	SELECT ID, 0 FROM tree WHERE ID IN (SELECT PID FROM tree)

SELECT * FROM @amounts;

WITH cte AS (
SELECT PID, Amount FROM @amounts_child
UNION ALL
SELECT PID, cte.Amount FROM tree WHERE ID = cte.PID
)
UPDATE @amounts SET Amount = Amount + cte.Amount
13 окт 17, 11:19    [20866772]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная агрегация на родительских ветках суммы дочерних элементов  [new]
LoopN
Guest
Как вариант

Добавить еще один столбец varchar в дерево Enumerate Path (полный путь к каждому листу дерева /1/1/3, /1/2/1). Навесить на этот столбец покрывающий индекс (с Amount).

Дальше написать индексированное представление
где будет примерно следующее: select SUM(Amount),... from Tree where EnPath like '/1/%'
13 окт 17, 11:34    [20866841]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная агрегация на родительских ветках суммы дочерних элементов  [new]
tibidoh
Member

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

Вряд ли это будет эффективно
13 окт 17, 12:04    [20866957]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная агрегация на родительских ветках суммы дочерних элементов  [new]
Владислав Колосов
Member

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

А итог как должен выглядеть?
13 окт 17, 17:59    [20868414]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить