Hierarchical Data Query with Level Number

Introduction

In this article, we will explore a common problem in data analysis: handling hierarchical data. Hierarchical data is a type of data where each element has a parent-child relationship. In this case, we are given a table with three columns: GOAL_ID, PARENT_GOAL_ID, and GOAL_NAME. The GOAL_ID column represents the unique identifier for each goal, the PARENT_GOAL_ID column indicates the parent goal of each goal, and the GOAL_NAME column stores the name of each goal.

We need to write a query that will give us the result with the level number prepended to the GOAL_NAME. For example, given the following data:

The desired output would be:

In this article, we will explore a solution using recursive subquery factoring.

What is Recursive Subquery Factoring?

Recursive subquery factoring is a feature introduced in Oracle Database 11g Release 2 (11gR2). It allows us to use a common table expression (CTE) with recursive references to itself. This enables us to solve complex hierarchical problems by breaking them down into smaller, more manageable pieces.

How does it work?

To understand how recursive subquery factoring works, let’s break down the process step-by-step:

1. Anchor: The anchor is the initial set of rows that are used as a starting point for the recursion. In our case, this would be the rows where PARENT_GOAL_ID is null, indicating the top-level goals.
2. Recursive: Once we have the anchor, we use it to build the next level of the hierarchy. We do this by joining the anchor with the original table and adding new levels to the result set based on the parent-child relationships.
3. Recursion Terminates: Recursion terminates when there are no more child records to process.

In our query, we first identify the top-level goals (anchor) using a CTE with a WHERE clause that filters out rows where PARENT_GOAL_ID is null. We then join this anchor CTE with the original table to build the recursive levels.

Recursive Subquery Factoring Query

Here’s the complete query:

with goals (GOAL_ID, PARENT_GOAL_ID, GOAL_NAME) as (
  select 1, null, 'GoalX' from dual
  union all select 2, 1, 'GoalY' from dual
  union all select 3, 1, 'GoalZ' from dual
  union all select 4, 3, 'GoalN' from dual
  union all select 5, null, 'GoalA' from dual
  union all select 6, 5, 'GoalB' from dual
  union all select 7, 6, 'GoalC' from dual
  union all select 8, 6, 'GoalD' from dual
),
rcte (root_id, label, goal_level, goal_id, goal_name) as (
  select goal_id,
    to_char(row_number() over (order by goal_id)),
    1,
    goal_id,
    goal_name
  from goals
  where parent_goal_id is null
  union all
  select r.root_id,
    r.label || '.' || row_number()
      over (partition by r.root_id, r.goal_level order by g.goal_id),
    r.goal_level + 1,
    g.goal_id,
    g.goal_name
  from rcte r
  join goals g on g.parent_goal_id = r.goal_id
)
select label, goal_name
from rcte
order by root_id, goal_level, goal_id;

This query uses a CTE named rcte (recursive table expression) to perform the recursive subquery factoring. The outer query simply selects the desired columns from the rcte CTE.

Result Set

The result set of this query would be:

Conclusion

In this article, we explored a solution to the problem of handling hierarchical data using recursive subquery factoring in Oracle Database. We walked through the process step-by-step and provided an example query that demonstrates how to solve complex hierarchical problems by breaking them down into smaller, more manageable pieces.

By mastering recursive subquery factoring, you can effectively analyze and solve a wide range of hierarchical data problems, from simple to complex. With practice and experience, you’ll become proficient in using this powerful feature to unlock new insights in your data analysis work.

Last modified on 2024-06-28