问BigQuery中的递归/分层查询

EN

DECLARE rows_count, run_away_stop INT64 DEFAULT 0;

CREATE TEMP TABLE initialData AS WITH input AS (
  SELECT 1 Employee_ID,'Joe' Employee_Name,'Worker' Position,11 Line_Manager_ID UNION ALL
  SELECT 2,'James','Worker',11 UNION ALL
  SELECT 3,'Jack','Worker',11 UNION ALL
  SELECT 4,'Jill','Worker',12 UNION ALL
  SELECT 5,'Jan','Worker',12 UNION ALL
  SELECT 6,'Jacquie','Worker',13 UNION ALL
  SELECT 7,'Joaquin','Worker',14 UNION ALL
  SELECT 8,'Jeremy','Worker',14 UNION ALL
  SELECT 9,'Jade','Worker',15 UNION ALL
  SELECT 10,'Jocelyn','Worker',15 UNION ALL
  SELECT 11, 'Bob', 'Store Manager',16 UNION ALL
  SELECT 12, 'Bill', 'Store Manager',16 UNION ALL
  SELECT 13, 'Barb', 'Store Manager',16 UNION ALL
  SELECT 14, 'Ben', 'Store Manager',17 UNION ALL
  SELECT 15, 'Burt', 'Store Manager',17 UNION ALL
  SELECT 16, 'Sally','Group Manager',18 UNION ALL
  SELECT 17, 'Sam','Group Manager',19 UNION ALL
  SELECT 18, 'Anna', 'Ops Manager',20 UNION ALL
  SELECT 19, 'Amy', 'Ops Manager',20 UNION ALL
  SELECT 20, 'Zoe', 'State Manager', NULL 
)
SELECT * FROM input;

CREATE TEMP TABLE ttt AS 
SELECT Line_Manager_ID, ARRAY_AGG(Employee_ID) Reports FROM initialData WHERE NOT Line_Manager_ID IS NULL GROUP BY Line_Manager_ID;

LOOP
  SET (run_away_stop, rows_count) = (SELECT AS STRUCT run_away_stop + 1, COUNT(1) FROM ttt);

  CREATE OR REPLACE TEMP TABLE ttt1 AS
    SELECT Line_Manager_ID, ARRAY(SELECT DISTINCT Employee_ID FROM UNNEST(Reports) Employee_ID ORDER BY Employee_ID DESC) Reports
    FROM (
      SELECT Line_Manager_ID, ARRAY_CONCAT_AGG(Reports) Reports    
      FROM (
        SELECT t2.Line_Manager_ID, ARRAY_CONCAT(t1.Reports, t2.Reports) Reports 
        FROM ttt t1, ttt t2 
        WHERE (SELECT COUNTIF(t1.Line_Manager_ID = Employee_ID) FROM UNNEST(t2.Reports) Employee_ID) > 0
      ) GROUP BY Line_Manager_ID
    );

  CREATE OR REPLACE TEMP TABLE ttt AS
    SELECT * FROM ttt1 UNION ALL
    SELECT * FROM ttt WHERE NOT Line_Manager_ID IN (SELECT Line_Manager_ID FROM ttt1);

  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 Employee_ID, 
  (
    SELECT STRING_AGG(CAST(Employee_ID AS STRING), ',' ORDER BY Employee_ID DESC)
    FROM ttt.Reports Employee_ID
  ) Reports_as_list
FROM (SELECT DISTINCT Employee_ID FROM initialData) d
LEFT JOIN ttt ON Employee_ID = Line_Manager_ID
ORDER BY Employee_ID DESC;  

Row Employee_ID Reports_as_list  
1   20          19,18,17,16,15,14,13,12,11,10,9,8,7,6,5,4,3,2,1  
2   19          17,15,14,10,9,8,7    
3   18          16,13,12,11,6,5,4,3,2,1  
4   17          15,14,10,9,8,7   
5   16          13,12,11,6,5,4,3,2,1     
6   15          10,9     
7   14          8,7  
8   13          6    
9   12          5,4  
10  11          3,2,1    
11  10          null     
12  9           null     
13  8           null     
14  7           null     
15  6           null     
16  5           null     
17  4           null     
18  3           null     
19  2           null     
20  1           null    

SELECT Employee_ID, Reports Reports_as_array
FROM (SELECT DISTINCT Employee_ID FROM initialData) d
LEFT JOIN ttt ON Employee_ID = Line_Manager_ID
ORDER BY Employee_ID DESC;  

with recursive iterations as (
  select line_manager_id, employee_id, 1 pos from your_table
  union all 
  select b.line_manager_id, a.employee_id, pos + 1 
  from your_table a join iterations b
  on b.employee_id = a.line_manager_id 
)
select line_manager_id, string_agg('' || employee_id order by pos, employee_id desc) as reports_as_list
from iterations
where not line_manager_id is null
group by line_manager_id
order by line_manager_id desc 

CREATE TEMP TABLE planets AS SELECT 'YOU' planet;

LOOP
  SET steps = steps+1
  ;
  CREATE OR REPLACE TEMP TABLE planets AS
  SELECT DISTINCT planet
  FROM (
    SELECT origin planet FROM t1 WHERE dest IN (SELECT planet FROM planets)
    UNION ALL
    SELECT dest planet FROM t1 WHERE origin IN (SELECT planet FROM planets)
  )
  ;
  IF 'SAN' IN (SELECT * FROM planets )
      THEN LEAVE;
  END IF;
END LOOP
;
SELECT steps-2