时间戳

首页 > 解决方案 > 我们如何从 BigQuery 中删除重复数据并将其保存到另一个具有很多属性的表中

sql - 我们如何从 BigQuery 中删除重复数据并将其保存到另一个具有很多属性的表中

问题描述

我已在 Google BigQuery 中上传了 99,628 行。该模式具有假设、公司名称、电话、电子邮件、地址、城市、州等。我只想保留company_name具有大多数属性的不同行。如果我有行

Microsoft | 2355 |

Microsoft | 1234 | ms@example.com | seatle | XYZ | KC

Microsoft | 2355 | any@example.com

我想保留第二行，因为它具有最高的属性。

我尝试使用以下查询，但它只返回不同的结果而不是具有最高属性的结果。

SELECT *
FROM (
  SELECT
      *,
      ROW_NUMBER()
      OVER (PARTITION BY company_name)
      row_number
  FROM `local-bastion-154121.Property_Dataset.pmDATA`
)
WHERE row_number = 1

标签： sqlgoogle-bigquery

解决方案

我将“具有最高属性”解释为对于特定company_name. 你应该能够做这样的事情：

CREATE TABLE dataset.new_table AS
SELECT
  company_name,
  ARRAY_AGG(
    (SELECT AS STRUCT t.* EXCEPT (company_name))
    ORDER BY ARRAY_LENGTH(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r': null'))
  )[OFFSET(0)].*
FROM dataset.existing_table AS t
GROUP BY company_name

作为示例数据的示例：

WITH existing_table AS (
  SELECT 'Microsoft' AS company_name, 2355 AS x, NULL AS email, NULL AS city, NULL AS y, NULL AS z UNION ALL
  SELECT 'Microsoft', 1234, 'ms@example.com', 'seattle', 'XYZ', 'KC' UNION ALL
  SELECT 'Microsoft', 2355, NULL, NULL, NULL, NULL
)
SELECT
  company_name,
  ARRAY_AGG(
    (SELECT AS STRUCT t.* EXCEPT (company_name))
    ORDER BY ARRAY_LENGTH(SPLIT(TO_JSON_STRING(t), ':null'))
  )[OFFSET(0)].*
FROM existing_table AS t
GROUP BY company_name

使用这个技巧和计NULL数值的SPLIT好处TO_JSON_STRING是你不需要显式地编写其他列的列表。它的作用是构建除 之外的所有列的结构column_name，并按行中值的数量NULL以升序排列，这意味着您将获得每个 的填充值最多的行company_name。

推荐阅读