sql - 如何在 BigQuery 中获取两个数组的交集

Question

我有这样的数据:

id  col1     col2
-----------------
1   [1,2]    [2,3]
2   [4,4,6]  [6,7]

我想要这样的数据:

id  col3
---------
1   [2]
2   [6]

有什么聪明的解决方案吗？

Answer 1

您可以使用 INTERSECT DISTINCT

-- build example table
WITH example as (
  SELECT
  * FROM UNNEST([
      STRUCT([1,2] as col1, [2,3] as col2),
      STRUCT([4,4,6],[6,7])
    ])
  )

-- INTERSECT per row on two arrays
SELECT
  ARRAY(SELECT * FROM example.col1
    INTERSECT DISTINCT
    (SELECT * FROM example.col2)
  ) AS result
FROM example