Recursive query sum leaf values and pass sum to parents. Values stored in another table-CodePudding

I have a problem to calculate parent account balances. In the database there are two tables: account and monthly_balance. In the monthly_balance table we store only leaf account balances, but I have to know the ancestors balances too. Also I have to filter by date interval but right now that is not the main issue.

I tried some solution from stackoverflow but I got null values or another faulty outcome.

example situation:

A1
 - A1.1 (6)
 - A1.2
    - A1.2.1 (1)
    - A1.2.2 (10)
    - A1.2.3 (3)
A2
 - A2.1 
    - A2.1.1 (10)
    - A2.1.2 (5)

On return I should get:

A1 = 20
A1.1 = 6
A1.2 = 14
A1.2.1 = 1
A1.2.2 = 10
A1.2.3 = 3
A2 = 15
A2.1 = 15
A2.1.1 = 10
A2.1.2 = 5

data stored like

create table account(id int primary key, parent_id int, account_name text)
insert into account values 
(1, null, 'A1'),
(2, 1, 'A1.1'),
(3, 1, 'A1.2'),
(4, 3, 'A1.2.1'),
(5, 3, 'A1.2.2'),
(6, 3, 'A1.2.3'),
(7, null, 'A2'),
(8, 7, 'A2.1'),
(9, 8, 'A2.1.1'),
(10, 8, 'A2.1.2');

create table monthly_balance(id int primary key, account_id int foreign key, balance numeric, year_month date)
insert into account values 
(1, 2, 6, '2022-08-01'),
(2, 4, 1, '2022-08-01'),
(3, 5, 10, '2022-08-01'),
(4, 6, 3, '2022-08-01'),
(5, 9, 10, '2022-08-01'),
(6, 10, 5, '2022-08-01');

CodePudding user response：

you can use recursive cte for this:

with recursive cte_account AS (
    select
        a.id as account_id,
        a.id as balance_account_id
    from account as a
    where
        not exists (select from account as tt where tt.parent_id = a.id)
  
    union all
  
    select
        a.parent_id as account_id,
        c.balance_account_id
    from cte_account as c
        inner join account as a on
            a.id = c.account_id
    where
        a.parent_id is not null
), cte_balance as (
  select
      a.account_id,
      sum(b.balance) as balance
  from cte_account as a
      inner join monthly_balance as b on
          b.account_id = a.balance_account_id
  group by
      a.account_id
)
select
    a.account_name,
    b.balance
from cte_balance as b
    inner join account as a on
        a.id = b.account_id
order by
    a.id asc

see db<>fiddle example