Identify root node with group by

Question

I need to identify the primary channel (which is highest in hierarchy) with only one set of flag set under it's hierarchy. In below case, US is not a valid primary channel since there are two flags underneath. Only valid primary channels are US1, US2, UK.

I would like to have a solution with single query, if it's possible; otherwise I will explore options with procedure.

I tried few variations with hierarchical query, group by, count(flag), somehow I am unable to eliminate "US" and get the desired result.

I would appreciate, if someone can help me with high level approach for this use case.

Input Data

---------------------------------
channel | flag  | parent channel
---------------------------------
US      |       |               

US1     |       | US        
A1      | yes   | US1       
A2      |       | A1        
A3      |       | A2

US2     |       | US        
B1      |       | US2       
B2      | yes   | B1        
B3      |       | B2        

UK      |       |   
C1      |       | UK        
C2      |       | C1        
C3      | yes   | C2        
---------------------------------

Multiple hierarchies with flag count using the input data

---------------------
channel | flag count 
---------------------
US      | 2     

US1     | 1     
A1      | 1 
A2      | 0      
A3      | 0      

US2     | 1     
B1      | 1      
B2      | 1   
B3      | 0      

UK      | 1      
C1      | 1      
C2      | 1      
C3      | 1   
----------------

Output

---------------------------------
primary channel
---------------------------------
US1
US2
UK
---------------------------------

Why are US1 and US2 "highest in hierarchy" when they both come under US? Eliminating US and including UK seems OK, but I don't understand how US1 and US2 are included int he final result? You have US listed as a root node (no parent) twice, which is also confusing. — Alex Poole, Jul 18 '18 at 16:43
Refer edit, I have added the intermediate result set. You will notice that by using the input data, we can create multiple hierarchies with underneath flag count. Primary channel would be the one with only one flag count and should be highest in hierarchy. And yes removed duplicate US entry. — Vishal, Jul 18 '18 at 16:58

score 1 · Answer 1 · answered Jul 18 '18 at 18:06

This is a bit tricky... unless I'm missing an obvious shortcut of course...

select max(channel) keep (dense_rank last order by lvl) as primary_channel
from (
  select channel, connect_by_root(channel) as root, level lvl,
    count(*) over (partition by channel) as flag_count
  from input_data
  connect by channel = prior parent_channel
  start with flag = 'yes'
)
where flag_count = 1
group by root;

PRIMARY_CHANNEL
---------------
US1
US2
UK

db<>fiddle demo.

The inner query is a fairly simpler hierarchical query, walking up the tree from the three flag = 'yes' rows, except that is also does an anlytic count of the number of times each channel has been seen in that generated hierarchy.

select channel, connect_by_root(channel) as root, level lvl,
  count(*) over (partition by channel) as flag_count
from input_data
connect by channel = prior parent_channel
start with flag = 'yes';

CHANNEL ROOT        LVL FLAG_COUNT
------- ---- ---------- ----------
A1      A1            1          1
B1      B2            2          1
B2      B2            1          1
C1      C3            3          1
C2      C3            2          1
C3      C3            1          1
UK      C3            4          1
US      A1            3          2
US      B2            4          2
US1     A1            2          1
US2     B2            3          1

11 rows selected.

The outer query then eliminates any where the count is more than 1 - i.e. both US rows; and uses another analytic function to find the row with the highest level for each root remaining in the results.

I've assumed US only actually appears once in your input_data; but if it is there twice then this still works, the intermediate count is just 4 instead of 2. You can see that version in this db<>fiddle.

Identify root node with group by

1 Answers1