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

问题描述

SQL上有很多很棒的帖子，用于选择唯一的行并写入（截断）表，以便删除dus。例如

  WITH ev AS（
 SELECT 
 *，
 ROW_NUMBER（）OVER（PARTITION BY row ORDER BY loadTime DESC）AS rowNum 
 FROM`duplicateates` 
）
 SELECT 
 * EXCEPT（rowNum）
 FROM 
 ev 
 WHERE rowNum = 1 
  

我试图用DML和DELETE略微探索一下（例如，如果你不用想要使用BQ savedQuery，只需执行SQL）。我想要做的大概是：
$ b $ pre $ WITH dup_events AS（
SELECT
*，
ROW_NUMBER（）OVER（PARTITION BY ID ORDER BY加载时DESC）AS的rowNum
FROM`duplicates`
）
DELETE FROM
dup_events
，其中的rowNum> 1


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

 语法错误：预期的（或关键字SELECT，但在[10：1]处获得了关键字DELETE 
  

可以使用DELETE来实现（standardSQL）吗？

谢谢！

解决方案

从语法文档， DELETE 的参数需要是一个表，并且没有规定使用 WITH 子句，这是有道理的，因为你不能从本质上是逻辑视图（CTE）中删除，你可以通过将逻辑放在过滤器中来表达你想要的内容，例如

  DELETE FROM 
`duplicateates` AS d 
 WHERE（SELECT ROW_NUMBER（）OVER（PARTITION BY id ORDER BY loadTime DESC）FROM `duplicateates` AS d2 WHERE d.id = d2.id AND d.loadTime = d2.loadTi我）> 1; 
  




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

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]

Can this be acheived (standardSQL) using DELETE?

thanks!

解决方案

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屋！