首页 文章

在BigQuery的嵌套重复模式中，每个“ID”只选择一行

提问于
浏览 次
5

我在BigQuery中有一个表，其中包含ID字段和重复记录字段以及其他一些字段，如数据收集时间 .

此表中有多个行用于每个ID，我想以某种方式为每个ID选择/合并一行 . 几乎每个解决方案，如选择最后一个，首先选择并在一行中聚合具有重复ID的行，在我的用例中是可以接受的，但到目前为止我无法使它们中的任何一个工作 .

更确切地说，我的表有一个ID字段，在BigQuery术语中是： {name: ID, type: STRING, mode: NULLABLE} 和重复字段： {name: data, type: RECORD, mode: REPEATED} 以及其他一些（普通）字段 . 在我的表中，每个ID都有多行，每个ID都有一个重复的字段 data . 在我的查询结果中，我想要一个具有完全相同模式的表，其中每个ID只出现一次，并且其对应的 data 字段是原始表中出现的ID之一的 data 字段 . （或理想地从其所有出现的联合）

以下是此处不起作用的解决方案列表：

First ：使用

row_number() OVER (PARTITION BY ID ORDER BY collection_time) as rn ... where  rn=1

原因：即使使用了Unflatten Results选项，BigQuery也会在使用 partition by 时展平结果 .

Second: 选择具有最大/最小收集时间值的行：

因为：由于系统其他部分的重复，列的值对于表中的每个id都不是唯一的 .

Third ：在其他字段中使用 group by ID和 nest / first .

原因：在重复记录上使用 nest 会破坏记录字段中的关系 . 例如 SELECT ID, nest(data.a), nest(data.b) 来自：

ID     data.a      data.b
--------------------------
1      1a1          null
       1a2          1b2
--------------------------
1      2a1          2b1
       null         2b2

结果是

ID      data.a       data.b
----------------------------
1        1a1         1b2
         1a2         2b1
         2a1         2b2
sql google-bigquery

2 回答

  • 1

    使用standard SQL（在"Show Options"下取消选中"Use Legacy SQL"）可以更轻松地解决此问题 . 你会使用 GROUP BY 与 ARRAY_CONCAT_AGG ，例如：

    SELECT id, ARRAY_CONCAT_AGG(data) AS data
FROM MyTable
GROUP BY id;
    回复于
  • 1

    在标准SQL模式下尝试以下

    SELECT id, ARRAY_AGG(STRUCT(a, b)) AS data
FROM (
  SELECT id, a, ROW_NUMBER() OVER() AS num 
  FROM YourTable, UNNEST(data) WHERE NOT a IS NULL 
) FULL OUTER JOIN (
  SELECT id, b, ROW_NUMBER() OVER() AS num 
  FROM YourTable, UNNEST(data) WHERE NOT b IS NULL 
)  
USING(id, num) 
GROUP BY id

    它会在你的问题中给你 exactly 结果（消除NULL）：

    ID      data.a       data.b
----------------------------
1        1a1         1b2
         1a2         2b1
         2a1         2b2

    如果（另一方面）你想保留原始的a / b对 - 你应该在下面使用（仍然在标准SQL模式下）

    SELECT id, ARRAY_CONCAT_AGG(data) AS data
FROM YourTable
GROUP BY id

    这给你以下结果

    ID      data.a       data.b
----------------------------
1        1a1         null
         1a2         1b2
         2a1         2b1
         null        2b2

    你可以通过对你的实际表运行它们来测试这两个查询（将 YourTable 更改为你的实际表 - >project.dataset.table），或者通过在下面的代码前面添加相应的查询并按原样运行

    WITH YourTable AS (
  SELECT 1 AS id, ARRAY<STRUCT<a STRING, b STRING>>[('1a1', NULL),('1a2','1b2')] AS data UNION ALL
  SELECT 1 AS id, ARRAY<STRUCT<a STRING, b STRING>>[('2a1', '2b1'),(NULL,'2b2')] AS data 
)
    回复于

相关问题