google-bigquery - BigQuery - 删除重复项的 DELETE 语句

Question

在 SQL 上有很多很棒的帖子，它们选择唯一的行并写入(截断)一个表，以便删除 dus。例如

WITH ev AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY loadTime DESC) AS rowNum
  FROM `duplicates`
 )
SELECT
  * EXCEPT(rowNum)
FROM
  ev
WHERE rowNum = 1

我试图使用 DML 和 DELETE 稍微不同地探索这个(例如，如果您不想使用 BQ 保存查询，只需执行 SQL)。我想做的大致是:

WITH dup_events AS (
  SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY loadTime DESC) AS rowNum
      FROM `duplicates`
 )
DELETE FROM
  dup_events
WHERE rowNum > 1

但在控制台中出现此错误:

Syntax error: Expected "(" or keyword SELECT but got keyword DELETE at [10:1]

这可以使用 DELETE 实现(标准SQL)吗？

谢谢!

Answer 1

来自 syntax documentation , DELETE 的参数需要是一个表，并且没有规定使用 WITH条款。鉴于您无法从本质上是逻辑 View (CTE)中删除，这是有道理的。您可以通过将逻辑放在过滤器中来表达您想要的内容，例如

DELETE
FROM duplicates AS d
WHERE (SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY loadTime DESC)
       FROM `duplicates` AS d2
       WHERE d.id = d2.id AND d.loadTime = d2.loadTime) > 1;