在Vertica数据库中删除重复行

Question

Vertica允许将重复数据插入表中。可以使用“analyze_constraints”函数查看这些数据。如何从Vertica表中删除重复行？

- Amit

Answer 1

您应该尽量避免/限制在大量记录中使用DELETE。以下方法应更加有效：

步骤1创建一个新表，其结构/投影与包含重复项的表相同：

create table mytable_new like mytable including projections ;

步骤 2 将去重后的行插入到这个新表中：

insert /* +direct */ into mytable_new select <column list> from (
    select * , row_number() over ( partition by <pk column list> ) as rownum from <table-name>
) a where a.rownum = 1 ;

步骤三 重命名原始表格（包含重复项的表格）：

alter table mytable rename to mytable_orig ;

步骤 4：重命名新表：

alter table mytable_new rename to mytable ;

这就是全部内容。

- mauro

---

这非常慢。我的表中有43万行。查询一直在运行。@mauro - kushagra mittal

@kushagramittal 在Vertica博客中，使用DISTINCT函数描述了一种更快的方法。请在https://www.vertica.com/blog/another-way-to-de-duplicate-table-rows-quick-tip/找到它。 - JPS

Answer 2

Mauro的回答是正确的，但第二步的SQL语句存在错误。因此，完整的避免DELETE工作方式应该如下： 步骤1：创建一个与包含重复值的表具有相同结构/投影的新表：

create table mytable_new like mytable including projections ;

第二步：将去重后的行插入到这个新表中：

insert /* +direct */ into mytable_new select <column list> from (
            select * , row_number() over ( partition by <pk column list> ) as rownum from mytable
    ) a where a.rownum = 1 ;

第三步：重命名原始表（包含重复项的表）：

alter table mytable rename to mytable_orig ;

步骤四 重命名新表：

alter table mytable_new rename to mytable ;

- stevendv

Answer 3

就我个人而言，这并不是一个很好的答案，让我们把它作为最终结论吧。您可以删除两者中的任意一个，并将其重新插入。

- geoffrobinson

Answer 4

您可以通过创建临时表并生成伪行ID来删除Vertica表中的重复项。以下是一些步骤，特别是如果您要从非常大和宽的表中删除重复项。在下面的示例中，我假设k1和k2行有多个重复项。有关更多信息，请在此处查看。

-- Find the duplicates
select keys, count(1) from large-table-1
where [where-conditions]
group by 1
having count(1) > 1
order by count(1) desc  ;

-- Step 2:  Dump the duplicates into temp table
create table test.large-table-1-dups
like large-table-1;

alter table test.large-table-1-dups     -- add row_num column (pseudo row_id)
add column row_num int;

insert into test.large-table-1-dups
select *, ROW_NUMBER() OVER(PARTITION BY key)
from large-table-1
where key in ('k1', 'k2');     -- where, say, k1 has n and k2 has m exact dups

-- Step 3: Remove duplicates from the temp table
delete from test.large-table-1-dups
where row_num > 1;

select * from test.dim_line_items_dups;    
--  Sanity test.  Should have 1 row each of k1 & k2 rows above

-- Step 4: Delete all duplicates from main table...
delete from large-table-1
where key in ('k1', 'k2');

-- Step 5: Insert data back into main table from temp dedupe data
alter table test.large-table-1-dups
drop column row_num;

insert into large-table-1
select * from test.large-table-1-dups;

- Shiva

Answer 5

步骤1：创建一个中间表格，将数据从原始表格传输/加载到其中，并附带行号。 在下面的示例中，将数据从Table1传输到Table2，并附带row_num列。

select * into Table2 from (select *, ROW_NUMBER() OVER(PARTITION BY A,B order by C)as row_num from Table1 ) A;

步骤2：使用上述步骤中创建的Table2删除Table1中的数据

DELETE FROM Table1 WHERE  EXISTS (SELECT NULL FROM Table2  
                                  where Table2.A=Table1.A 
                                  and Table2.B=Table1.B 
                                  and row_num > 1);

第三步：删除在第一步（即Table2）创建的表。

Drop Table Table2;  

- sunil kumar

Answer 6

你应该查看PostgreSQL wiki中的这个答案，它同样适用于Vertica：

DELETE
FROM
    tablename
WHERE
    id IN(
        SELECT
            id
        FROM
            (
                SELECT
                    id,
                    ROW_NUMBER() OVER(
                        partition BY column1,
                        column2,
                        column3
                    ORDER BY
                        id
                    ) AS rnum
                FROM
                    tablename
            ) t
        WHERE
            t.rnum > 1
    );

它会删除所有重复的条目，但保留id最低的那个。

- Basil

---

如果ID相同，它将删除所有记录。 - Carbonrock