时间戳

首页 > 解决方案 > 在 bigquery 中合并具有一个共同值的数组

sql - 在 bigquery 中合并具有一个共同值的数组

问题描述

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

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

现在我想要的输出是：

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

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

我有什么办法可以做到吗？

谢谢

标签： sqlarraysgoogle-bigquery

解决方案

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

幸运的是，最近引入的脚本功能允许在 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（见循环中的最后一条语句）

注意：上面很可能可以优化 - 由您决定

推荐阅读