Hierarchical Query - Counting records that belongs to Parent and Child Places

user_7686240 0
Viewed 128

I have two tables PLACE and ADRESS. PLACE includes places like parent-child hierarcy. ADRESS includes adresses with PLACE_ID column. These tables as shown below;

PLACE

ID      PARENT_ID       NAME        CONTINENT   
11      null            USA         America
22      11              New York    America
33      22              Manhattan   America
44      null            Brasil      America
55      44              Rio         America
66      null            France      Europe
77      66              Paris       Europe  
88      66              Nice        Europe



MEMBER  

ID      PLACE_ID    NAME    ADRESS
1       22          ..      ..
2       77          ..      ..
3       33          ..      ..
4       22          ..      ..
5       55          ..      ..
6       55          ..      ..
7       88          ..      ..
8       88          ..      ..
9       88          ..      ..
10      22          ..      ..



Expected Result:

ID      PARENT_ID       MEMBER_COUNT    PLACE_NAME      CONTITNET       
11      null                4           USA             America
22      11                  4           New York        America
33      22                  1           Manhattan       America
44      null                2           Brasil          America
55      44                  2           Rio             America
66      null                4           France          Europe
77      66                  1           Paris           Europe  
88      66                  3           Nice            Europe

I want to find out how many members are in which place. I couldn't add child places's member count to parent place. My query as shown below;

WITH MEMBER_COUNT_BY_PLACE AS (
    SELECT  P.PLACE_ID, COUNT(P.ID) AS MEMBER_COUNT
    FROM MEMBER P
    GROUP BY P.PLACE_ID
) SELECT C.ID,  C.NAME, C.PARENT_ID AS PARENTID, C.CONTINENT, SUM(NVL(D.MEMBER_COUNT, 0)) AS MEMBER_COUNT
  FROM PLACE C LEFT JOIN MEMBER_COUNT_BY_PLACE D ON D.PLACE_ID = C.ID
  START WITH D.PLACE_ID IS NOT NULL
  CONNECT BY PRIOR C.PARENT_ID = C.ID
  GROUP BY C.ID, C.NAME, C.PARENT_ID, C.CONTINENT
  ORDER BY CONTINENT ASC, PARENT_ID ASC NULLS FIRST;

Thank you for your help.

hierarchical-data oracle sql
2 Answers

Here's one option:

Sample data first:

SQL> with
  2  -- sample data
  3  place (id, parent_id, name, continent) as
  4    (select 11, null, 'USA'      , 'America' from dual union all
  5     select 22, 11  , 'New York' , 'America' from dual union all
  6     select 33, 22  , 'Manhattan', 'America' from dual union all
  7     select 44, null, 'Brasil'   , 'America' from dual union all
  8     select 55, 44  , 'Rio'      , 'America' from dual union all
  9     select 66, null, 'France'   , 'Europe'  from dual union all
 10     select 77, 66  , 'Paris'    , 'Europe'  from dual union all
 11     select 88, 66  , 'Nice'     , 'Europe'  from dual
 12    ),
 13  member (id, place_id) as
 14    (select  1, 22 from dual union all
 15     select  2, 77 from dual union all
 16     select  3, 33 from dual union all
 17     select  4, 22 from dual union all
 18     select  5, 55 from dual union all
 19     select  6, 55 from dual union all
 20     select  7, 88 from dual union all
 21     select  8, 88 from dual union all
 22     select  9, 88 from dual union all
 23     select 10, 22 from dual
 24    ),

Then, a few CTEs (see comments):

 25  -- naively, count members of leaf nodes
 26  naive as
 27    (select m.place_id, count(*) cnt
 28     from member m
 29     group by m.place_id
 30    ),
 31  -- set root parent node to each row
 32  cbr as
 33    (select connect_by_root p.id as tpid,
 34       p.id, p.parent_id, n.cnt
 35     from place p left join naive n on p.id = n.place_id
 36     connect by prior p.id = p.parent_id
 37     start with p.parent_id is null
 38     order by p.id
 39    ),
 40  -- how many members does each root node have?
 41  sumtpid as
 42    (select c.tpid, sum(c.cnt) cnt
 43     from cbr c
 44     group by c.tpid
 45    )
 46  -- join CBR + SUMTPID + PLACE for the final result
 47  select c.id, c.parent_id, nvl(c.cnt, s.cnt) member_count,
 48    p.name place_name,
 49    p.continent
 50  from cbr c join sumtpid s on s.tpid = c.tpid
 51             join place p on p.id = c.id
 52  order by c.id;

