Detailed description of MySQL Data deduplication instances

Detailed description of MySQL Data deduplication instances

Detailed description of MySQL deduplication instances

There are two Repeated Records. One is a completely repeated record, that is, all fields are duplicated, and the other is a record with some fields repeated. For the first type of repetition, it is easy to solve. You only need to use the distinct keyword in the query statement to remove duplicates. Almost all Database Systems Support the distinct operation. This type of repetition occurs mainly because the table is designed for weeks and can be avoided by adding a primary key or a unique index column to the table.

select distinct * from t;

For the second type of repetition problem, it is usually required to query any record in the repetition record. Assume that table t has three fields: id, name, and address. The id is the primary key and the duplicate fields are name and address. The unique result set of these two fields must be obtained.

-- Oracle and MySQL: use related subqueries

select * from t t1 where t1.id = (select min(t2.id)  from t t2  where t1.name = t2.name and t1.address = t2.address);

-- Hive only supports subqueries in the from clause. subqueries must have names and must be unique in columns.

select t1.* from t t1,   (select name, address, min(id) id from t group by name, address) t2 where t1.id = t2.id; 

-- You can also use the row_number () analysis function of hive.

select t.id, t.name, t.address from (select id, name, address,row_number() over (distribute by name, address sort by id) as rn      from t) t  where t.rn=1;

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!