如何用BigQuery(GCP)查询链式关联ID的最终映射值并生成新表

To get the final new_id for each initial old_id in your chain, you can use a recursive CTE (Common Table Expression) in BigQuery. This approach traverses each chain until it reaches the last node, then selects that final value.

Step-by-Step Query #

WITH RECURSIVE id_chain AS (
  -- Base case: Start with initial old_ids that never appear as new_id
  SELECT
    old_id AS initial_old_id,
    old_id,
    new_id,
    1 AS depth
  FROM `your-project.your-dataset.table1`
  WHERE old_id NOT IN (SELECT new_id FROM `your-project.your-dataset.table1`)
  
  UNION ALL
  
  -- Recursive step: Follow the chain to the next linked new_id
  SELECT
    c.initial_old_id,
    t.old_id,
    t.new_id,
    c.depth + 1
  FROM id_chain c
  JOIN `your-project.your-dataset.table1` t
    ON c.new_id = t.old_id
)

-- Pick the final node in each chain (highest depth)
SELECT
  initial_old_id AS old_id,
  new_id
FROM (
  SELECT
    initial_old_id,
    new_id,
    ROW_NUMBER() OVER (PARTITION BY initial_old_id ORDER BY depth DESC) AS rn
  FROM id_chain
)
WHERE rn = 1;

How It Works #

1. Base Case: Identifies the starting points (a1, a2) by selecting old_ids that don't exist in the new_id column (these are the roots of your chains).
2. Recursive Step: Repeatedly joins the CTE with your table to follow each chain, incrementing a depth counter to track how far along the chain we are.
3. Final Selection: Uses ROW_NUMBER() to rank rows by depth (descending) for each initial old_id, then picks the top-ranked row (the last node in the chain).

Notes #

• Replace your-project.your-dataset with your actual GCP project and dataset names.
• If your data contains cycles (e.g., a new_id that points back to an earlier old_id), add a check to avoid infinite loops (e.g., track visited IDs and stop when a duplicate is found).

内容的提问来源于stack exchange，提问作者Saurav O'Lee