google-bigquery - BigQuery 标准 SQL : Delete Duplicates from Table

Question

我正在使用以下查询从使用标准 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 中使用正确的方法吗？

Answer 1

Option #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 

Option #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 可能是什么)

Option #3

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

例如你有 ts TIMESTAMP 字段，并且您希望最新的(基于 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
  )