Recursive SQL: aggregate function using Recursive Subquery Factoring

Question

Table T represents a tree. Every record is a node and each node has only one parent.

This query calculates the SUM() of each branch for every node.

WITH t AS
        (SELECT  1 id, NULL parent_id, NULL value FROM dual UNION ALL
         SELECT 10 id,    1 parent_id, 1000 value FROM dual UNION ALL
         SELECT 20 id,    1 parent_id, 2000 value FROM dual UNION ALL
         SELECT 30 id,   10 parent_id, 3000 value FROM dual UNION ALL
         SELECT 40 id,   10 parent_id, 4000 value FROM dual UNION ALL
         SELECT 50 id,   20 parent_id, 5000 value FROM dual UNION ALL
         SELECT 60 id,    1 parent_id, 6000 value FROM dual UNION ALL
         SELECT 70 id,   60 parent_id, 7000 value FROM dual UNION ALL
         SELECT 80 id,   70 parent_id, 8000 value FROM dual
    ) SELECT CAST(LPAD(' ', (LEVEL-1)*4) || ID AS VARCHAR2(20))  id
        ,VALUE                                                   self_value
        ,(SELECT SUM (value)
          FROM   t t2
          CONNECT BY 
            PRIOR t2.ID = t2.parent_id
            START WITH t2.id = t.id)                             branch_value
      FROM   t
      CONNECT BY PRIOR t.id = t.parent_id
      START WITH t.parent_id IS NULL
      ORDER SIBLINGS BY t.id;

ID                   SELF_VALUE BRANCH_VALUE
-------------------- ---------- ------------
1                                      36000
    10                     1000         8000
        30                 3000         3000
        40                 4000         4000
    20                     2000         7000
        50                 5000         5000
    60                     6000        21000
        70                 7000        15000
            80             8000         8000

9 rows selected.

I've been trying to achieve the same result of this query using the alternative Subquery Factoring syntax. Any help would be truly appreciated !

As you said ´and each node has only one parent´ is your format wrong on the question? 30 and 40 has 10 as parent. — Jorge Campos
Thanks for your comment Jorge.. what I mean is that every node/record has only one parent. In a tree structure, each parent may have several children. That's what happen with #30 and #40, both having #10 as their parent. Let me know if this is not clear enough. — abrittaf
Own... thanks. English is not my primary language and I always mix parent with child. Sorry. And I have a question, you want to do this without the WITH command is that it? — Jorge Campos
No Jorge,.. take a look at Recursive Subquery Factoring doc from Oracle and see some examples at the end of the article to see what it is. I want to do this without the CONNECT BY clause — abrittaf
Here's what I've got so far; sadly not quite there yet / I'm not familiar enough with Oracle... sqlfiddle.com/#!4/d41d8/32449 — JohnLBevan

Jan Martin Keil Jan Martin Keil · Accepted Answer · 2014-07-14T22:33:47

Until GROUP BY is supported in recursive queries, I think it's not possible with only one query. So I added a second subquery in the WITH-clause. Maybe this is sufficient for your problem (nice brain-teasers).

This will calculate the data:

WITH t AS
        (SELECT  1 id, NULL parent_id, NULL value FROM dual UNION ALL
         SELECT 10 id,    1 parent_id, 1000 value FROM dual UNION ALL
         SELECT 20 id,    1 parent_id, 2000 value FROM dual UNION ALL
         SELECT 30 id,   10 parent_id, 3000 value FROM dual UNION ALL
         SELECT 40 id,   10 parent_id, 4000 value FROM dual UNION ALL
         SELECT 50 id,   20 parent_id, 5000 value FROM dual UNION ALL
         SELECT 60 id,    1 parent_id, 6000 value FROM dual UNION ALL
         SELECT 70 id,   60 parent_id, 7000 value FROM dual UNION ALL
         SELECT 80 id,   70 parent_id, 8000 value FROM dual),
hierarchy (id,ancestor,value) AS (
  SELECT t.id,t.id,t.value
    FROM t
  UNION ALL
  SELECT t.id,h.ancestor,t.value
    FROM t
      INNER JOIN hierarchy h
        ON t.parent_id = h.id)
SELECT h.ancestor, t.parent_id, t.value, SUM(h.value)
  FROM hierarchy h
    INNER JOIN t
      ON t.id = h.ancestor
  GROUP BY h.ancestor,t.value,t.parent_id;

To get the same order and formatting as described in the question add calculation of path and depth:

WITH t AS
        (SELECT  1 id, NULL parent_id, NULL value FROM dual UNION ALL
         SELECT 10 id,    1 parent_id, 1000 value FROM dual UNION ALL
         SELECT 20 id,    1 parent_id, 2000 value FROM dual UNION ALL
         SELECT 30 id,   10 parent_id, 3000 value FROM dual UNION ALL
         SELECT 40 id,   10 parent_id, 4000 value FROM dual UNION ALL
         SELECT 50 id,   20 parent_id, 5000 value FROM dual UNION ALL
         SELECT 60 id,    1 parent_id, 6000 value FROM dual UNION ALL
         SELECT 70 id,   60 parent_id, 7000 value FROM dual UNION ALL
         SELECT 80 id,   70 parent_id, 8000 value FROM dual),
hierarchy (id,ancestor,value,depth,path) AS (
  SELECT t.id,t.id,t.value,0,''||t.id
    FROM t
  UNION ALL
  SELECT t.id,h.ancestor,t.value,h.depth+1,h.path||'.'||t.id
    FROM t
      INNER JOIN hierarchy h
        ON t.parent_id = h.id)
SELECT LPAD(h.ancestor,p.depth*4+1,' ') AS id, t.value AS self_value, SUM(h.value) as branch_value
  FROM hierarchy h
    INNER JOIN t
      ON t.id = h.ancestor
    INNER JOIN (SELECT id,depth,path
                  FROM hierarchy
                  WHERE ancestor IN (SELECT id FROM t WHERE parent_id IS NULL)
                  ORDER BY path) p
      ON p.id = t.id
  GROUP BY h.ancestor,t.value,t.parent_id,p.path,p.depth
  ORDER BY p.path;

Recursive SQL: aggregate function using Recursive Subquery Factoring

1 Answers