1、示例场景描述
以保险行业的客户信息去重为例，为防止坐席重复联系客户，需要将客户进行唯一身份识别。存在以下两种情况，需要将其识别成一个人，这时候就需要进行数据去重的动作。
- 情况一：同一个客户有不同的来源渠道：客户即购买了寿险、又购买了产险（两个不同的来源系统）；
- 情况二：同一个客户多次回流：客户在同一个渠道多次购买（续保或者购买同一险种的不同产品）。
2、定义重复数据
通过姓名+证件类型+证件号将其识别为一个人，即只要这三个字段重复，就认为这些数据行为重复数据。
3、制定去重规则
- 多选一
随机：根据去重规则，随机保留一条数据。
优先级：根据去重规则 + 业务逻辑，保留优先需要的一条数据。例如优先保留“是否有房、是否有车”。 - 多合一
将重复数据合并成一条数据，合并规则根据业务逻辑确定。
4、创建测试数据（GaussDB）
客户信息字段主要包含姓名、性别、出生年月日、证件类型、证件号、来源、是否有车、是否有房、婚姻状态、手机号、……等信息。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
--创建客户信息表
CREATE TABLE customer(
name VARCHAR(20),
sex INT,
birthday VARCHAR(10),
ID_type INT,
ID_number VARCHAR(20),
source VARCHAR(10),
IS_car INT,
IS_house INT,
marital_status INT,
tel_number VARCHAR(15)
);
--插入测试数据
INSERT INTO customer VALUES('张三','1','1988-01-01','1',
'61010019880101****','寿险','1','1','1','');
INSERT INTO customer VALUES('张三','1','1988-01-01','1',
'61010019880101****','车险','1','0','1','');
INSERT INTO customer VALUES('张三','1','1988-01-01','1',
'61010019880101****','','','','','186****0701');
INSERT INTO customer VALUES('李四','1','1989-01-02','1',
'61010019890102****','寿险','1','1','1','');
INSERT INTO customer VALUES('李四','1','1989-01-02','1',
'61010019890102****','车险','1','0','1','');
INSERT INTO customer VALUES('李四','1','1989-01-02','1',
'61010019890102****','','','','','186****0702');
--查看结果
SELECT * FROM customer;
5、编写去重方法
1）随机保留：根据业务逻辑，随机保留一条记录。
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
*
FROM
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY name, id_type, id_number) as row_num
FROM
customer
)
WHERE
row_num = 1;
说明：
- ROW_NUMBER()：从第一行开始，依次为每一行分配一个唯一且连续的编号。
- PARTITION BY col1[, col2…]：指定分区的列，例如去重的键“姓名、证件类型、证件号码”。
- WHERE row_num = 1：取ROW_NUMBER()生成的编号1。
2）按优先级保留：根据业务逻辑，优先保留有手机号的一条记录，如果有多条记录含有手机号或有没有手机号，则在此基础上随机保留。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--保留含有手机号的记录行
SELECT
t.*
FROM
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY name, id_type, id_number
ORDER BY tel_number ASC
) as row_num
FROM
customer
) t
WHERE
t.row_num = 1;
说明：
- ROW_NUMBER()：从第一行开始，依次为每一行分配一个唯一且连续的号码。
- PARTITION BY col1[, col2…]：指定分区的列，例如去重的键“姓名、证件类型、证件号码”。
- ORDER BY col [asc或desc]：指定排序的列。升序（ ASC ）排列指只保留第一行，而降序排列（ DESC ）则指保留最后一行。
- WHERE row_num = 1：取ROW_NUMBER()生成的编号1。
3）合并保留：根据业务逻辑，合并完整性高、准确性高的字段信息。例如优先将含有手机号的记录行进行补齐，需要补齐的字段有“是否有车、是否有房、婚姻状况”，其取值是来源为“车险”的对应记录。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
--合并保留
SELECT
t1.name,
t1.sex,
t1.birthday,
t1.id_type,
t1.id_number,
t1.source,
t2.is_car,
t2.is_house,
t2.marital_status,
t1.tel_number
FROM
(
SELECT t.* FROM
(SELECT *,
ROW_NUMBER() OVER (PARTITION BY name, id_type, id_number
ORDER BY
tel_number ASC
) as row_num
FROM
customer
) t
WHERE
t.row_num = 1
) t1
LEFT JOIN (
SELECT
*
FROM
customer
WHERE
source = '车险'
and is_car IS NOT NULL
AND is_house IS NOT NULL
AND marital_status IS NOT NULL
) t2 ON t1.name = t2.name
and t1.id_type = t2.id_type
and t1.id_number = t2.id_number
说明：
t1表是优先保留含有手机的记录行（去重），并作为主表，t2表是需要补齐的字段来源表。两张表通过“姓名+证件类型+证件号码”进行关联，然后合并需要的信息。