Which results in:

        ID  PARENT_ID MEMBER_COUNT PLACE_NAM CONTINE
---------- ---------- ------------ --------- -------
        11                       4 USA       America
        22         11            3 New York  America
        33         22            1 Manhattan America
        44                       2 Brasil    America
        55         44            2 Rio       America
        66                       4 France    Europe
        77         66            1 Paris     Europe
        88         66            3 Nice      Europe

8 rows selected.

SQL>
user_90979060

This query also aggregates child members:

with mcnt (id, parent_id, name, continent, child_members, own_members, ff,level_members) as (
   select 
          p.id, p.parent_id, p.name, p.continent
        , 0 as child_members
        , (select count(*) from member m where m.place_id=p.id) as own_members
        , row_number()over(partition by p.parent_id order by p.id) ff
        , sum((select count(*) from member m where m.place_id=p.id))over(partition by p.parent_id) level_members
   from place p
   where not exists(select null from place child where p.id = child.parent_id)
   union all
   select p.id, p.parent_id, p.name, p.continent
        , mcnt.level_members + mcnt.child_members as child_members
        , (select count(*) from member m where m.place_id=p.id) as own_members
        , row_number()over(partition by p.parent_id order by p.id) ff
        , sum((select count(*) from member m where m.place_id=p.id))over(partition by p.parent_id) level_members
   from mcnt, place p
   where mcnt.parent_id=p.id
     and mcnt.ff=1
)
select
       id, parent_id, name, continent, child_members, own_members
      ,child_members+own_members as total_cnt
from mcnt
order by id;

Full example:

with
-- sample data
place (id, parent_id, name, continent) as
  (select 11, null, 'USA'      , 'America' from dual union all
   select 22, 11  , 'New York' , 'America' from dual union all
   select 33, 22  , 'Manhattan', 'America' from dual union all
   --
  select 35, 33  , 'Central Park', 'America' from dual union all
   select 44, null, 'Brasil'   , 'America' from dual union all
   select 55, 44  , 'Rio'      , 'America' from dual union all
   select 66, null, 'France'   , 'Europe'  from dual union all
   select 77, 66  , 'Paris'    , 'Europe'  from dual union all
   select 88, 66  , 'Nice'     , 'Europe'  from dual
  ),
member (id, place_id) as
  (select  1, 22 from dual union all
   select  2, 77 from dual union all
   select  3, 33 from dual union all
   select  4, 22 from dual union all
   select  5, 55 from dual union all
   select  6, 55 from dual union all
   select  7, 88 from dual union all
   select  8, 88 from dual union all
   select  9, 88 from dual union all
   select 10, 22 from dual
   --
   union all select 1001, 35 from dual
   union all select 1002, 35 from dual
   union all select 1003, 35 from dual
  ),
mcnt (id, parent_id, name, continent, child_members, own_members, ff,level_members) as (
   select 
      p.id, p.parent_id, p.name, p.continent
    , 0 as child_members
    , (select count(*) from member m where m.place_id=p.id) as own_members
    , row_number()over(partition by p.parent_id order by p.id) ff
    , sum((select count(*) from member m where m.place_id=p.id))over(partition by p.parent_id) level_members
   from place p
   where not exists(select null from place child where p.id = child.parent_id)
   union all
   select p.id, p.parent_id, p.name, p.continent
    , mcnt.level_members + mcnt.child_members as child_members
    , (select count(*) from member m where m.place_id=p.id) as own_members
    , row_number()over(partition by p.parent_id order by p.id) ff
    , sum((select count(*) from member m where m.place_id=p.id))over(partition by p.parent_id) level_members
   from mcnt, place p
   where mcnt.parent_id=p.id
 and mcnt.ff=1
)
select
   id, parent_id, name, continent, child_members, own_members
  ,child_members+own_members as total_cnt
from mcnt
order by id;

Results:

        ID  PARENT_ID NAME         CONTINE CHILD_MEMBERS OWN_MEMBERS  TOTAL_CNT
---------- ---------- ------------ ------- ------------- ----------- ----------
        11            USA          America             7           0          7
        22         11 New York     America             4           3          7
        33         22 Manhattan    America             3           1          4
        35         33 Central Park America             0           3          3
        44            Brasil       America             2           0          2
        55         44 Rio          America             0           2          2
        66            France       Europe              4           0          4
        77         66 Paris        Europe              0           1          1
        88         66 Nice         Europe              0           3          3

9 rows selected.
user_4291000
Related