sql - 将 ARRAY_AGG() 与输入中的所有空值一起使用

Question

我想为我的表中的每个用户选择不同的列值(在 Google BigQuery 中)。我想过使用 ARRAY_AGG()喜欢:

SELECT user_id, ARRAY_AGG(DISTINCT field1) AS f1, ARRAY_AGG(DISTINCT field2) AS f2
FROM t GROUP BY user_id

但是因为对于一些 user_id field1 中的所有值或 field2为空，我收到此错误消息: Array 'f1' cannot have a null element
我想知道是否有解决方法可以避免此错误，或者是否有不同的方法可以在不使用 ARRAY_AGG() 的情况下实现结果。

Answer 1

来自 https://cloud.google.com/bigquery/sql-reference/data-types#array-type

BigQuery raises an error if query result has ARRAYs which contain NULL elements, although such ARRAYs can be used inside the query.

您的查询在临时查询中没问题，但在最终查询结果中没问题；因此，解决方法是您可以将查询定义为 tmp 表并在给出最终结果之前过滤掉 NULL 值:

WITH tmp AS (SELECT user_id,
                    ARRAY_AGG(DISTINCT field1) AS f1,
                    ARRAY_AGG(DISTINCT field2) AS f2
FROM t GROUP BY user_id)

SELECT user_id,
    ARRAY(SELECT el FROM UNNEST(f1) AS el WHERE el IS NOT NULL) AS f1,
    ARRAY(SELECT el FROM UNNEST(f2) AS el WHERE el IS NOT NULL) AS f2
 FROM tmp

我在将一些 Postgres SQL 移植到 BigQuery 时遇到了同样的问题，一个更优雅的解决方案是聚合函数上的 FILTER 子句，

https://www.postgresql.org/docs/current/static/sql-expressions.html

喜欢 ARRAY_AGG(f1 FILTER WHEN f1 IS NOT NULL)这在 BigQuery 中不可用，我真的希望他们可以实现它