Обсуждение: Cascading sum in tree with CTE?

Hi .

My problem resembles this:

I have the following (simplified) tables

1) create table account  ( id serial, name varchar, parent_id int4
references account, primary key (id))
2) create table transaction (id serial, account_id int4 references
account, memo varchar, debet, credit, primary key(id))

So a basic ledger system, with a tree of accounts and transactions on
them. Some accounts have transactions others doesn't.

I have written a CTE which generates the tree, but I would like to
append to each line the sum of all debits and credits of this and all
sub accounts.
I think it sould be doable, but cannot bend my brain around it and my
google-fu has come out short.

Say the data was:

Account:
1, 'Expenses', null
2, 'IRS', 1
3, '7-Eleven'

Transaction:
1, 2, 'Tax Jan 10', null, 100
2, 2, 'Tax Feb 10', null, 120
3, 2, 'Tax Feb 10 correction', 10,null
4, 3, 'Sodas', 10, null
5, 3, 'Beer', 5, null

I would expect a tree like:

1, Debt, 25, 220
2, IRS, 10, 220
3, 7-eleven, 15, 0

Is there any way around a writing a stored procedure for that?

My current CTE works top down ( top > Expenses > 7-elven) and writes out "paths" to the leaves (ie "Expenses ->
7-Eleven"instead of just 7-Eleven)
 

Svenne

to select a whole subtree of a particular node of a tree.
u have to modify the tree representation in one of the two ways
(according to you fine-tuned needs)

(1) store in the EACH node the PATH from root to this node
(2) store (l,r) segment representing the INCLUSIONS of nodes into other 
nodes subtree (exactly as segments include each other)

(2) i forgot the "official" name of this type of tree representation.

In article <4BBED49D.7080501@krap.dk>,
Svenne Krap <svenne.lists@krap.dk> writes:

> Hi .
> My problem resembles this:

> I have the following (simplified) tables

> 1) create table account  ( id serial, name varchar, parent_id int4
> references account, primary key (id))
> 2) create table transaction (id serial, account_id int4 references
> account, memo varchar, debet, credit, primary key(id))

> So a basic ledger system, with a tree of accounts and transactions on
> them. Some accounts have transactions others doesn't.

> I have written a CTE which generates the tree, but I would like to
> append to each line the sum of all debits and credits of this and all
> sub accounts.
> I think it sould be doable, but cannot bend my brain around it and my
> google-fu has come out short.

> Say the data was:

> Account:
> 1, 'Expenses', null
> 2, 'IRS', 1
> 3, '7-Eleven'

> Transaction:
> 1, 2, 'Tax Jan 10', null, 100
> 2, 2, 'Tax Feb 10', null, 120
> 3, 2, 'Tax Feb 10 correction', 10,null
> 4, 3, 'Sodas', 10, null
> 5, 3, 'Beer', 5, null

> I would expect a tree like:

> 1, Debt, 25, 220
> 2, IRS, 10, 220
> 3, 7-eleven, 15, 0

> Is there any way around a writing a stored procedure for that?

How about an ancestor table?

WITH RECURSIVE tree (id, anc) AS ( SELECT id, id FROM account
UNION ALL SELECT a.id, t.anc FROM account a JOIN tree t ON t.id = a.parent_id
)
SELECT a.id, a.name, sum(x.debet) AS debet, sum(x.credit) AS credit
FROM account a
JOIN tree t ON t.anc = a.id
LEFT JOIN transaction x ON x.account_id = t.id
GROUP BY a.id, a.name