Vertica允许将重复数据插入表中。可以使用“analyze_constraints”函数查看这些数据。如何从Vertica表中删除重复行?
Vertica允许将重复数据插入表中。可以使用“analyze_constraints”函数查看这些数据。如何从Vertica表中删除重复行?
您应该尽量避免/限制在大量记录中使用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 ;
这就是全部内容。
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 ;
就我个人而言,这并不是一个很好的答案,让我们把它作为最终结论吧。您可以删除两者中的任意一个,并将其重新插入。
您可以通过创建临时表并生成伪行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;
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);
Drop Table Table2;
你应该查看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最低的那个。