Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / July 2006

Tip: Looking for answers? Try searching our database.

Trees, recursion, and grouping

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
jefftyzzer - 06 Jul 2006 22:57 GMT
Friends:

In a DB2 UDB LUW table, I have a table with pairs of equivalent ID's.
What I want to do is assign all equivalent IDs to the same group
number, including those that are transitively related, i.e., if A = B
and B = C then A = C, so I'd group all three together.

Although they're not related in a composition fashion per se, it seems
like the way to go conceptually is to consider the ID relationships as
a reporting tree (ID_A would be the manager and ID_B would be the
employee) and assign all IDs that share the same root to the same
group.

For instance, let's say I have the following pairs

ID_A ID_B
---- ----
1800 1804
1800 1808
1806 1809
1808 1810
1808 1812
1809 1815
1810 1811

I'd have two trees (sideways):

1800 1804
    1808
        1810
            1811
        1812
and

1806
   1809
       1815

I'm struggling with the following:

1. How to group based on a shared *root* (I'd hate to have to build a
chain column, e.g., for 1811: "1800-->1808-->1810" and do something
like DENSE_RANK() OVER(ORDER BY SUBSTR(CHAIN,1,4)--that seems
unreliable, and the ID is not always the same length)

2. How to write a recursive CTE that accomodates multiple, independent,
trees.

What I'd like to end up with is this:

ID   GRP
---- ---
1800 1
1804 1
1808 1
1810 1
1811 1
1812 1
1806 2
1809 2
1815 2

I feel like I'm close--I've read Serge's "CONNECT BY" article and
Molinaro's chapter "Hierarchical Queries" in his _SQL Cookbook_, but
I'm just not able to stitch it all together.

Would anyone care to lend a hand?

Thanks,

--Jeff
jefftyzzer - 07 Jul 2006 00:12 GMT
I should have mentioned that by "equivalent IDs," I meant logically
equivalent, i.e., they identify the same thing. Also, to view my tables
and "trees," please click fixed-font.

--Jeff

> Friends:
>
[quoted text clipped - 67 lines]
>
> --Jeff
4.spam@mail.ru - 07 Jul 2006 08:34 GMT
Hello.
Try this:
--------------
with a(ID_A, ID_B) as
(
values
(1800, 1804),
(1800, 1808),
(1806, 1809),
(1808, 1810),
(1808, 1812),
(1809, 1815),
(1810, 1811)
), t(level, id_a, id_b, chain) as
(
select distinct 1, id_a, id_a, cast(digits(id_a) as varchar(50))
from a
where not exists
(
select 1
from a a2
where a2.id_b=a.id_a
)
 UNION ALL
select t.level+1, t.id_a, a.id_b, t.chain||digits(a.id_b)
from a, t
where a.id_a=t.id_b
)
select dense_rank() over(order by id_a) as grp,
substr(
repeat(' ', level-1)||
char(case level when 1 then id_a else id_b end)
,1,20)
--, level, chain
from t
order by id_a, chain;
--------------

> Friends:
>
[quoted text clipped - 67 lines]
>
> --Jeff
jefftyzzer - 07 Jul 2006 19:12 GMT
Brilliant!  Thank you, kind stanger :-)

> Hello.
> Try this:
[quoted text clipped - 105 lines]
> >
> > --Jeff
jefftyzzer - 07 Jul 2006 19:12 GMT
Brilliant!  Thank you, kind stanger :-)

> Hello.
> Try this:
[quoted text clipped - 105 lines]
> >
> > --Jeff
jefftyzzer - 07 Jul 2006 19:17 GMT
Brilliant! Thank you, kind stranger :-)

> Hello.
> Try this:
[quoted text clipped - 105 lines]
> >
> > --Jeff
--CELKO-- - 08 Jul 2006 17:32 GMT
In a DB2 UDB LUW table, I have a table with pairs of equivalent ID's.
What I want to do is assign all equivalent IDs to the same group
number, including those that are transitively related, i.e., if A = B
and B = C then A = C, so I'd group all three together.

Although they're not related in a composition fashion per se, it seems
like the way to go conceptually is to consider the ID relationships as
a reporting tree (ID_A would be the manager and ID_B would be the
employee) and assign all IDs that share the same root to the same
group.

