如何将所有行嵌套行转换回普通行bigquery？

在 Google BigQuery 中，将嵌套行（通常是记录类型或重复记录类型）转换为普通行（非嵌套）通常涉及到使用 UNNEST() 函数。UNNEST() 函数用于将数组类型的字段展开成多个行，这样每个数组元素都会对应一个行。这是处理嵌套数据并将其“平铺”为标准表格式的常用方法。

示例场景

假设你有一个包含嵌套数组的表 orders，其中有一个字段 items 是重复的记录类型（即嵌套行）。每个 items 记录包含 item_name 和 quantity 字段。

表结构如下：

• order_id: STRING
• order_date: DATE
• items: ARRAY<STRUCT<item_name STRING, quantity INT64>>

转换嵌套行为普通行

要将这种结构的数据转换为普通行，你可以使用以下 SQL 查询：

SELECT
  order_id,
  order_date,
  item.item_name,
  item.quantity
FROM
  `project.dataset.orders`,
  UNNEST(items) AS item

这里的关键是 UNNEST(items) AS item，它将 items 数组中的每个元素转换为单独的行。每个元素都被赋予别名 item，然后你可以通过 item.item_name 和 item.quantity 访问其字段。

处理多层嵌套

如果你的数据结构更复杂，例如有多层嵌套，你可以连续使用 UNNEST() 函数。假设每个 item 还包含一个嵌套数组 components，你可以这样查询：

SELECT
  order_id,
  order_date,
  item.item_name,
  item.quantity,
  component.component_name,
  component.component_quantity
FROM
  `project.dataset.orders`,
  UNNEST(items) AS item,
  UNNEST(item.components) AS component

这将进一步展开 components 数组，为每个组件创建单独的行。

注意事项

• 使用 UNNEST() 时，如果外层记录（如订单）中的数组为空，那么在结果中不会有这些记录的行。如果你希望即使数组为空也保留这些记录，可以使用 LEFT JOIN。

例如，如果你想保留所有订单，即使它们没有任何项目：

SELECT
  order_id,
  order_date,
  item.item_name,
  item.quantity
FROM
  `project.dataset.orders`
LEFT JOIN
  UNNEST(items) AS item

这样，即使 items 为空，订单的其他信息仍然会显示在结果中。

通过这些方法，你可以有效地将 BigQuery 中的嵌套数据结构转换为更传统的扁平化表格式，这对于数据分析和报告通常更为方便。