批量删除冗余（重复）的垃圾数据

日常会遇到一些垃圾数据需要删除，少的话，可以一条一条删，多的话，手动删那效率就低了。因此可以通过 sql 脚本批量删除去实现，如下文 Demo 案例所述，案例测试数据参考附录 1 的 sql 脚本。

初始效果：ID 重复，名称重复

SELECT * FROM "AA"."TABLE1"

图中，通过两个维度分析数据质量：
第一，由于 TABLE1 表的 ID 没有加主键，导致 ID 重复
第二，名称列重复（典型的数据重复）

情景一、删除 ID 重复的数据,并将 ID 设置为主键

主键必须唯一，因此在加主键之前，必须清洗数据，否则加主键会报错“违反唯一性约束”。

目标：删除 ID 重复的数据，保留入库时间相对较晚的那条记录。

方式一：group by

步骤一：
（1）先查询 ID 重复的数据

select id from AA.TABLE1 group by id having count(1)>1

PS 知识点:在 SQL 语法中，"HAVING" 语句用于对聚合函数的结果进行过滤。它通常与"GROUP BY" 语句一起使用，以对分组的结果进行条件筛选。上面的例子就是对 ID 列进行分组，并过滤出来条数大于 1 的那些 ID。

select * from AA.TABLE1 t1 where t1.id in (
select id from AA.TABLE1 group by id having count(1)>1
)

PS 知识点:如果想更直观的看到重复的数据详情（比如名称和地区），可以使用 sql 中的子查询，即外层查询语句的 where 条件，是内层查询的结果集。如上面的例子 t1.id in (结果集)。
步骤二：
（2）这样可以查到哪些 ID 是重复的，查到之后，直接删除肯定不行，那表中就会丢失 ID=1 和ID=4 的数据。此时，需要用到 DM 数据库的隐藏字段“ROWID”。

select ROWID,* from AA.TABLE1 t1 where t1.id in (
select id from AA.TABLE1 group by id having count(1)>1
)

PS 知识点:ROWID 是用来标识记录的唯一标识符，通常在数据库表中自动生成。这个字段是隐藏的，不会在普通的查询结果中显示出来。入库早的ROWID 小，入库晚的 ROWID 大。
步骤三：
（3）在此基础上，找到重复 ID 中 ROWID 小的数据，即本情景中 ROWID=1 和 ROWID=5 的数据

select min(rowid) from AA.TABLE1 t1 where t1.id in (
select id from AA.TABLE1 group by id having count(1)>1
) group by id

PS 知识点：在 SQL 中，MIN() 函数用于返回某列中的最小值，MAX() 函数用于返回某列中的最大值，需结合 GROUP BY 语句使用。上面的例子就是对那些 ID 重复的数据，进行二次查询，通过 ID 分组，找到其中 ROWID 最小的那个值。
步骤四：
(4)删除垃圾数据
在 SQL 中，DELETE 语句用于从数据库表中删除记录。以下是 DELETE 语句的基本语法:

DELETE FROM table_name WHERE condition;
table_name: 要从中删除记录的表的名称。
condition: 可选的筛选条件，用于指定要删除的记录。如果省略条件，将删除表中的所有记录

PS 知识点：使用 DELETE 语句时要小心，因为它会永久删除数据。在执行删除操作之前，请确保已经备份了重要的数据，并且仔细检查和验证条件，以避免意外删除不需要的记录。
针对本情景，需要将前几次的查询到的结果集进行删除：
最终删除 sql 脚本如下：

--id 重复，删除 rowid 最小的-start
delete from AA.TABLE1 where rowid in(
--id 重复，查询到 rowid 最小的-start
select min(rowid) from AA.TABLE1 t1 where t1.id in (
select id from AA.TABLE1 group by id having count(1)>1
) group by id
--id 重复，查询到 rowid 最小的-end
)
--id 重复，删除 rowid 最小的-end

PS:假如重复数据 3 条以上，多次执行上面的删除语句即可

再次查询当前表，ID 列数据正常，且唯一：

SELECT ROWID,* FROM AA.TABLE1

再次给 ID 设置为主键，即可成功。

PS:数据量达到十万级别（131072 条删除 131066 条）时，此方式每次执行只能删除 6 条数据，因此需要删除 21844 次。故而寻求其他的解决办法，如下文方式二所述。

方式二：自连接

1.十万级
PS:数据量达到十万级别（131072 条删除 131066 条）时，上面提到的方式一适用于大多数场景，但是无法一次性删除重复次数大于 2 的数据，需要反复多次执行。此时，可以使用如下方式进行删除，一次性删除所有重复数据，耗时约 300 毫秒：
自连接的方式，sql 如下：

delete from "AA"."TABLE1" a
where
a.rowid!=
(
select max(b.rowid) from "AA"."TABLE1" b where a.id=b.id
)

2.百万级
PS:数据量达到百万级别（1048576 条删除 1048570 条）时，耗时 3 秒

3. 千万级
PS:数据量达到千万级别（16777216 条删除 16777210 条）时，耗时 39 秒

方式三：中间表

此方式，就是先把需要保留的数据查询出来，存到临时表；然后将原表中的数据清空；最后把临时表的数据写入到原表。

百万级

PS:数据量达到百万级别（1048576 条保留 6 条）时，整个流程操作下来约 5 秒
（1）创建中间表

