How to sum a column in SQL Server recursive cte for optimization?

Question

I have following table with hierarchical data:

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

I want to find number of affected items under each folders and all of its children. I can write a recursive cte, which can produce following result, after that by doing group by I can find out what I want.

Normal recursive 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          
  )

Result:

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

But I want to optimize it, I want to start from the leaf child, while moving through the CTE itself, I want to compute NumberOfAffectedItems.

Expected CTE

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
  )

Result:

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

If I group by ParentFolderId, I will get a wrong result, the reason is while doing computing in CTE, the same parent folder is visited from multiple children, hence results in a wrong result. I want to find out is there anyway we can compute the result while going through the CTE itself.

Could you post your cte please? – Tyron78 Sep 14 '17 at 06:30 — Tyron78, Sep 14 '17 at 06:30
This is the cte I am using now, this gives correct result – Mohanraja Sep 14 '17 at 06:33 — Mohanraja, Sep 14 '17 at 06:33
Where is the cte? I only see the results... – Tyron78 Sep 14 '17 at 06:33 — Tyron78, Sep 14 '17 at 06:33
I have updated the cte in the question – Mohanraja Sep 14 '17 at 06:38 — Mohanraja, Sep 14 '17 at 06:38

Tyron78 · Answer 1 · 2017-09-14T08:37:34.753

Please check the following solution. I used your cte as basis and added the calculation (as column x) to it:

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)

Result:

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

Thanks @Tyron78 , however as you have used the same CTE as I used, it returns the same number of raw result before the summation, I want to start traversal from leaf child, and while moving through cte itself i want to compute the result, I don't know whether its possible or not. Please take a look at the second cte in the question for your reference. — Mohanraja, Sep 14 '17 at 07:11
I modified the query in order to use the leaf-cte as proposed in your second cte. — Tyron78, Sep 14 '17 at 08:37
P.S.: Furthermore I had to modify the join in your second cte - it produced too many records. — Tyron78, Sep 14 '17 at 09:11
Thanks @Tyron78, however if I want to find how many number of affected items under folderid 1, right now your query will give 20, but the actual count is 13. — Mohanraja, Sep 14 '17 at 09:28
Not exactly. You can's sum up over the parent folder ID (10 + 8 + 2) since the mentioned Affected items are the corresponding aggregates for leafes 3, 4 and 5 - the ones defined as leafes. Starting from the leaf, you can not calculate / aggregate the correct number of items for non-leaf members. — Tyron78, Sep 14 '17 at 09:34
P.S.:Therefore I fist suggested the tree parent --> leaf instead of leaf --> parent — Tyron78, Sep 14 '17 at 09:40

How to sum a column in SQL Server recursive cte for optimization?

1 Answers1