1 2 Previous Next 28 Replies Latest reply: Dec 19, 2012 11:34 AM by 980341 RSS

    Hierarchical Queries

    980341
      Hi,

      I am trying to find a working query for the following problem:

      The table 'users' contains the following information:


      username | is_friend_of
      --- --- --- --- --- --- --- --- ---
      Alfred | Anika
      Anika | Andreas
      Andreas | Armin
      Armin | Axel
      Axel | Anika
      Armin | Anika
      Bill | Boris
      Ben | Boris
      Ben | Bill
      Ben | Bruno
      Claudia | Clemens

      The query should list the number of friends and the name of the friends for each "circle of friends".


      For the upper example the result would look like the following:

      circle | number_of_friends | Names
      --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
      Curcle1 | 5 | Alfred,Andreas,Anika,Armin,Axel
      Curcle2 | 4 | Ben, Bill, Boris, Bruno
      Curcle3 | 2 | Claudia, Clemens



      With the following query I am able to list all friends in the circle of 'Axel'.

      Select DISTINCT username
      from users
      Start with username = 'Axel'
      CONNECT BY NOCYCLE username = PRIOR befreundet_mit

      Any ideas?

      Cheers
      Björn

      Edited by: 977338 on 17.12.2012 08:52
        • 1. Re: Hierarchical Queries
          Frank Kulash
          Hi, Björn,

          Welcome to the forum!

          Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, like this:
          CREATE TABLE     users
          (   username        VARCHAR2 (10)     NOT NULL
          ,   is_friend_of   VARCHAR2 (10)     NOT NULL
          ,   circle        NUMBER             DEFAULT 0     NOT NULL
          );
          
          INSERT INTO users (username, is_friend_of) VALUES ('Alfred',     'Anika');
          INSERT INTO users (username, is_friend_of) VALUES ('Anika',     'Andreas');
          INSERT INTO users (username, is_friend_of) VALUES ('Andreas',     'Armin');
          INSERT INTO users (username, is_friend_of) VALUES ('Armin',     'Axel');
          INSERT INTO users (username, is_friend_of) VALUES ('Axel',     'Anika');
          INSERT INTO users (username, is_friend_of) VALUES ('Armin',     'Anika');
          INSERT INTO users (username, is_friend_of) VALUES ('Bill',     'Boris');
          INSERT INTO users (username, is_friend_of) VALUES ('Ben',     'Boris');
          INSERT INTO users (username, is_friend_of) VALUES ('Ben',     'Bill');
          INSERT INTO users (username, is_friend_of) VALUES ('Ben',     'Bruno');
          INSERT INTO users (username, is_friend_of) VALUES ('Claudia',     'Clemens');
          COMMIT;
          Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
          Simplify the problem as much as possible. Remove all tables and columns that play no role in this problem.
          If you're asking about a DML statement, such as UPDATE, the CREATE TABLE and INSERT statements should re-create the tables as they are before the DML, and the results will be the contents of the changed table(s) when everything is finished.
          Always say which version of Oracle you're using (for example, 11.2.0.2.0).
          See the forum FAQ {message:id=9360002}

          NOCYCLE is one way to do it, but it's extremely inefficient. See {message:id=10743186} for how to do it.

          If you're using Oracle 11.2, a recursive WITH clause might be more efficient, but still slow.

          You're best bet might be using PL/SQL, especially if you can add a column or two to the table.

          Is this a reflexive realtionship? That is, if Alfred is a friend of Anika, does that mean that Anika must be a friend of Alfred? If you change the START WITH clause of your query to
          Start with username = 'Clemens'
          it will produce no rows. Should it return 'Claudia' instead?
          • 2. Re: Hierarchical Queries
            BrendanP
            This is another example of the highly general network resolution problem. Being so general, I wrote a generic query recently that solves the problem relatively efficiently (although not as efficiently as a PL/SQL solution that I also have for this). The generic query requires two views nodes_v and links_v, and obtains all the networks present. You can easily use Listagg to group them into one line (if length below 4,000 characters).

            I also have an extension to the query that goes on to list the structure of each network. Here is the output from both for your data:
            Nodes
            
            NODE_ID N N
            ------- - -
            Alfred
            Andreas
            Anika
            Armin
            Axel
            Ben
            Bill
            Boris
            Bruno
            Claudia
            Clemens
            
            11 rows selected.
            
            Links
            
            LI NODE_ID NODE_ID LINK_INFO
            -- ------- ------- ------------------------------
            1  Alfred  Anika   1
            10 Ben     Bruno   10
            11 Claudia Clemens 11
            2  Anika   Andreas 2
            3  Andreas Armin   3
            4  Armin   Axel    4
            5  Axel    Anika   5
            6  Armin   Anika   6
            7  Bill    Boris   7
            8  Ben     Boris   8
            9  Ben     Bill    9
            
            11 rows selected.
            
            Model, solution only
            
             Network Node
            -------- ----------
                   1 Alfred
                     Andreas
                     Anika
                     Armin
                     Axel
                   2 Ben
                     Bill
                     Boris
                     Bruno
                   3 Claudia
                     Clemens
            
            11 rows selected.
            
            Elapsed: 00:00:00.35
            Model/RSF - all networks, pruned, formatted
            
            Node (* denotes repeat)                            ...via link (summary for root)
            -------------------------------------------------- --------------------------------------------------
            Alfred                                             ***[Network 1: 6 links, 5 nodes]***-
             T Anika                                           1-
              T Andreas                                        2-
               T Armin                                         3-
                T Anika*                                       6-
                T Axel                                         4-
                 T Anika*                                      5-
            Ben                                                ***[Network 2: 4 links, 4 nodes]***-
             T Bill                                            9-
              T Boris                                          7-
               F Ben*                                          8-
             T Bruno                                           10-
            Claudia                                            ***[Network 3: 1 links, 2 nodes]***-
             T Clemens                                         11-
            
            14 rows selected.
            
            Elapsed: 00:00:00.51
            The first query is Model-based and not that easy to read, but I will post if requested.
            • 3. Re: Hierarchical Queries
              980341
              Hi,

              thank you both for your quick reply.

              Oracle Version is 11.2. I'm afraid I can't add any additional columns to the table nor can I use PL/SQL. Brandon, if you could share your generic query that would be highly appreciated.
              Is this a reflexive realtionship?
              No, its not. Yes, it is. (sorry for confusion)


              Thanks
              Björn

              Edited by: 977338 on 17.12.2012 09:55
              • 4. Re: Hierarchical Queries
                Solomon Yakobson
                Use:
                with t as (
                           select  username || sys_connect_by_path(is_friend_of,',') names
                             from  users
                             where connect_by_isleaf = 1
                             start with username not in (select is_friend_of from users)
                             connect by nocycle username = prior is_friend_of
                          )
                select  'Circle' || rownum circle,
                        regexp_count(names,',') + 1 number_of_friends,
                        names
                  from  t
                /
                For example:
                with users as (
                               select 'Alfred' username,'Anika' is_friend_of from dual union all
                               select 'Anika','Andreas' from dual union all
                               select 'Andreas','Armin' from dual union all
                               select 'Armin','Axel' from dual union all
                               select 'Axel','Anika' from dual union all
                               select 'Armin','Anika' from dual union all
                               select 'Bill','Boris' from dual union all
                               select 'Ben','Boris' from dual union all
                               select 'Ben','Bill' from dual union all
                               select 'Ben','Bruno' from dual union all
                               select 'Claudia','Clemens' from dual
                              ),
                         t as (
                               select  username || sys_connect_by_path(is_friend_of,',') names
                                 from  users
                                 where connect_by_isleaf = 1
                                 start with username not in (select is_friend_of from users)
                                 connect by nocycle username = prior is_friend_of
                              )
                select  'Circle' || rownum circle,
                        regexp_count(names,',') + 1 number_of_friends,
                        names
                  from  t
                /
                
                CIRCLE     NUMBER_OF_FRIENDS NAMES
                ---------- ----------------- ----------------------------------------
                Circle1                    5 Armin,Anika,Andreas,Armin,Axel
                Circle2                    3 Bill,Bill,Boris
                Circle3                    2 Ben,Boris
                Circle4                    2 Ben,Bruno
                Circle5                    2 Claudia,Clemens
                
                SQL> 
                And couple of options if you are not on 11g:
                SQL> with users as (
                  2                 select 'Alfred' username,'Anika' is_friend_of from dual union all
                  3                 select 'Anika','Andreas' from dual union all
                  4                 select 'Andreas','Armin' from dual union all
                  5                 select 'Armin','Axel' from dual union all
                  6                 select 'Axel','Anika' from dual union all
                  7                 select 'Armin','Anika' from dual union all
                  8                 select 'Bill','Boris' from dual union all
                  9                 select 'Ben','Boris' from dual union all
                 10                 select 'Ben','Bill' from dual union all
                 11                 select 'Ben','Bruno' from dual union all
                 12                 select 'Claudia','Clemens' from dual
                 13                ),
                 14           t as (
                 15                 select  username || sys_connect_by_path(is_friend_of,',') names
                 16                   from  users
                 17                   where connect_by_isleaf = 1
                 18                   start with username not in (select is_friend_of from users)
                 19                   connect by nocycle username = prior is_friend_of
                 20                )
                 21  select  'Circle' || rownum circle,
                 22          length(regexp_replace(names,'[^,]')) + 1 number_of_friends,
                 23          names
                 24    from  t
                 25  /
                
                CIRCLE     NUMBER_OF_FRIENDS NAMES
                ---------- ----------------- ----------------------------------------
                Circle1                    5 Armin,Anika,Andreas,Armin,Axel
                Circle2                    3 Bill,Bill,Boris
                Circle3                    2 Ben,Boris
                Circle4                    2 Ben,Bruno
                Circle5                    2 Claudia,Clemens
                
                SQL> with users as (
                  2                 select 'Alfred' username,'Anika' is_friend_of from dual union all
                  3                 select 'Anika','Andreas' from dual union all
                  4                 select 'Andreas','Armin' from dual union all
                  5                 select 'Armin','Axel' from dual union all
                  6                 select 'Axel','Anika' from dual union all
                  7                 select 'Armin','Anika' from dual union all
                  8                 select 'Bill','Boris' from dual union all
                  9                 select 'Ben','Boris' from dual union all
                 10                 select 'Ben','Bill' from dual union all
                 11                 select 'Ben','Bruno' from dual union all
                 12                 select 'Claudia','Clemens' from dual
                 13                ),
                 14           t as (
                 15                 select  username || sys_connect_by_path(is_friend_of,',') names
                 16                   from  users
                 17                   where connect_by_isleaf = 1
                 18                   start with username not in (select is_friend_of from users)
                 19                   connect by nocycle username = prior is_friend_of
                 20                )
                 21  select  'Circle' || rownum circle,
                 22          length(names) - length(replace(names,',')) + 1 number_of_friends,
                 23          names
                 24    from  t
                 25  /
                
                CIRCLE     NUMBER_OF_FRIENDS NAMES
                ---------- ----------------- ----------------------------------------
                Circle1                    5 Armin,Anika,Andreas,Armin,Axel
                Circle2                    3 Bill,Bill,Boris
                Circle3                    2 Ben,Boris
                Circle4                    2 Ben,Bruno
                Circle5                    2 Claudia,Clemens
                
                SQL> 
                SY.
                • 5. Re: Hierarchical Queries
                  980341
                  Hi, unfortunately this is not the expected result. Bill, Ben, Boris and Bruno should be in the same circle.
                   CIRCLE     NUMBER_OF_FRIENDS NAMES
                   ---------- ----------------- ----------------------------------------
                   Circle1                    5 Armin,Anika,Andreas,Armin,Axel
                   Circle2                    3 Bill,Bill,Boris
                   Circle3                    2 Ben,Boris
                   Circle4                    2 Ben,Bruno
                   Circle5                    2 Claudia,Clemens
                   
                  
                   
                  Thanks
                  Björn

                  Edited by: 977338 on 17.12.2012 09:53
                  • 6. Re: Hierarchical Queries
                    BrendanP
                    I was looking for a way of avoiding the inefficiency of earlier tree-walking solutions, which started from all nodes, and also navigated many routes through the network, and this is what I came up with:
                    WITH lnk_iter AS (
                    SELECT *
                      FROM links_v
                     CROSS JOIN (SELECT 0 iter FROM DUAL UNION SELECT 1 FROM DUAL)
                    ), mod AS (
                    SELECT *
                      FROM lnk_iter
                    MODEL
                      DIMENSION BY (Row_Number() OVER (PARTITION BY iter ORDER BY link_id) rn, iter)
                      MEASURES (Row_Number() OVER (PARTITION BY iter ORDER BY link_id) id_rn, link_id id,
                            node_id_fr nd1, node_id_to nd2,
                            1 lnk_cur,
                            CAST ('x' AS VARCHAR2(100)) nd1_cur,
                            CAST ('x' AS VARCHAR2(100)) nd2_cur,
                            0 net_cur,
                            CAST (NULL AS NUMBER) net,
                            CAST (NULL AS NUMBER) lnk_prc,
                            1 not_done,
                            0 itnum)
                      RULES UPSERT ALL
                      ITERATE(200) UNTIL (lnk_cur[1, Mod (iteration_number+1, 2)] IS NULL)
                      (
                        itnum[ANY, ANY] = iteration_number,
                        not_done[ANY, Mod (iteration_number+1, 2)] = Count (CASE WHEN net IS NULL THEN 1 END)[ANY, Mod (iteration_number, 2)],
                        lnk_cur[ANY, Mod (iteration_number+1, 2)] = 
                            CASE WHEN not_done[CV(), Mod (iteration_number+1, 2)] > 0 THEN 
                                   Nvl (Min (CASE WHEN lnk_prc IS NULL AND net = net_cur THEN id_rn END)[ANY, Mod (iteration_number, 2)],
                                        Min (CASE WHEN net IS NULL THEN id_rn END)[ANY, Mod (iteration_number, 2)])
                            END,
                        lnk_prc[ANY, Mod (iteration_number+1, 2)] = lnk_prc[CV(), Mod (iteration_number, 2)],
                        lnk_prc[lnk_cur[1, Mod (iteration_number+1, 2)], Mod (iteration_number+1, 2)] = 1,
                        net_cur[ANY, Mod (iteration_number+1, 2)] = 
                            CASE WHEN Min (CASE WHEN lnk_prc IS NULL AND net = net_cur THEN id_rn END)[ANY, Mod (iteration_number, 2)] IS NULL THEN
                                   net_cur[CV(), Mod (iteration_number, 2)] + 1 
                                 ELSE 
                                   net_cur[CV(), Mod (iteration_number, 2)] 
                            END,
                        nd1_cur[ANY, Mod (iteration_number+1, 2)] = nd1[lnk_cur[CV(), Mod (iteration_number+1, 2)], Mod (iteration_number, 2)],
                        nd2_cur[ANY, Mod (iteration_number+1, 2)] = nd2[lnk_cur[CV(), Mod (iteration_number+1, 2)], Mod (iteration_number, 2)],
                        net[ANY, Mod (iteration_number+1, 2)] = 
                            CASE WHEN (nd1[CV(),Mod (iteration_number+1, 2)] IN (nd1_cur[CV(),Mod (iteration_number+1, 2)], nd2_cur[CV(),Mod (iteration_number+1, 2)]) OR 
                                       nd2[CV(),Mod (iteration_number+1, 2)] IN (nd1_cur[CV(),Mod (iteration_number+1, 2)], nd2_cur[CV(),Mod (iteration_number+1, 2)]))
                                       AND net[CV(),Mod (iteration_number, 2)] IS NULL THEN
                                   net_cur[CV(),Mod (iteration_number+1, 2)]
                                 ELSE
                                   net[CV(),Mod (iteration_number, 2)]
                            END
                      ) 
                    )
                    SELECT net "Network", nd1 "Node"
                      FROM mod
                     WHERE not_done = 0
                     UNION
                    SELECT net, nd2
                      FROM mod
                     WHERE not_done = 0
                    ORDER by 1, 2
                    • 7. Re: Hierarchical Queries
                      980341
                      Thank you!
                      How do the views nodes_v and links_v need to look like?
                      • 8. Re: Hierarchical Queries
                        Solomon Yakobson
                        977338 wrote:
                        Hi, unfortunately this is not the expected result. Bill, Ben, Boris and Bruno should be in the same circle.
                        Yeap, it is more complex than I initially thought. Hope this works:
                        create or replace
                          type NameList
                            as table of varchar2(20)
                        /
                        
                        Type created.
                        
                        SQL> 
                        Now:
                        with users as (
                                       select 'Alfred' username,'Anika' is_friend_of from dual union all
                                       select 'Anika','Andreas' from dual union all
                                       select 'Andreas','Armin' from dual union all
                                       select 'Armin','Axel' from dual union all
                                       select 'Axel','Anika' from dual union all
                                       select 'Armin','Anika' from dual union all
                                       select 'Bill','Boris' from dual union all
                                       select 'Ben','Boris' from dual union all
                                       select 'Ben','Bill' from dual union all
                                       select 'Ben','Bruno' from dual union all
                                       select 'Claudia','Clemens' from dual
                                      ),
                                t1 as (
                                       select  connect_by_root username root,
                                               is_friend_of
                                         from  users
                                         start with username not in (select is_friend_of from users)
                                         connect by nocycle username = prior is_friend_of
                                      ),
                                t2 as (
                                       select  root,
                                               set(NameList(root) multiset union all cast(collect(is_friend_of) as NameList)) names
                                         from  t1
                                         group by root
                                      )
                        select  'Circle' || row_number() over(order by root) circle,
                                 count(*) number_of_friends,
                                 rtrim(xmlagg(xmlelement(e,column_value,',').extract('//text()') order by column_value),',') names
                          from  t2,
                                table(names)
                          group by root
                          order by root
                        /
                        
                        CIRCLE     NUMBER_OF_FRIENDS NAMES
                        ---------- ----------------- --------------------------------
                        Circle1                    5 Alfred,Andreas,Anika,Armin,Axel
                        Circle2                    4 Ben,Bill,Boris,Bruno
                        Circle3                    2 Claudia,Clemens
                        
                        SQL> 
                        SY.
                        • 9. Re: Hierarchical Queries
                          Solomon Yakobson
                          Solution I posted will not work in cases like:
                          with users as (
                                         select 'Alfred' username,'Anika' is_friend_of from dual union all
                                         select 'Anika','Andreas' from dual union all
                                         select 'Andreas','Armin' from dual union all
                                         select 'Armin','Axel' from dual union all
                                         select 'Axel','Anika' from dual union all
                                         select 'Armin','Anika' from dual union all
                                         select 'Bill','Boris' from dual union all
                                         select 'Sam','Bill' from dual union all
                                         select 'Ben','Boris' from dual union all
                                         select 'Ben','Bill' from dual union all
                                         select 'Ben','Bruno' from dual union all
                                         select 'Claudia','Clemens' from dual
                                        ),
                                  t1 as (
                                         select  connect_by_root username root,
                                                 is_friend_of
                                           from  users
                                           start with username not in (select is_friend_of from users)
                                           connect by nocycle username = prior is_friend_of
                                        ),
                                  t2 as (
                                         select  root,
                                                 set(NameList(root) multiset union all cast(collect(is_friend_of) as NameList)) names
                                           from  t1
                                           group by root
                                        )
                          select  'Circle' || row_number() over(order by root) circle,
                                   count(*) number_of_friends,
                                   rtrim(xmlagg(xmlelement(e,column_value,',').extract('//text()') order by column_value),',') names
                            from  t2,
                                  table(names)
                            group by root
                            order by root
                          /
                          
                          CIRCLE     NUMBER_OF_FRIENDS NAMES
                          ---------- ----------------- -------------------------------
                          Circle1                    5 Alfred,Andreas,Anika,Armin,Axel
                          Circle2                    4 Ben,Bill,Boris,Bruno
                          Circle3                    2 Claudia,Clemens
                          Circle4                    3 Bill,Boris,Sam
                          
                          SQL> 
                          SY.

                          Edited by: Solomon Yakobson on Dec 17, 2012 2:14 PM
                          • 10. Re: Hierarchical Queries
                            Solomon Yakobson
                            OK. I think I finally got it:
                            with users as (
                                           select 'Alfred' username,'Anika' is_friend_of from dual union all
                                           select 'Anika','Andreas' from dual union all
                                           select 'Andreas','Armin' from dual union all
                                           select 'Armin','Axel' from dual union all
                                           select 'Axel','Anika' from dual union all
                                           select 'Armin','Anika' from dual union all
                                           select 'Bill','Boris' from dual union all
                                           select 'Ben','Boris' from dual union all
                                           select 'Ben','Bill' from dual union all
                                           select 'Ben','Bruno' from dual union all
                                           select 'Claudia','Clemens' from dual
                                          ),
                                    t1 as (
                                           select  connect_by_root username name,
                                                   is_friend_of
                                             from  users
                                             start with username not in (select is_friend_of from users)
                                             connect by nocycle username = prior is_friend_of
                                          ),
                                    t2 as (
                                           select  min(name) over(partition by is_friend_of) root,
                                                   name,
                                                   is_friend_of
                                             from  t1
                                          ),
                                    t3 as (
                                            select  root,
                                                    name
                                              from  t2
                                           union
                                            select  root,
                                                    is_friend_of
                                              from  t2
                                          )
                            select  'Circle' || row_number() over(order by root) circle,
                                     count(*) number_of_friends,
                                     rtrim(xmlagg(xmlelement(e,name,',').extract('//text()') order by name),',') names
                              from  t3
                              group by root
                              order by root
                            /
                            
                            CIRCLE     NUMBER_OF_FRIENDS NAMES
                            ---------- ----------------- --------------------------------
                            Circle1                    5 Alfred,Andreas,Anika,Armin,Axel
                            Circle2                    4 Ben,Bill,Boris,Bruno
                            Circle3                    2 Claudia,Clemens
                            
                            SQL> 
                            And if we add Sam --> Bill:
                            with users as (
                                           select 'Alfred' username,'Anika' is_friend_of from dual union all
                                           select 'Anika','Andreas' from dual union all
                                           select 'Andreas','Armin' from dual union all
                                           select 'Armin','Axel' from dual union all
                                           select 'Axel','Anika' from dual union all
                                           select 'Armin','Anika' from dual union all
                                           select 'Bill','Boris' from dual union all
                                           select 'Sam','Bill' from dual union all
                                           select 'Ben','Boris' from dual union all
                                           select 'Ben','Bill' from dual union all
                                           select 'Ben','Bruno' from dual union all
                                           select 'Claudia','Clemens' from dual
                                          ),
                                    t1 as (
                                           select  connect_by_root username name,
                                                   is_friend_of
                                             from  users
                                             start with username not in (select is_friend_of from users)
                                             connect by nocycle username = prior is_friend_of
                                          ),
                                    t2 as (
                                           select  min(name) over(partition by is_friend_of) root,
                                                   name,
                                                   is_friend_of
                                             from  t1
                                          ),
                                    t3 as (
                                            select  root,
                                                    name
                                              from  t2
                                           union
                                            select  root,
                                                    is_friend_of
                                              from  t2
                                          )
                            select  'Circle' || row_number() over(order by root) circle,
                                     count(*) number_of_friends,
                                     rtrim(xmlagg(xmlelement(e,name,',').extract('//text()') order by name),',') names
                              from  t3
                              group by root
                              order by root
                            /
                            
                            CIRCLE     NUMBER_OF_FRIENDS NAMES
                            ---------- ----------------- -----------------------------------
                            Circle1                    5 Alfred,Andreas,Anika,Armin,Axel
                            Circle2                    5 Ben,Bill,Boris,Bruno,Sam
                            Circle3                    2 Claudia,Clemens
                            
                            SQL> 
                            SY.
                            • 11. Re: Hierarchical Queries
                              Solomon Yakobson
                              Just noticed you are on 11.2. Then use LISTAGG:
                              with users as (
                                             select 'Alfred' username,'Anika' is_friend_of from dual union all
                                             select 'Anika','Andreas' from dual union all
                                             select 'Andreas','Armin' from dual union all
                                             select 'Armin','Axel' from dual union all
                                             select 'Axel','Anika' from dual union all
                                             select 'Armin','Anika' from dual union all
                                             select 'Bill','Boris' from dual union all
                                             select 'Sam','Bill' from dual union all
                                             select 'Ben','Boris' from dual union all
                                             select 'Ben','Bill' from dual union all
                                             select 'Ben','Bruno' from dual union all
                                             select 'Claudia','Clemens' from dual
                                            ),
                                      t1 as (
                                             select  connect_by_root username name,
                                                     is_friend_of
                                               from  users
                                               start with username not in (select is_friend_of from users)
                                               connect by nocycle username = prior is_friend_of
                                            ),
                                      t2 as (
                                             select  min(name) over(partition by is_friend_of) root,
                                                     name,
                                                     is_friend_of
                                               from  t1
                                            ),
                                      t3 as (
                                              select  root,
                                                      name
                                                from  t2
                                             union
                                              select  root,
                                                      is_friend_of
                                                from  t2
                                            )
                              select  'Circle' || row_number() over(order by root) circle,
                                       count(*) number_of_friends,
                                       listagg(name,',') within group(order by name) names
                                from  t3
                                group by root
                                order by root
                              /
                              
                              CIRCLE     NUMBER_OF_FRIENDS NAMES
                              ---------- ----------------- -------------------------------
                              Circle1                    5 Alfred,Andreas,Anika,Armin,Axel
                              Circle2                    5 Ben,Bill,Boris,Bruno,Sam
                              Circle3                    2 Claudia,Clemens
                              
                              SQL> 
                              SY.
                              • 12. Re: Hierarchical Queries
                                Frank Kulash
                                Hi,
                                977338 wrote:
                                ... I'm afraid I can't add any additional columns to the table nor can I use PL/SQL.
                                That's too bad. I think PL/SQL would be a lot faster than CONNECT BY.

                                If you can't add a column to your real table, maybe you could copy the relevant columns to another table, where you could have a column identifying to which circle each row belongs. That is, your table might look like this:
                                CREATE TABLE     users
                                (   username        VARCHAR2 (10)     NOT NULL
                                ,   is_friend_of   VARCHAR2 (10)     NOT NULL
                                ,   circle        NUMBER             DEFAULT 0     NOT NULL
                                );
                                Given these procedures:
                                CREATE OR REPLACE PROCEDURE     set_circle
                                (       in_rowid     IN     ROWID
                                ,     in_circle     IN     users.circle%TYPE
                                )
                                IS
                                    this_row     users%ROWTYPE;
                                BEGIN
                                    SELECT  *
                                    INTO    this_row
                                    FROM    users
                                    WHERE   ROWID     = in_rowid;
                                
                                    IF  this_row.circle = 0
                                    THEN
                                     -- mark this row as belonging to in_circle
                                        UPDATE  users
                                     SET     circle     = in_circle
                                     WHERE     ROWID     = in_rowid;
                                
                                     -- mark all adjacent rows as belonging to in_circle, too
                                     FOR neighbor IN (
                                                            SELECT  ROWID     AS r_id
                                                   FROM    users
                                                   WHERE   circle       = 0
                                                   AND         (   username     IN (this_row.username, this_row.is_friend_of)
                                                            OR     is_friend_of IN (this_row.username, this_row.is_friend_of)
                                                        )
                                                        )
                                     LOOP
                                         set_circle (neighbor.r_id, in_circle);
                                     END LOOP;
                                    END IF;
                                END set_circle;
                                /
                                SHOW ERRORS
                                
                                CREATE OR REPLACE PROCEDURE     set_all_circles
                                AS
                                    circle_num     PLS_INTEGER     := 1;
                                    first_rowid     ROWID;
                                BEGIN
                                    UPDATE  users
                                    SET         circle  = 0;
                                
                                    LOOP
                                     SELECT     ROWID
                                     INTO     first_rowid
                                     FROM     users
                                     WHERE     circle     = 0
                                     AND     ROWNUM     = 1;
                                
                                     set_circle (first_rowid, circle_num);
                                     circle_num := circle_num + 1;
                                    END LOOP;
                                EXCEPTION
                                    WHEN  NO_DATA_FOUND
                                    THEN
                                        NULL;
                                END set_all_circles;
                                /
                                SHOW ERRORS
                                You could set the circle column in all rows by:
                                EXEC  set_all_circles;
                                • 13. Re: Hierarchical Queries
                                  980341
                                  Thanks SY, your version works perfectly - since I need that query for an adhoc analysis performance is not really an issue.

                                  To make the problem a little bit more complicated, how would I need to modify your existing query to include information about the countries and salary of the friends / circle.

                                  CREATE TABLE "USERDATA" 
                                     (     "USERNAME" VARCHAR2(50), 
                                       "COUNTRY" VARCHAR2(50), 
                                       "SALARY" NUMBER
                                     )
                                  
                                  Insert into USERDATA (USERNAME,COUNTRY,SALARY) values ('Armin','UK','500');
                                  Insert into USERDATA (USERNAME,COUNTRY,SALARY) values ('Alfred','UK','1000');
                                  Insert into USERDATA (USERNAME,COUNTRY,SALARY) values ('Andreas','Germany','500');
                                  Insert into USERDATA (USERNAME,COUNTRY,SALARY) values ('Anika','Sweden','500');
                                  Insert into USERDATA (USERNAME,COUNTRY,SALARY) values ('Axel','France','500');
                                  Insert into USERDATA (USERNAME,COUNTRY,SALARY) values ('Ben','USA','500');
                                  Insert into USERDATA (USERNAME,COUNTRY,SALARY) values ('Bill','USA','500');
                                  Insert into USERDATA (USERNAME,COUNTRY,SALARY) values ('Boris','Russia','750');
                                  Insert into USERDATA (USERNAME,COUNTRY,SALARY) values ('Bruno','Austria','250');
                                  Insert into USERDATA (USERNAME,COUNTRY,SALARY) values ('Claudia','Sweden','500');
                                  Insert into USERDATA (USERNAME,COUNTRY,SALARY) values ('Clemens','UK','300');
                                  
                                  
                                  
                                  The result should look like this
                                  
                                  circle    no_of_friends    names                                countries                     salary
                                  --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---  --- --- --- --- --- --- --- --- ---  --- --- 
                                  Circle1              5     Alfred,Andreas,Anika,Armin,Axel      France, Germany, Sweden, UK     3000
                                  ...
                                  • 14. Re: Hierarchical Queries
                                    Frank Kulash
                                    Hi,
                                    977338 wrote:
                                    To make the problem a little bit more complicated, how would I need to modify your existing query to include information about the countries and salary of the friends / circle.
                                    You can join the userdata table in the main query, and add the new columns to the main query's SELECT clause like this:
                                    ...
                                    select  'Circle' || row_number() over(order by t3.root) circle,
                                             count(*) number_of_friends,
                                             rtrim(xmlagg(xmlelement(e, t3.name,',').extract('//text()') order by t3.name),',') names,
                                          RTRIM ( XMLAGG ( XMLELEMENT (e, ud.country, ',').EXTRACT ('//text ()')
                                                             ORDER BY  ud.country
                                                   )
                                                , ','
                                                )          AS countries,
                                          SUM (ud.salary)     AS salary
                                      from  t3
                                      JOIN     userdata  ud  ON  ud.username  = t3.name
                                      group by t3.root
                                      order by t3.root
                                    / 
                                    The subqueries are exactly as Solomon posted them.
                                    1 2 Previous Next