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

From: <BigBoote66_at_hotmail.com>
Date: 29 Sep 2005 09:12:56 -0700
Message-ID: <1128010376.259542.40290@z14g2000cwz.googlegroups.com>

My answer isn't as exotic as the previous poster's, but it also simpler to understand and doesn't involve you labelling the original values with primes (it may also be less efficient - you'll have to check). The basic idea is to create a copy (TempRelations) of your Relations table (assuming that's the ACCT/REL_ACCT table you give an example of above), then insert one row from it into a new table (MyFamilies), using its ROWID as a "Family ID".

Then iterate the following steps until no more copying can be done:

1. Insert all ACCTS whose REL_ACCT is in MyFamilies
2. Insert all REL_ACCTS whose ACCT is in MyFamilies
3. Delete all rows from TempRelations which you've inserted into MyFamilies

Once that dies down, insert a single row from TempRelation into MyFamilies & repeat again, until there are no more rows left in TempRelation.

When you're done, the MyFamilies table will have all the families in it, with FamilyID identifying a family. Because you allow relations to go both ways (such as rows 2 & 3 in your example), there will be dupes in the table that you can remove with a SELECT DISTINCT after the fact.

SELECT COUNT(DISTINCT FamilyID) FROM MyFamilies gives you the number of families.

SELECT DISTINCT ACCT From MyFamilies where FamilyID = X gives you the members of a family.

You may want to add some indexes to the TempRelation & MyFamily tables in order to make this more efficient.

Implementation:

CREATE TABLE TempRelation AS
SELECT ACCT
     , REL_ACCT
  FROM Relations;

CREATE TABLE MyFamilies AS
SELECT ROWID FamilyId

     , ACCT
     , ROWID OrigRowId

  FROM TempRelation
 WHERE Rownum <= 1;

BEGIN
   DECLARE

      MembersCreated       NUMBER := 1;
      FamiliesCreated   NUMBER := 1;
   BEGIN
      WHILE FamiliesCreated > 0 LOOP
         WHILE MembersCreated > 0 LOOP
           INSERT
             INTO MyFamilies
           SELECT MyFamilies.FamilyId


                , TempRelation.ACCT

                , TempRelation.ROWID

             FROM MyFamilies


                , TempRelation

            WHERE TempRelation.REL_ACCT = MyFamilies.ACCT;

            MembersCreated := SQL%ROWCOUNT;

           INSERT
             INTO MyFamilies
           SELECT MyFamilies.FamilyId


                , TempRelation.REL_ACCT

                , TempRelation.ROWID

             FROM MyFamilies


                , TempRelation

            WHERE TempRelation.ACCT = MyFamilies.ACCT;

            MembersCreated := MembersCreated + SQL%ROWCOUNT;

            DELETE TempRelation
             WHERE ROWID IN
                   ( SELECT OrigRowId
                       FROM MyFamilies);

         END LOOP;

            INSERT
              INTO MyFamilies
            SELECT ROWID FamilyId
                 , ACCT
                 , ROWID OrigRowId
             FROM TempRelation
            WHERE rownum <= 1;

           FamiliesCreated := SQL%ROWCOUNT;
           MembersCreated := 1;
      END LOOP;

   END;
END;
/

-Steve Received on Thu Sep 29 2005 - 11:12:56 CDT