Question

I have a table where some records may be duplicates.

Table: dashboard.scrappy.imoveis | data | imobiliaria | negocio | codigo | cidade | uf | tipo | bairro | url | endpoint | total | valor | | --------- | ----------- | ------- | ------ | ------ | ------ | ------ | ------ | ------ | -------- | ----- | ----- | | timestamp | string | string | string | string | string | string | string | string | string | float | float |

复制记录在4栏(/data,imobiliaria,negocio ,codigo)中标明。

表中没有中值指数(增值)栏。

我试图使用以下指示:(删除留下1个记录的重复)

DELETE FROM `dashboard.scrappy.imoveis`
WHERE ((ROW_NUMBER() OVER (PARTITION BY `data`, `imobiliaria`, `negocio`, `codigo`) > 1) AND (COUNT(*) > 1 OR (COUNT(*) = 1 AND ROW_NUMBER() OVER (PARTITION BY `data`, `imobiliaria`, `negocio`, `codigo`) = 1)))
GROUP BY `data`, `imobiliaria`, `negocio`, `codigo`;

But it is giving the error: Syntax error: Syntax error: Expected end of input but got keyword GROUP at [3:1]

关于如何确定建议的任何建议?

Answer 1

由于我们不能用小组来删除发言,你必须重新解释问题。在条款中也无法使用总合/窗口功能。

所有栏目中识别重复的最容易的方法是操作。

 Select distinct * from table_name

which will give you the unique rows. you can store this result temporarily in a different table or replace the existing table:

create or replace table table_name as
select distinct * from table_name.

或您可以发言并删除以下几句:

with cte as
(select *,
ROW_NUMBER() OVER (PARTITION BY `data`, `imobiliaria`, `negocio`, `codigo`) as dup_row from table_name )
delete from cte where dup_row > 1

友情链接