For instance, let's say I have the following pairs

ID_A ID_B
---- ----
1800 1804
1800 1808
1806 1809
1808 1810
1808 1812
1809 1815
1810 1811

I'd have two trees (sideways):

1800 1804
     1808
         1810
             1811
         1812
and

1806
    1809
        1815

I'm struggling with the following:

1. How to group based on a shared *root* (I'd hate to have to build a
chain column, e.g., for 1811: "1800-->1808-->1810" and do something
like DENSE_RANK() OVER(ORDER BY SUBSTR(CHAIN,1,4)--that seems
unreliable, and the ID is not always the same length)

2. How to write a recursive CTE that accomodates multiple,
independent,
trees.

>> I feel like I'm close--I've read Serge's "CONNECT BY" article and Molinaro's chapter "Hierarchical Queries" in his _SQL Cookbook_, but I'm just not able to stitch it all together. <<

You should have been reading Celko's TREES & HIERARCHIES IN SQL instead
:)!

What you have is a forest, not a tree and you can do this with a nested
sets model

CREATE TABLE FoobarForest
(foobar_id INTEGER NOT NULL PRIMARY KEY,  --assumption
tree_nbr INTEGER NOT NULL,
lft INTEGER NOT NULL CHECK (lft > 0),
rgt INTEGER NOT NULL,
CHECK (lft < rgt),
UNIQUE (tree_nbr, lft));

Instead of a recursive CTE, pick the roots and use a simple push-down
stack for tree traversal.  Here is a SQL/PSM version for one tree:

- Tree holds the adjacency model
CREATE TABLE Tree
(node CHAR(10) NOT NULL,
parent CHAR(10));

-- Stack starts empty, will holds the nested set model
CREATE TABLE Stack
(stack_top INTEGER NOT NULL,
node CHAR(10) NOT NULL,
lft INTEGER,
rgt INTEGER);

CREATE PROCEDURE TreeTraversal ()
LANGUAGE SQL
DETERMINISTIC
BEGIN ATOMIC
DECLARE counter INTEGER;
DECLARE max_counter INTEGER;
DECLARE current_top INTEGER;

SET counter = 2;
SET max_counter = 2 * (SELECT COUNT(*) FROM Tree);
SET current_top = 1;

--clear the stack
DELETE FROM Stack;

-- push the root
INSERT INTO Stack
SELECT 1, node, 1, max_counter
 FROM Tree
WHERE parent IS NULL;

-- delete rows from tree as they are used
DELETE FROM Tree WHERE parent IS NULL;

WHILE counter <= max_counter- 1
DO IF EXISTS (SELECT *
             FROM Stack AS S1, Tree AS T1
            WHERE S1.node = T1.parent
              AND S1.stack_top = current_top)
  THEN BEGIN -- push when top has subordinates and set lft value
       INSERT INTO Stack
       SELECT (current_top + 1), MIN(T1.node), counter, NULL
         FROM Stack AS S1, Tree AS T1
        WHERE S1.node = T1.parent
          AND S1.stack_top = current_top;

       -- delete rows from tree as they are used
       DELETE FROM Tree
        WHERE node = (SELECT node
                        FROM Stack
                       WHERE stack_top = current_top + 1);
       -- housekeeping of stack pointers and counter
       SET counter = counter + 1;
       SET current_top = current_top + 1;
    END;
    ELSE
    BEGIN -- pop the stack and set rgt value
      UPDATE Stack
         SET rgt = counter,
             stack_top = -stack_top -- pops the stack
       WHERE stack_top = current_top;
      SET counter = counter + 1;
      SET current_top = current_top - 1;
    END;
END IF;
END WHILE;
-- SELECT node, lft, rgt FROM Stack;
-- the top column is not needed in the final answer
-- move stack contents to new tree table
END;
jefftyzzer - 12 Jul 2006 06:52 GMT
Thank you, Joe--I look forward to digging into your suggestion, and
appreciate your considered reply to my posting.

--Jeff

> In a DB2 UDB LUW table, I have a table with pairs of equivalent ID's.
>  What I want to do is assign all equivalent IDs to the same group
[quoted text clipped - 134 lines]
> -- move stack contents to new tree table
> END;
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.