文档首页/ 数据仓库服务 GaussDB(DWS)/ 常见问题/ 数据库使用/ 如何删除GaussDB(DWS)重复的表数据？
更新时间：2024-10-08 GMT+08:00
查看PDF
分享

如何删除GaussDB(DWS)重复的表数据？

清理数据库脏数据时，可能会有多条重复数据只保留一条的场景，此场景可以使用聚合函数或窗口函数来实现。

构建表数据

  1. 创建表t_customer，向表中插入包含重复记录的数据： 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
    		 
    CREATE TABLE t_customer (
  id int NOT NULL,
  cust_name varchar(32) NOT NULL COMMENT '名字',
  gender varchar(10) NOT NULL COMMENT '性别',
  email varchar(32) NOT NULL COMMENT 'email',
  PRIMARY KEY (id)
) ;

INSERT INTO t_customer VALUES ('1', 'Tom', 'Male', 'high_salary@sample.com');
INSERT INTO t_customer VALUES ('2', 'Jennifer', 'Female', 'good_job@sample.com');
INSERT INTO t_customer VALUES ('3', 'Tom', 'Male', 'high_salary@sample.com');
INSERT INTO t_customer VALUES ('4', 'John', 'Male', 'good_job@sample.com');
INSERT INTO t_customer VALUES ('5', 'Jennifer', 'Female', 'good_job@sample.com');
INSERT INTO t_customer VALUES ('6', 'Tom', 'Male', 'high_salary@sample.com');

  2. 查询表t_customer： 

    1
    		 
    SELECT * FROM t_customer ORDER BY id;

当客户的名字、性别、邮件都相同时，则判定它们为重复的记录。对于表t_customer，id等于1、3、6的为重复数据，id为2、5的也是重复数据，删除多余的数据的同时需要保留其中的一条。

方法一：使用聚合函数min(expr)

使用聚合函数通过子查询取出id最小的不重复行，然后通过NOT IN删除重复数据。

  1. 查询id最小的不重复行： 

    1
2
3
4
5
6
7
8
    		 
    SELECT
    min(id) id,
    cust_name,
    gender,
    COUNT( cust_name ) count 
FROM t_customer 
GROUP BY cust_name,gender
ORDER BY id;

    通过查询结果可知，重复的数据行id为3、5、6的数据被过滤掉了。

  2. 使用NOT IN过滤重复数据行并删除： 

    1
2
3
4
5
6
    		 
    DELETE from t_customer where id not in (
	SELECT
	min(id) id
	FROM t_customer
	GROUP BY cust_name,gender
);

  3. 查询删除重复数据后的表t_customer： 

    1
    		 
    SELECT * FROM t_customer ORDER BY id;

    由返回结果可知，重复数据已被删除。

方法二：使用窗口函数row_number()

通过PARTITION BY对列进行分区排序并生成序号列，然后将序号大于1的行删除。

  1. 分区查询，对列进行分区排序并生成序号列： 

    1
2
3
4
5
6
    		 
    SELECT
	id,
	cust_name,
	gender,
	ROW_NUMBER() OVER (PARTITION BY cust_name,gender ORDER BY id) num
FROM t_customer;

    由返回结果可知，num>1的数据即为重复数据。

  2. 删除num>1的数据： 

    1
2
3
4
5
6
7
8
    		 
    DELETE FROM t_customer WHERE id in (
   SELECT id FROM(
	SELECT * FROM (
        SELECT ROW_NUMBER() OVER w AS row_num,id
	FROM t_customer  
	WINDOW w AS (PARTITION BY cust_name,gender ORDER BY id) )
   WHERE row_num >1 )
);

  3. 查询删除重复数据后的表t_customer： 

    1
    		 
    SELECT * FROM t_customer ORDER BY id;

父主题： 数据库使用