'Summation graph over tree in PostgreSQL

I have salaries table and a tree with departments (child, parent). Need to go over the graph and calculate every summation vertex. The output I need - summation of all child nodes. The issue looks like the request is double counting values.

Test data:

CREATE TABLE deps (
    id serial,
    child varchar,
    parent varchar);

CREATE TABLE salaries (
    name varchar,
    salary numeric);

INSERT INTO salaries(name, salary) VALUES 
('manager1', 100),
('manager2', 100),
('manager3', 100),
('manager4', 100),
('manager5', 100),
('manager6', 100),
('manager7', 100),
('manager8', 100),
('manager9', 100),
('engeneer1', 100),
('engeneer2', 100),
('engeneer3', 100),
('engeneer4', 100),
('engeneer5', 100),
('engeneer6', 100),
('engeneer7', 100),
('engeneer8', 100),
('engeneer9', 100),
('engeneer10', 100),
('accountant1', 100),
('accountant2', 100),
('accountant3', 100),
('accountant4', 100);

insert INTO deps(child, parent) VALUES 
('manager1', 'management'),
('manager2', 'management'),
('manager3', 'management'),
('manager4', 'management'),
('management_team1', 'management'),
('management_team1_1', 'management_team1'),
('management_team1_2', 'management_team1'),
('manager5', 'management_team1_1'),
('manager6', 'management_team1_1'),
('manager7', 'management_team1'),
('manager8', 'management_team1_2'),
('manager9', 'management_team1_2'),
('engeneer1', 'it'),
('engeneer2', 'it'),
('engeneer3', 'it'),
('engeneer4', 'it'),
('it_dep1', 'it'),
('it_dep2', 'it'),
('engeneer5', 'it_dep1'),
('engeneer6', 'it_dep1'),
('engeneer7', 'it_dep2'),
('engeneer8', 'it_dep2'),
('it_dep3', 'it_dep2'),
('engeneer9', 'it_dep3'),
('engeneer10', 'it_dep3'),
('accountant1', 'accounts'),
('accountant2', 'accounts'),
('accountant3', 'accounts'),
('accountant4', 'accounts'),
('management', NULL),
('accounts', NULL),
('it', NULL);

Request:

WITH RECURSIVE tree ("depth", parent, child) as(
    SELECT 
        0,
        parent,
        child
    FROM deps
    WHERE parent IS NULL
    UNION
    SELECT
        "depth" + 1,
        tree.child,
        deps.child
    FROM tree JOIN deps ON tree.child = deps.parent
),
graph (child, parent, "depth", value) as(
-- non recursive
    SELECT
        tree.child,
        tree.parent,
        tree."depth",
        salaries.salary -- outbound amount FROM node which IS equal TO salary AT this depth
    FROM 
        tree JOIN salaries ON salaries.name = tree.child
    WHERE tree."depth" = (SELECT max("depth") FROM tree) -- we START FROM deepest LEVEL OF yierarchy
    UNION
-- recursive
    SELECT
          current_tree.child,
          current_tree.parent,
          current_tree."depth",
          COALESCE(current_tree.salary,
                   sum(graph.value) OVER (PARTITION BY current_tree.child)
                  ) -- outbound amount FROM node which IS equal TO SUM of ALL incoming amounts 
    FROM 
         graph,
         LATERAL (SELECT * FROM tree LEFT JOIN salaries ON salaries.name = tree.child WHERE tree."depth" = graph."depth" - 1) AS current_tree
         LEFT JOIN LATERAL (SELECT * FROM tree WHERE tree."depth" = graph."depth") AS previous_tree
         ON current_tree.child = previous_tree.parent
--    WHERE graph."depth" = (SELECT max("depth") FROM graph)
)
SELECT * FROM graph

WHERE graph."depth" = (SELECT max("depth") FROM graph) gives error since double calling of graph table is not allowed

DB Fiddle sample

the structure of data

I expect to see 32 relations corresponding to initial tree with sums as values of all child nodes.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source