我的目标是使用标准SQL从Google的Big Query专利-公共data.patents.publications_201710表中创建一个表，其中一行用于publication_number，受让人和assignee_harmonized.name，其中publication_number对于具有多个受让人的记录重复。这是我想要的输出的示例：
publication_number|受让人|assignee_harm
US-6044964-A|索尼公司|索尼公司
US-6044964-A|数字音频光盘公司|数字音频光盘公司
US-8746747-B2|IPS株式会社—焊接事业部|IPS公司—焊接部门
US-8746747-B2|空|麦克弗森 特里·
我根据这篇文章中找到的UNNEST建议尝试了以下查询
#standard SQL
SELECT
p.publication_number,
p.assignee,
a.name AS assignee_harm
FROM
`patents-public-data.patents.publications_201710` AS p,
UNNEST(assignee_harmonized) AS a
WHERE
p.publication_number IN ('US-6044964-A',
'US-8746747-B2')
但是，输出如下所示：
row|publication_number|受让人|assignee_harm
1|US-6044964-A|索尼公司|索尼公司
||数字音频光盘公司|
2|US-6044964-A|索尼公司|数字音频光盘公司
||数字音频光盘公司|
3|US-8746747-B2|IPS株式会社—焊接事业部|麦克弗森 特里·
4|US-8746747-B2|IPS株式会社—焊接事业部|IPS公司—焊接部门
您可以看到，"索尼公司"受让人与第 2 行中的"数字音频光盘公司"协调名称相关联，第 3 行中出现了类似的问题。此外，第 1 行和第 2 行各包含两行，但不重复publication_number标识符。我没有看到一种直接的方法可以做到这一点，因为"受让人"的数量并不总是等于"assignee_harmonized.name"的数量，而且它们并不总是以相同的顺序出现(否则我可以尝试创建两个表并以某种方式合并它们)。另一方面，必须有一种方法将"受让人"变量与其协调值"assignee_harmonized.name"相关联，否则将失去具有协调值的目的。您能否建议一个查询(或一组查询)，当有多个"受让人"或多个"assignee_harmonized.name"或两者兼而有之时，该查询将产生所需的输出？
您正在查询一个字符串和两个数组 - 整个事情基本上看起来像这样：
{
"publication_number": "US-8746747-B2",
"assignee": [
"IPS Corporation—Weld-On Division"
],
"assignee_harm": [
"MCPHERSON TERRY R",
"IPS CORPORATION—WELD ON DIVISION"
]
}
所以这就是数据，您需要以某种方式决定如何处理它们的组合......要么交叉连接所有内容：
#standard SQL
SELECT
p.publication_number,
assignee,
assignee_harmonized.name AS assignee_harm
FROM
`patents-public-data.patents.publications_201710` AS p
,p.assignee assignee
,p.assignee_harmonized AS assignee_harmonized
WHERE
p.publication_number IN ('US-6044964-A','US-8746747-B2')
.. 它给你关系数据 .. 或者你把它保留为两个单独的数组：
#standard SQL
SELECT
p.publication_number,
assignee,
ARRAY( (SELECT name FROM p.assignee_harmonized)) AS assignee_harm
FROM
`patents-public-data.patents.publications_201710` AS p
WHERE
p.publication_number IN ('US-6044964-A','US-8746747-B2')
您也可以将此嵌套结果另存为 bq 中的表。