CREATE TABLE "AA"."TABLE1_TEMP"
(
"ID" VARCHAR2(50),
"NAME" VARCHAR2(50),
"AREA" VARCHAR2(50));

（2）将查询到保留数据，写入中间表

insert into "AA"."TABLE1_TEMP"
select * from AA.TABLE1 where rowid in(
select max(rowid) from AA.TABLE1 t1 where t1.id in (
select id from AA.TABLE1 group by id having count(1)>1
) group by id
);

（3）清空原表

TRUNCATE TABLE "AA"."TABLE1";

（4）写入保留数据到原表

INSERT INTO "AA"."TABLE1" SELECT * FROM "AA"."TABLE1_TEMP" ;

（5）删除临时表

DROP TABLE "AA"."TABLE_TEMP1";

2. 千万级
PS:数据量达到百万级别（16777216 条保留 6 条）时，整个流程操作下来同样约 5 秒
（1）创建中间表

CREATE TABLE "AA"."TABLE1_TEMP"
(
"ID" VARCHAR2(50),
"NAME" VARCHAR2(50),
"AREA" VARCHAR2(50));

（2）将查询到保留数据，写入中间表

insert into "AA"."TABLE1_TEMP"
select * from AA.TABLE1 where rowid in(
select max(rowid) from AA.TABLE1 t1 where t1.id in (
select id from AA.TABLE1 group by id having count(1)>1
) group by id
);

（3）清空原表

TRUNCATE TABLE "AA"."TABLE1"

（4）写入保留数据到原表

INSERT INTO "AA"."TABLE1" SELECT * FROM "AA"."TABLE1_TEMP"

（5）删除临时表

DROP TABLE "AA"."TABLE_TEMP1"

总结：

综上所述，实际工作中，一般会有部分数据重复两次，重复三次以上的场景不多时，使用方式一或方式二都可以。当重复次数很多时（如百万级），推荐使用方式二，一个 sql 就能删除所有重复数据。方式三，百万级同样可以实现重复数据的处理，但是步骤相对较多，且需要新建和删除临时表，当方式二处理耗时很久时（如千万级），则推荐使用方式三。
假如数据库开启了归档，方式二删除千万级数据时（DELETE），会生成大量的归档日志文件，磁盘空间小很容易撑满导致数据库宕机，方式三删除千万级数据时（TRUNCATE），则不会产生大量的归档日志，对于空间不足的建议使用方式三。
本案例是基于 8 条基础 DEMO 数据，主要是说明删除重复数据的三种实现方式，和实际环境不一致请自行斟酌采用。

情景二、删除名称列重复的数据，保留 ID 较大的值

SELECT * FROM AA.TABLE1

同理，区别是不需要用隐藏列 ROWID，直接使用 ID、NAME 两列即可实现。
最终删除 sql 脚本如下：
--方式一

delete from AA.TABLE1 where id in(
select min(id) from AA.TABLE1 t1 where t1.name in (
select name from AA.TABLE1 group by name having count(1)>1
) group by name
)

--方式二

delete from "AA"."TABLE1" a
where a.ID!=(select max(b.ID) from "AA"."TABLE1" b where a.NAME=b.NAME )

最终效果：ID 唯一，数据唯一

附录 1：测试数据脚本

--创建表

CREATE TABLE "AA"."TABLE1"
(
"ID" VARCHAR2(50),
"NAME" VARCHAR2(50),
"AREA" VARCHAR2(50)) ;

--写入基础数据

INSERT INTO "AA"."TABLE1" VALUES ('1','张三','北京');
INSERT INTO "AA"."TABLE1" VALUES ('1','张三','北京');
INSERT INTO "AA"."TABLE1" VALUES ('2','张三','北京');
INSERT INTO "AA"."TABLE1" VALUES ('3','李四','石家庄');
INSERT INTO "AA"."TABLE1" VALUES ('4','李四','石家庄');
INSERT INTO "AA"."TABLE1" VALUES ('4','李四','石家庄');
INSERT INTO "AA"."TABLE1" VALUES ('5','王五','上海');
INSERT INTO "AA"."TABLE1" VALUES ('6','赵六','重庆');
COMMIT;

--使数据翻倍，多次执行就能到所需量级（或者使用循环语句）

INSERT INTO "AA"."TABLE1" SELECT * FROM "AA"."TABLE1";
COMMIT;

--利用循环快速翻倍数据

--十万数据量（131072 条）

declare
v_num integer;
begin
v_num:=1;
loop
insert into "AA"."TABLE1"
select * from "AA"."TABLE1" ;
v_num:=v_num+1;
exit when v_num=15;
end loop;
commit;
end;

--百万级数据量（1048576 条）

declare
v_num integer;
begin
v_num:=1;
loop
insert into "AA"."TABLE1"
select * from "AA"."TABLE1" ;
v_num:=v_num+1;
exit when v_num=18;
end loop;
commit;
end;

--千万级数据量（16777216 条）

declare
v_num integer;
begin
v_num:=1;
loop
insert into "AA"."TABLE1"
select * from "AA"."TABLE1" ;
v_num:=v_num+1;
exit when v_num=22;
end loop;
commit;
end;

仅供参考，如有不足，欢迎斧正。
更多资讯请上达梦技术社区了解： https://eco.dameng.com/