sql - 合并bigquery中具有一个公共(public)值的数组

Question

在大查询中，我有一个表，其中一列是一个字符串数组。数据将如下所示:

['a','b']
['b','c']
['c', 'd']
['e']

现在我想要的输出是:

['a','b','c','d']
['e']

基本上我想合并至少有一个共同值的所有数组。

有什么办法可以做到这一点吗？

谢谢

Answer 1

通常这种类型的逻辑是使用所谓的递归 CTE 来实现的，但 BigQuery 不支持此类!

幸运的是，最近推出了scripting功能允许在 BigQuery 中实现此功能

下面是 BigQuery 标准 SQL

DECLARE rows_count, run_away_stop INT64 DEFAULT 0;

CREATE TEMP TABLE ttt AS WITH input AS (
  SELECT ['a', 'b'] arr UNION ALL
  SELECT ['b', 'c'] UNION ALL
  SELECT ['c', 'd'] UNION ALL
  SELECT ['x', 'y'] UNION ALL
  SELECT ['y', 'a'] UNION ALL
  SELECT ['e'] 
)
SELECT ARRAY(SELECT val FROM UNNEST(arr) val ORDER BY val ) arr FROM input;

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

  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 ARRAY_TO_STRING(arr, ',') arr FROM ttt;    

最终输出

Row arr  
1   a,b,c,d,x,y  
2   e    

上面进行了 3 次迭代。在现实生活中的示例中，显然需要更多 - 因此您需要调整允许的最大迭代次数 - 目前为 10(请参阅循环中的最后一条语句)

注意:上面的内容很可能可以优化 - 由您决定