更新时间:2024-06-21 GMT+08:00
如何删除重复的表数据?
清理数据库脏数据时,可能会有多条重复数据只保留一条的场景,此场景可以使用聚合函数或窗口函数来实现。
构建表数据
- 创建表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');
- 查询表t_customer:
1
SELECT * FROM t_customer ORDER BY id;
当客户的名字、性别、邮件都相同时,则判定它们为重复的记录。对于表t_customer,id等于1、3、6的为重复数据,id为2、5的也是重复数据,删除多余的数据的同时需要保留其中的一条。
方法一:使用聚合函数min(expr)
使用聚合函数通过子查询取出id最小的不重复行,然后通过NOT IN删除重复数据。
- 查询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的数据被过滤掉了。
- 使用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 );
- 查询删除重复数据后的表t_customer:
1
SELECT * FROM t_customer ORDER BY id;
由返回结果可知,重复数据已被删除。
方法二:使用窗口函数row_number()
通过PARTITION BY对列进行分区排序并生成序号列,然后将序号大于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的数据即为重复数据。
- 删除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 ) );
- 查询删除重复数据后的表t_customer:
1
SELECT * FROM t_customer ORDER BY id;
父主题: 数据库使用
数据库使用 所有常见问题
- 如何调整分布列?
- 如何查看和设置数据库的字符集编码格式
- 如何处理建表时date类型字段自动转换为timestamp类型的问题?
- 是否需要定时对常用的表做VACUUM FULL和ANALYZE操作?
- GaussDB(DWS)数据库设置主键后还需要设置分布键吗?
- GaussDB(DWS)是否兼容PostgreSQL的存储过程?
- 如何理解分区表、数据分区和分区键?
- 如何导出某张表结构?
- 是否有高效的删除表数据的方法?
- 如何查看外部表信息?
- 如果建表时没有指定分布列,数据会怎么存储?
- 如何将联结查询的null结果替换成0?
- 如何查看表是行存还是列存?
- GaussDB(DWS)列存表的常用信息查询
- GaussDB(DWS)查询时索引失效场景解析
- 如何使用自定义函数改写CRC32()函数
- 以pg_toast_temp*或pg_temp*开头的Schema是什么?
- GaussDB(DWS)查询时结果不一致的常见场景和解决方法
- 哪些系统表不能做VACUUM FULL
- 语句处于idle in transaction状态常见场景
- GaussDB(DWS)如何实现行转列及列转行?
- 唯一约束和唯一索引有什么区别?
- 函数和存储过程有什么区别?
- 如何删除重复的表数据?
more