How to get the cumulative sum of children up its parents?-CodePudding

So I have written a query to get the cumulative sum of children but I think partition sum has error as its totalling for the parent that is not part of the children.

My fiddle is

CodePudding user response：

To get totals for tree nodes you need to generate hierarchy tree for every node in a subquery like this

SELECT
    d.*,
    v.volume,
    (
        WITH RECURSIVE cte AS (
                SELECT 
                    dd.id AS branch_id,
                    dd.id
                FROM dimensionvalue dd
                WHERE dd.id = d.id
                UNION ALL
                SELECT
                    cte.branch_id,
                    dd.id
                FROM dimensionvalue dd
                JOIN cte ON dd.dimensionvalueid = cte.id
        )
        SELECT SUM(v.volume)
        FROM cte
        JOIN valuation v ON v.dimensionvalueid = cte.id
        GROUP BY cte.branch_id
    ) AS totals
FROM dimensionvalue d
LEFT JOIN valuation v ON v.dimensionvalueid = d.id
ORDER BY d.name;

If you really need all those "decoration" columns that you generate in your query for each tree node than you can combine your recursive CTE hierarchy with subquery for totals calculation like this

WITH RECURSIVE hierarchy AS (
    SELECT
    d.id,
    d.name,
    d.dimensionvalueid,
    0 AS level,
        CAST(d.id AS varchar(50)) AS order_sequence
    FROM dimensionvalue d
    WHERE d.dimensionvalueid IS NULL
    UNION ALL
    SELECT
    e.id,
    e.name,
    e.dimensionvalueid,
    hierarchy.level   1 AS level,
    CAST(hierarchy.order_sequence || '_' || CAST(hierarchy.id AS VARCHAR(50)) AS VARCHAR(50)) AS order_sequence
    FROM hierarchy
    JOIN dimensionvalue e ON e.dimensionvalueid = hierarchy.id
)
SELECT
    RIGHT('-----------', h.level * 5) || h.name || ' ' AS parent_child_tree, 
    h.*,
    v.volume,
    (
        WITH RECURSIVE cte AS (
                SELECT 
                    dd.id AS branch_id,
                    dd.id
                FROM dimensionvalue dd
                WHERE dd.id = h.id
                UNION ALL
                SELECT
                    cte.branch_id,
                    dd.id
                FROM dimensionvalue dd
                JOIN cte ON dd.dimensionvalueid = cte.id
        )
        SELECT SUM(v.volume)
        FROM cte
        JOIN valuation v ON v.dimensionvalueid = cte.id
        GROUP BY cte.branch_id
    ) AS totals
FROM hierarchy h
LEFT JOIN valuation v ON v.dimensionvalueid = h.id
ORDER BY h.name

You can check a working demo here