带有重复变量的sql pivot

Question

我不熟悉 SQL 并使用 Google BigQuery。我有一个表，其中有一条记录如下所示:

publication_number |受让人

US-6044964-A|索尼公司

|数字音频光盘公司

标识符 publication_number 仅列出一次；第一个受让人出现在与 publication_number 相同的行，第二个受让人只是显示为没有标识符的附加行。

我想做的是创建一个如下表:

publication_number |受让人1 |受让人2

US-6044964-A |索尼公司 |数字音频光盘公司

其他受让人出现在另一列的位置。

我有 Google 的专利公开数据所称的“重复”变量。我在 BigQuery 中尝试了以下查询:

SELECT pvt.publication_number, pvt.[1] as assignee1, pvt.[2] as assignee2
FROM `main_tables.main_table5` 
PIVTO (
   MAX(assignee)
   FOR publication_number IN([1],[2])
) as pvt

我收到以下错误:

Syntax error: Unexpected "[" at [2:36]. If this is a table identifier, escape the name with , e.g.table.name` rather than [table.name]

我发现以下问题/回复与我使用 pivot here 想要做的事情类似。 .但是，我没有像该示例中那样为每一行设置标识符。

如何为第二个受让人创建另一列？

Answer 1

下面是针对 BigQuery 标准 SQL 的，应该会给你一个想法

#standardSQL
SELECT 
  publication_number, 
  assignee[SAFE_OFFSET(0)] assignee1,
  assignee[SAFE_OFFSET(1)] assignee2,
  assignee[SAFE_OFFSET(2)] assignee3,
  assignee[SAFE_OFFSET(3)] assignee4
FROM `yourproject.yourdataset.yourtable`   

你可以用下面的虚拟数据测试/玩它

#standardSQL
WITH `yourproject.yourdataset.yourtable` AS (
  SELECT 'US-6044964-A' publication_number , ['Sony Corporation', 'Digital Audio Disc Corporation'] assignee UNION ALL
  SELECT 'ABC', ['xyz', 'abc', 'uvw']
)
SELECT 
  publication_number, 
  assignee[SAFE_OFFSET(0)] assignee1,
  assignee[SAFE_OFFSET(1)] assignee2,
  assignee[SAFE_OFFSET(2)] assignee3,
  assignee[SAFE_OFFSET(3)] assignee4
FROM `yourproject.yourdataset.yourtable`   

结果是

publication_number  assignee1           assignee2                       assignee3   assignee4    
US-6044964-A        Sony Corporation    Digital Audio Disc Corporation  null        null     
ABC                 xyz                 abc                             uvw null