Re: CONNECT BY / START WITH query to count "families"
- From: "Vadim Tropashko" <vadimtro_invalid@xxxxxxxxx>
- Date: 27 Sep 2005 17:59:47 -0700
trippknightly@xxxxxxxxxxx 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.
.
- References:
- CONNECT BY / START WITH query to count "families"
- From: trippknightly
- CONNECT BY / START WITH query to count "families"
- Prev by Date: Re: What do Oracle professionals think of Fabian Pascal?
- Next by Date: Re: Understanding EXECUTE IMMEDIATE
- Previous by thread: CONNECT BY / START WITH query to count "families"
- Next by thread: Re: CONNECT BY / START WITH query to count "families"
- Index(es):
Relevant Pages
|
