在BigQuery中展开结构体

Question

在BigQuery中，将包含两个数组的结构体展开的正确方法是什么？我有一个数据集，就像这里展示的一样（struct.destination和struct.visitors数组是有序的 - 即访问者计数与同一行中的目的地对应）：

我希望重新组织数据，以便为每个唯一的起点和目的地组合获得总访问者计数。理想情况下，最终结果将如下所示：

我尝试连续两次使用UNNEST - 一次在struct.destination上，一次在struct.visitors上，但这会产生错误的结果（每个目的地都被映射到访问者计数数组中的每个值，而实际上它只应该映射到同一行中的值）。

SELECT
  origin,
  unnested_destination,
  unnested_visitors
FROM
  dataset.table,
  UNNEST(struct.destination) AS unnested_destination,
  UNNEST(struct.visitors) AS unnested_visitors

- seeess1

Answer 1

您有一个重复的结构体。因此，我认为您想要：

SELECT origin,
       s.destination,
       s.visitors
FROM dataset.table t CROSS JOIN
     UNNEST(t.struct) s;

编辑：

我明白了，你有一个包含两个数组的结构体。你可以这样做：

SELECT origin, d.destination, v.visitors
FROM dataset.table t CROSS JOIN
     UNNEST(struct.destination) s WITH OFFSET nd LEFT JOIN
     UNNEST(struct.visitors) v WITH OFFSET nv
     ON nd = nv

- Gordon Linoff

---

感谢你的超快回复，Gordon。我尝试了你的建议，但是BigQuery给了我一个错误提示："Values referenced in UNNEST must be arrays. UNNEST contains expression of type STRUCT<destination ARRAY<STRING>, visitors ARRAY<STRING>>"（“在UNNEST中引用的值必须是数组。UNNEST包含类型为STRUCT<destination ARRAY<STRING>, visitors ARRAY<STRING>>的表达式。”） - seeess1

“s WITH offset” 应该是指的是 “d WITH offset” 吗？ - dlamblin

Answer 2

我发现这种构造可以正确对齐结构和行数据。

SELECT 
   T0.origin AS origin,
   S.destination AS destination,
   S.visitors AS total_visitors
FROM dataset.table T0,
UNNEST(struct) AS S

- Jamilah Foucher

Answer 3

没有底层数据进行测试很困难，因此我使用您的数据集创建了自己的查询。据我所知，destination|visitors 不是以数组格式而是结构格式存在，因此您不需要对其进行 UNNEST 操作。同时请查看此线程please :)

SELECT
  origin,
  COUNT(struct.destination),
  COUNT(struct.visitors)
FROM dataset.table
GROUP BY 1

- d_-