sql - 对 BigQuery 分区中的行进行重复数据删除

Question

我有一个包含许多重复行的表 - 但我只想一次删除一个分区的重复行。

我怎样才能做到这一点？

例如，您可以从按日期分区并填充 1 到 5 的随机整数的表开始:

CREATE OR REPLACE TABLE `temp.many_random`
PARTITION BY d
AS 
SELECT DATE('2018-10-01') d, fhoffa.x.random_int(0,5) random_int
FROM UNNEST(GENERATE_ARRAY(1, 100))
UNION ALL
SELECT CURRENT_DATE() d, fhoffa.x.random_int(0,5) random_int
FROM UNNEST(GENERATE_ARRAY(1, 100))

Answer 1

让我们看看现有表中有哪些数据:

SELECT d, random_int, COUNT(*) c
FROM `temp.many_random`
GROUP BY 1, 2
ORDER BY 1,2

这是很多重复!

我们可以使用 MERGE 对单个分区进行重复数据删除和 SELECT DISTINCT *像这样的查询:

MERGE `temp.many_random` t
USING (
  SELECT DISTINCT *
  FROM `temp.many_random`
  WHERE d=CURRENT_DATE()
)
ON FALSE
WHEN NOT MATCHED BY SOURCE AND d=CURRENT_DATE() THEN DELETE
WHEN NOT MATCHED BY TARGET THEN INSERT ROW

那么最终的结果是这样的:



我们需要确保在 SELECT 中具有相同的日期以及带有 THEN DELETE 的行.这将删除该分区上的所有行，并插入 SELECT DISTINCT 中的所有行。 .

灵感来自:

https://medium.com/google-cloud/bigquery-deduplication-14a1206efdbb

要对整个表进行重复数据删除，请参阅:

https://stackoverflow.com/a/45311051/132438