Re: CONNECT BY / START WITH query to count "families"

trippknightly_at_hotmail.com wrote:
> Suppose I have a table that contains Account# & RelatedAccount# (among
> other things).
>
> How could I use CONNECT BY & START WITH in a query to count
> relationships or families.
>
> For example, in
>
> ACCT REL_ACCT
> Bob Mary
> Bob Jane
> Jane Bob
> Larry Moe
> Curly Larry
>
> there are 2 relationship sets (Bob,Mary,Jane & Larry,Moe,Curly). If I
> added
>
> Curly Jane
>
> then there'd be only 1 larger family. Can I use CONNECT BY & START
> with to detect such relationships and count them? In my case I'd be
> willing to go any number of levels deep in the recursion. Am open to
> other suggestions as well.

The idea: Label all the graph nodes with distinct primes. Find all simple paths in the graph weighted with product of nodes. Find the skyline of all maximal products. Count them.

Implementation (switched to boolean vectors instead of primes)

create table input as (
select 1 x, 2 y from dual
union
select 2, 3 from dual
union
select 2, 4 from dual
union
select 6, 7 from dual
union
select 8, 9 from dual);

with TC as ( -- Transitive Closure
 select connect_by_root(x) x, y
 from (
  select x,y from input -- make relation symmetric   union
  select y,x from input
 ) connect by nocycle x = prior y
), weightedNodes as (
  select node, power(2,rownum-1) code
  from (select x node from input
  union select y node from input )
)
select count(distinct code) from (
  select x,y,sum(code) code from ( -- weighted paths     select distinct pre.x, pre.y mid, post.y from TC pre, TC post     where pre.y=post.x
  ), weightedNodes
  where node = mid
  group by x,y
);

drop table input;

BTW, I'm writing advanced SQL book. Please comment if you consider examples like this intersting or not. Received on Tue Sep 27 2005 - 19:59:47 CDT