BigQuery 中不同元素的数组连接 [英] Array concatenation with distinct elements in BigQuery

问题描述

假设在每一行中我有一个 id 和两个数组 array_1 和 array_2，如下所示

Let's say in each row I have an id and two arrays array_1 and array_2 that looks like following

SELECT 'a' id, [1,2,3,4,5] array_1, [2,2,2,3,6] array_2 UNION ALL
SELECT 'b', [2,3,4,5,6], [7,7,8,6,9] UNION ALL
SELECT 'c', [], [1,4,5]

我想连接这两个数组，只保留新数组中的唯一元素.我想要的输出如下所示

I want concatenate these two arrays and only keep the unique elements in the new array. My desired output would look like the following

+----+-----------+-----------+-----------------------------+
| id |  array_1  |  array_2  | concatenated_array_distinct |
+----+-----------+-----------+-----------------------------+
| a  | 1,2,3,4,5 | 2,2,2,3,6 |                 1,2,3,4,5,6 |
| b  | 2,3,4,5,6 | 7,7,8,6,9 |             2,3,4,5,6,7,8,9 |
| c  |           |     1,4,5 |                       1,4,5 |
+----+-----------+-----------+-----------------------------+

我试图使用 array_concat 函数，但我找不到使用 array_concat 函数保留不同元素的方法.

I was trying to use array_concat function but I could not find a way to keep distinct elements using the array_concat function.

无论如何我可以获得所需的输出?

Is there anyway I can get the desired output?

推荐答案

以下为 BigQuery Standard SQL

Below is for BigQuery Standard SQL

... 我试图使用 array_concat 函数，但我找不到使用 array_concat 函数保留不同元素的方法....

... I was trying to use array_concat function but I could not find a way to keep distinct elements using the array_concat function. ...

你走对了:o)

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'a' id, [1,2,3,4,5] array_1, [2,2,2,3,6] array_2 UNION ALL
  SELECT 'b', [2,3,4,5,6], [7,7,8,6,9] UNION ALL
  SELECT 'c', [], [1,4,5]
)
SELECT *, 
  ARRAY(SELECT DISTINCT x 
    FROM UNNEST(ARRAY_CONCAT(array_1, array_2)) x 
    ORDER BY x
  ) concatenated_array_distinct
FROM `project.dataset.table`  

这篇关于BigQuery 中不同元素的数组连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！