google-bigquery - bigquery可以查询扁平表并将其转换为嵌套数据结构吗

Question

现在我面临一个问题，bigquery 中有 5 个表，图像表 A 有一条记录，在表 B 中有 5 条记录与它有一些联系。这是示例:表A:记录一个B表:记录b,c,d,e,f

现在我正在使用这个 sql:

select A.*,B.* from A join each B on A.xx = B.xx

查询结果如下:a,b;a,c;a,d;a,e;a,f;(显示为 5 行)

有没有什么想法可以使结果像这样:

a,b c d e f; (show in 1 row)

希望得到您的帮助!谢谢!

Answer 1

一种方法是使用 GROUP_CONCAT

SELECT t1.c1,
       group_concat(t2.c2)
FROM
  (SELECT 'a' AS c1,
          1 AS k) t1
JOIN
  (SELECT *
   FROM
     (SELECT 'b' AS c2,
             1 AS k),
     (SELECT 'c' AS c2,
             1 AS k),
     (SELECT 'd' AS c2,
             1 AS k),
     (SELECT 'e' AS c2,
             1 AS k),
     (SELECT 'f' AS c2,
             1 AS k)) t2 ON t1.k=t2.k
GROUP BY t1.c1

这会产生:

+-----+-------+-----------+---+
| Row | t1_c1 |    f0_    |   |
+-----+-------+-----------+---+
|   1 | a     | b,c,d,e,f |   |
+-----+-------+-----------+---+

另一个是 using NEST (*免责声明适用于进一步阅读)

SELECT t1.c1,
       nest(t2.c2)
FROM
  (SELECT 'a' AS c1,
          1 AS k) t1
JOIN
  (SELECT *
   FROM
     (SELECT 'b' AS c2,
             1 AS k),
     (SELECT 'c' AS c2,
             1 AS k),
     (SELECT 'd' AS c2,
             1 AS k),
     (SELECT 'e' AS c2,
             1 AS k),
     (SELECT 'f' AS c2,
             1 AS k)) t2 ON t1.k=t2.k
GROUP BY t1.c1

但这必须写入表，因为 BigQuery 会自动展平查询结果，因此如果您在顶级查询上使用 NEST 函数，结果将不会包含重复字段。在使用子选择时使用 NEST 函数，该子选择会产生中间结果供同一查询立即使用。

默认情况下，在界面上，BigQuery 会展平所有查询结果。要保留嵌套和重复的结果，请选择一个目标表并启用允许大型结果，然后取消选中 Flatten results 选项。

*但是当您使用写入目标表时此功能存在一个已知错误:Save a result set containing repeated field to a destination table- 如果您不将结果保存到目标表中，它可能就很好。