Как суммировать столбец в рекурсивном cte SQL Server для оптимизации?

У меня есть следующая таблица с иерархическими данными:

FolderId ParentFolderId NumberOfAffectedItems
---------------------------------------------
1           NULL        2
2           1           3
3           2           5
4           2           3
5           1           0

Я хочу найти количество затронутых элементов в каждой папке и всех ее дочерних элементах. Я могу написать рекурсивный cte, который может дать следующий результат, после чего, выполнив группировку, я смогу узнать, что я хочу.

Нормальный рекурсивный CTE:

WITH FolderTree AS
(
    SELECT
        fsa.FolderId AS ParentFolderId,
        fsa.FolderId AS ChildFolderId,          
        fsa.NumberOfReportsAffected
    FROM
        FoldersWithNumberOfReportsAffected fsa

    UNION ALL

    SELECT
        ft.ParentFolderId,
        fsa.FolderId AS ChildFolderId,                  
        fsa.NumberOfReportsAffected
    FROM
        FoldersWithNumberOfReportsAffected fsa
    INNER JOIN
        FolderTree ft ON fsa.ParentFolderId = ft.ChildFolderId          
  )

Результат:

ParentFolderId ChildFolderId NumberOfAffectedItems
--------------------------------------------------
1               1           2
1               2           3
1               3           5
1               4           3
1               5           0
2               2           3
2               3           5
2               4           3
3               3           5
4               4           3
5               5           0

Но я хочу его оптимизировать, я хочу начать с дочернего листа, двигаясь по самому CTE, я хочу вычислить NumberOfAffectedItems.

Ожидаемый КТР

WITH FolderTree AS
(
    SELECT
        fsa.FolderId AS LeafChildId,
        fsa.FolderId AS ParentFolderId,         
        fsa.NumberOfReportsAffected
    FROM
        FoldersWithNumberOfReportsAffected fsa
    LEFT JOIN
        FoldersWithNumberOfReportsAffected f ON fsa.folderid = f.ParentfolderId
    WHERE
        f.ParentfolderId is null -- this is finding leaf child

    UNION ALL

    SELECT
        ft.LeafChildId,
        fsa.FolderId AS ParentFolderId,                 
        fsa.NumberOfReportsAffected + ft.NumberOfReportsAffected AS [ComputedResult]
    FROM
        FoldersWithNumberOfReportsAffected fsa
    INNER JOIN 
        FolderTree ft ON fsa.FolderId = ft.ParentFolderId
  )

Результат:

LeafChildId ParentFolderId ComputedNumberOfAffectedItems
---------------------------------------------------------
3           3               5
3           2               8
3           1               10
4           4               3
4           2               5
4           1               7
5           5               0
5           1               2

Если я сгруппирую по ParentFolderId, я получу неправильный результат, причина в том, что при выполнении вычислений в CTE одна и та же родительская папка посещается несколькими дочерними элементами, что приводит к неправильному результату. Я хочу выяснить, можем ли мы в любом случае вычислить результат, проходя через CTE.


sql-server recursive-cte
person Mohanraja    schedule 14.09.2017    source источник
comment
Не могли бы вы опубликовать свой КТ, пожалуйста?   -  person Tyron78    schedule 14.09.2017
comment
Это cte, который я использую сейчас, это дает правильный результат   -  person Mohanraja    schedule 14.09.2017
comment
Где ктэ? Я вижу только результаты...   -  person Tyron78    schedule 14.09.2017
comment
Я обновил cte в вопросе   -  person Mohanraja    schedule 14.09.2017

Ответы (1)


arrow_upward
1
arrow_downward

Проверьте следующее решение. Я использовал ваш cte в качестве основы и добавил к нему расчет (как столбец x):

DECLARE @t TABLE(
  FolderID INT
 ,ParentFolderID INT
 ,NumberOfAffectedItems INT
);

INSERT INTO @t VALUES (1           ,NULL        ,2)
                     ,(2           ,1           ,3)
                     ,(3           ,2           ,5)
                     ,(4           ,2           ,3)
                     ,(5           ,1           ,0);


WITH FolderTree AS
(
    SELECT 1lvl,
        fsa.FolderId AS LeafChildId,
        fsa.ParentFolderId AS ParentFolderId,
        fsa.NumberOfAffectedItems
    FROM
        @t fsa
    LEFT JOIN
        @t f ON fsa.folderid = f.ParentfolderId
    WHERE
        f.ParentfolderId is null -- this is finding leaf child

    UNION ALL

    SELECT lvl + 1,
        ft.LeafChildId,
        fsa.ParentFolderId,                 
        fsa.NumberOfAffectedItems
    FROM
        FolderTree ft
    INNER JOIN @t fsa
        ON fsa.FolderId = ft.ParentFolderId
  )
SELECT  LeafChildId,
        ISNULL(ParentFolderId, LeafChildId) ParentFolderId,
        NumberOfAffectedItems,
        SUM(NumberOfAffectedItems) OVER (PARTITION BY LeafChildId ORDER BY ISNULL(ParentFolderId, LeafChildId) DESC) AS x
  FROM FolderTree
  ORDER BY 1, 2 DESC
  OPTION (MAXRECURSION 0)

Результат:

LeafChildId ParentFolderId  NumberOfAffectedItems   x
3           3               2                       2
3           2               5                       7
3           1               3                       10
4           4               2                       2
4           2               3                       5
4           1               3                       8
5           5               2                       2
5           1               0                       2
person Tyron78    schedule 14.09.2017
comment
Спасибо @ Tyron78, однако, поскольку вы использовали тот же CTE, что и я, он возвращает то же количество необработанных результатов перед суммированием, я хочу начать обход с листового дочернего элемента, и при перемещении по самому CTE я хочу вычислить результат, Я не знаю, возможно ли это или нет. Пожалуйста, взгляните на второй пункт в вопросе для справки. - person Mohanraja; 14.09.2017
comment
Я изменил запрос, чтобы использовать leaf-cte, предложенный во втором cte. - person Tyron78; 14.09.2017
comment
P.S.: Кроме того, мне пришлось изменить соединение во втором cte - оно произвело слишком много записей. - person Tyron78; 14.09.2017
comment
Спасибо @ Tyron78, однако, если я хочу узнать, сколько затронутых элементов в папке с идентификатором 1, прямо сейчас ваш запрос даст 20, но фактическое количество равно 13. - person Mohanraja; 14.09.2017
comment
Не совсем. Вы можете подвести итоги по идентификатору родительской папки (10 + 8 + 2), поскольку упомянутые Затронутые элементы являются соответствующими агрегатами для листов 3, 4 и 5 — тех, которые определены как листы. Начиная с листа, вы не можете рассчитать / агрегировать правильное количество элементов для нелистовых элементов. - person Tyron78; 14.09.2017
comment
P.S.: Поэтому я сначала предложил родитель дерева --› лист вместо листа --› родитель - person Tyron78; 14.09.2017