在BigQuery中查询多个重复的字段 [英] Querying multiple repeated fields in BigQuery
问题描述
我试图查询交叉产品,但是我得到一个错误:
无法查询重复字段的交叉积...
如果我只查询2个重复字段,我可以将其中一个字段变平。然而,我有兴趣查询更多2个重复的字段,并且我无法理解FLATTEN语法是如何支持这个的。
例如,表结构是:
a1,记录(重复):a1.b1,整数
a2,记录(重复):a2.b1,整数
a3,记录(重复):a3.b1,整数
我想查询:
从tab中选择(*)
您可以在 FLATTEN
内使用嵌套子查询。它在选择语句周围需要额外的空白。 (不幸的是,语法很丑陋)。例如
SELECT
...
FROM(
FLATTEN((
SELECT
...
FROM(
FLATTEN((
SELECT
...
FROM
table ),
f1)
)
),
f2)
)
)
I have a schema that contains multiple repeated fields which are not nested.
I'm trying to query the cross product, but I get an error: "Cannot query the cross product of repeated fields..."
If I query only 2 repeated fields, I can flatten one of them. Yet, I'm interested in querying more that 2 repeated fields, and I can't understand how FLATTEN syntax supports this.
For example, say the table structure is: a1, record (repeated) : a1.b1, integer a2, record (repeated) : a2.b1, integer a3, record (repeated) : a3.b1, integer
I want to query: select (*) from tab
You can use a nested subselect inside the FLATTEN
. It requires an extra paren around the select statement. (the syntax is kind of ugly, unfortunately). e.g.
SELECT
...
FROM (
FLATTEN((
SELECT
...
FROM (
FLATTEN((
SELECT
...
FROM
table),
f1)
)
),
f2)
)
)
这篇关于在BigQuery中查询多个重复的字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!