google-bigquery - BigQuery 中的空数组

Question

我有一个有 2 列的表：-（ 类型：字符串）
- （类型：int）name
order_id

我想做的是：

SELECT
  name,
  ARRAY_AGG(DISTINCT order_id) AS ids
FROM
  table
GROUP BY
  name

问题是，该order_id列有一些空字段。并非所有名称都对应一个 id。我想要的是一个包含 2 列（名称、id）的表，其中原始表中的所有名称仍然存在，如果没有相应的 id，则 ids 字段应该只是 NULL/空。如果有相应的 id，则 ids 列应包含一个包含所有order_ids属于该名称的数组。当我尝试ARRAY_AGG在空字段上使用时，我得到："Array cannot have a null element; error in writing field ids"

以下工作但很慢。有什么方法可以更有效地获得相同的结果？

SELECT * FROM
(SELECT name FROM table)
LEFT JOIN
(SELECT name, ARRAY_AGG(DISTINCT order_id FROM table GROUP BY name)

我也尝试过使用：

CASE
      WHEN ARRAY_LENGTH(ARRAY_AGG(DISTINCT order_id)) = 0 THEN NULL
      ELSE ARRAY_AGG(DISTINCT order_id)
    END AS ids

和

CASE
  WHEN ARRAY_LENGTH(ARRAY_AGG(DISTINCT order_id)) = 0 THEN []
  ELSE ARRAY_AGG(DISTINCT order_id)
END AS ids

仅将某些内容放在空字段中不是一种选择/解决方案，它必须是整数，并且任何整数都可能被“误解”为 id。

标签： google-bigquery

Answer 1

以下示例适用于 BigQuery 标准 SQL

#standardSQL
SELECT name, ARRAY_AGG(DISTINCT order_id IGNORE NULLS) ids
FROM `project.dataset.table`
GROUP BY name

这里的“技巧”在于使用IGNORE NULLS- 你可以阅读更多关于ARRAY_AGG

您可以使用下面的虚拟示例进行测试，玩上面

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'abc' name, 1 order_id UNION ALL
  SELECT 'abc', 2 UNION ALL
  SELECT 'abc', NULL UNION ALL
  SELECT 'xyz', NULL
)
SELECT name, ARRAY_AGG(DISTINCT order_id IGNORE NULLS) ids
FROM `project.dataset.table`
GROUP BY name

结果

Row name    ids  
1   abc     1    
            2    
2   xyz