时间戳

首页 > 解决方案 > BigQuery 标准 SQL：从表中删除重复项

google-bigquery - BigQuery 标准 SQL：从表中删除重复项

问题描述

我正在使用以下查询使用标准 sql 从 bigquery 中删除重复记录。但它抛出错误

with cte as (
select * ,row_number()over (partition by CallRailCallId order by CallRailCallId) as rn
from `encoremarketingtest.EncoreMarketingTest.CallRailCall2` )

delete
 from cte
where rn>1

Query Failed
Error: Syntax error: Expected "(" or keyword SELECT but got keyword DELETE at [5:5]

任何人都可以帮助我在 BigQuery 中使用正确的方法吗？

标签： google-bigquery

解决方案

选项1 

CREATE OR REPLACE TABLE `project.dataset.your_table` AS
SELECT * EXCEPT(rn)
FROM (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY CallRailCallId ORDER BY CallRailCallId) rn
  FROM `project.dataset.your_table`
) 
WHERE rn = 1

选项 #2 

CREATE OR REPLACE TABLE `project.dataset.your_table` AS
SELECT row.*
FROM (
  SELECT ARRAY_AGG(t ORDER BY CallRailCallId LIMIT 1)[OFFSET(0)] row
  FROM `project.dataset.your_table` t
  GROUP BY CallRailCallId
)

正如您可能注意到的那样，上述选项使用DDL(CREATE TABLE) 方法，并且可以只使用一个已知的（来自您的问题）列 -CallRailCallId
另外，请注意 -ORDER BY CallRailCallId在那里没有真正的作用，因为 GROUP BY 和 PARTITION BY 完全是相同的归档。但是，如果您更改该字段，这将控制哪一行（在少数重复项中）“存活”（例如ORDER BY ts DESC- 请参阅下面的选项了解 ts 可能是什么）

选项#3

此选项使用DML(DELETE FROM)，但需要使用一些额外的列作为决胜局

例如，您有tsTIMESTAMP 字段，并且您希望最新的（基于 ts）行存在 

DELETE FROM `project.dataset.your_table`
WHERE STRUCT(CallRailCallId, ts) NOT IN (
  SELECT AS STRUCT CallRailCallId, MAX(ts) ts
  FROM `project.dataset.your_table`
  GROUP BY CallRailCallId
  )

推荐阅读