BigQuery递归CTE受限下的员工层级表重构求助

Got it, let's work through this hierarchy transformation problem. Since you noted BigQuery's recursive CTE support is limited, we can use array handling and conditional logic to get exactly the output you're aiming for. Here's a step-by-step solution:

Step 1: Extract Unique, Ordered Hierarchy Paths #

First, we'll create a CTE to pull out the unique chain of command for each employee (starting from the employee themselves, up to the top-level manager). We'll break down the wide columns into rows, deduplicate entries, and sort them to preserve the correct hierarchy order:

WITH unique_hierarchy AS (
  SELECT
    Employee0,
    ARRAY(
      SELECT DISTINCT val
      FROM UNNEST([Employee0, Employee1, Employee2, Employee3, Employee4]) val
      ORDER BY 
        CASE 
          WHEN val = Employee0 THEN 1   -- First: the employee themselves
          WHEN val = Employee1 THEN 2   -- Second: their direct manager
          WHEN val = Employee2 THEN 3   -- Third: next level up
          WHEN val = Employee3 THEN 4
          WHEN val = Employee4 THEN 5
        END
    ) AS hierarchy_array
  FROM your_table_name  -- Replace this with your actual table name
)

Step 2: Map Paths to Your Target Columns #

Next, we'll take each unique hierarchy array and map its elements to the correct columns in your desired output. We use the length of the array to determine where to place each value, filling NULLs in the leading columns so the top-level manager always lands in Employee4:

SELECT
  -- Employee0: Only filled if there are 5 unique hierarchy levels (not applicable here)
  CASE WHEN ARRAY_LENGTH(hierarchy_array) >= 5 THEN hierarchy_array[OFFSET(ARRAY_LENGTH(hierarchy_array) - 5)] ELSE NULL END AS Employee0,
  -- Employee1: Only filled if there are 4+ unique levels
  CASE WHEN ARRAY_LENGTH(hierarchy_array) >= 4 THEN hierarchy_array[OFFSET(ARRAY_LENGTH(hierarchy_array) - 4)] ELSE NULL END AS Employee1,
  -- Employee2: Filled for employees with 3 levels (like C)
  CASE WHEN ARRAY_LENGTH(hierarchy_array) >= 3 THEN hierarchy_array[OFFSET(ARRAY_LENGTH(hierarchy_array) - 3)] ELSE NULL END AS Employee2,
  -- Employee3: Filled for employees with 2 levels (like A)
  CASE WHEN ARRAY_LENGTH(hierarchy_array) >= 2 THEN hierarchy_array[OFFSET(ARRAY_LENGTH(hierarchy_array) - 2)] ELSE NULL END AS Employee3,
  -- Employee4: Always filled with the top-level manager
  hierarchy_array[OFFSET(ARRAY_LENGTH(hierarchy_array) - 1)] AS Employee4
FROM unique_hierarchy
-- Optional: Order results to match your sample output
ORDER BY Employee4 DESC, Employee3 NULLS LAST, Employee2 NULLS LAST;

Let's Verify the Output #

• For B: The hierarchy array is [B] (length 1). Only Employee4 gets B, all other columns are NULL.
• For A: The hierarchy array is [A, B] (length 2). Employee3 is A, Employee4 is B, leading columns are NULL.
• For C: The hierarchy array is [C, A, B] (length 3). Employee2 is C, Employee3 is A, Employee4 is B, leading columns are NULL.

This matches exactly the transformed table you provided!

内容的提问来源于stack exchange，提问作者Sandy