BigQuery - 删除重复项的 DELETE 语句 [英] BigQuery - DELETE statement to remove duplicates

问题描述

有很多关于 SQL 选择唯一行并写入(截断)表以便删除 dus 的精彩帖子.例如

There are plenty of great posts on SQL that selects unique rows and write (truncates) a table so the dus are removed. e.g

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).我想做的大概是:

I was trying to explore this slightly differently using DML and DELETE (e.g if you dont want to use a BQ savedQuery, just execute SQL). What I want to do is roughly:

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

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

but got this error in the console:

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

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

Can this be acheived (standardSQL) using DELETE?

谢谢！

推荐答案

来自 语法文档，DELETE 的参数需要是一个表，并且没有规定使用WITH 子句.鉴于您无法从本质上是逻辑视图(CTE)中删除，这是有道理的.您可以通过将逻辑放在过滤器中来表达您想要的内容，例如

From the syntax documentation, the argument to DELETE needs to be a table, and there is no provision for using a WITH clause. This makes sense given that you can't delete from what is essentially a logical view (a CTE). You can express what you want by putting the logic inside the filter, e.g.

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;

这篇关于BigQuery - 删除重复项的 DELETE 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！