在BigQuery中将长度不均匀的多个数组展平 [英] Flatten multiple arrays with uneven lengths in BigQuery

问题描述

我试图在不重复结果的情况下将具有不同长度的不同列中的数组展平.

I'm trying to flatten arrays in different columns with different lengths without duplicating the results.

例如(使用标准SQL):

For example (using standard SQL):

WITH
  x AS (
  SELECT
    ARRAY[1,
    2,
    3] AS a,
    ARRAY[1,
    2] AS b)
SELECT
  a,
  b
FROM
  x,
  x.a,
  x.b

产生:

+-----++-----+
| a   |   b  |
+-----++-----+
|   1 |   1  |
|   1 |   2  |
|   2 |   1  |
|   2 |   2  |
|   3 |   1  |
|   3 |   2  |
+-----++-----+

它应该看起来像这样:

+-----++-----+
| a   |   b  |
+-----++-----+
|   1 |   1  |
|   2 |   2  |
|   3 | null |
+-----++-----+

推荐答案

您可以使用 JOIN :

SELECT a, b
FROM x LEFT JOIN
     UNNEST(x.a) a left join
     unnest(x.b) b 
     ON a = b;

这篇关于在BigQuery中将长度不均匀的多个数组展平的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！