下面的示例说明了如何在这种情况下使用BigQuery脚本
DECLARE rows_count, run_away_stop INT64 DEFAULT 0;CREATE TEMP TABLE input AS SELECt 1 child_id, 2 parent_id UNIOn ALL SELECT 2, 3 UNIOn ALL SELECT 3, NULL UNIOn ALL SELECT 4, 5 UNIOn ALL SELECT 5, 6 UNIOn ALL SELECT 6, NULL UNIOn ALL SELECT 7, 8 ;CREATE TEMP TABLE ttt AS SELECT ARRAY(SELECT val FROM UNNEST([child_id, IFNULL(parent_id, child_id)]) val ORDER BY val ) arr FROM input;LOOP SET (run_away_stop, rows_count) = (SELECt AS STRUCT run_away_stop + 1, COUNT(1) FROM ttt); CREATE OR REPLACe TEMP TABLE ttt AS SELECT ANY_VALUE(arr) arr FROM ( SELECt ARRAY(SELECT DISTINCT val FROM UNNEST(arr) val ORDER BY val) arr FROM ( SELECt ANY_VALUE(arr1) arr1, ARRAY_CONCAT_AGG(arr) arr FROM ( SELECt t1.arr arr1, t2.arr arr2, ARRAY(SELECT DISTINCT val FROM UNNEST(ARRAY_CONCAt( t1.arr, t2.arr)) val ORDER BY val) arr FROM ttt t1, ttt t2 WHERe (SELECt COUNT(1) FROM UNNEST(t1.arr) val JOIN UNNEST(t2.arr) val USING(val)) > 0 ) GROUP BY FORMAT('%t', arr1) ) ) GROUP BY FORMAT('%t', arr); IF (rows_count = (SELECt COUNT(1) FROM ttt) AND run_away_stop > 1) OR run_away_stop > 10 THEN BREAK; END IF;END LOOP;SELECt input.*, branch_idFROM input JOIN ( SELECt ROW_NUMBER() OVER() AS branch_id, arr AS IDs FROM ttt)ON child_id IN UNNEST(IDs)最终输出
Row child_id parent_id branch_id 1 781 2 452 3 6null 2 4 562 5 3null 3 6 233 7 123
