抱歉回答有些晚了,最近几天有点忙。
以下是我的答案,基于以下假设:
1)您将使用其他机制(您在原始问题的评论中提到要使用正则表达式进行清理)来清除名称拼写问题。
2)可以使用名字、姓氏和出生日期来识别重复记录集(您在原始问题的评论中提到了这一点)。
3)名字、姓氏和出生日期不能为空。
4)在一个重复记录集中不能有多个FIN记录(您在原始问题的评论中提到了这一点)。
如果以上任何假设无效,则我的答案将需要修改。
请执行以下步骤:
1)将所有FIN记录更新为从非-FIN记录中复制PersonalKey:
UPDATE Pruebas p1
INNER JOIN Pruebas p2
ON p1.Firstname = p2.Firstname
AND p1.Lastname = p2.Lastname
AND p1.Birth = p2.Birth
SET p1.PersonalKey = p2.PersonalKey
WHERE p1.Source like 'FIN%'
AND p1.PersonalKey is null
AND p2.PersonalKey is not null;
2) 删除所有非FIN记录,如果我们有FIN记录:
DELETE p2
FROM Pruebas p1
INNER JOIN Pruebas p2
ON p1.Firstname = p2.Firstname
AND p1.Lastname = p2.Lastname
AND p1.Birth = p2.Birth
WHERE p1.Source like 'FIN%'
AND p2.Source not like 'FIN%';
此时所有带有 FIN 记录的 DUP 已经被清除,只剩下 FIN 记录。
3) 如果我们决定对于所有其他情况都使用 DRV 记录,则需要将 PersonalKey 从另一个记录复制到 DRV 记录中:
UPDATE Pruebas p1
INNER JOIN Pruebas p2
ON p1.Firstname = p2.Firstname
AND p1.Lastname = p2.Lastname
AND p1.Birth = p2.Birth
SET p1.PersonalKey = p2.PersonalKey
WHERE p1.Source like 'DRV%'
AND p1.PersonalKey is null
AND p2.PersonalKey is not null;
4) 删除所有非DRV记录,但保留存在DRV记录的记录:
DELETE p2
FROM Pruebas p1
INNER JOIN Pruebas p2
ON p1.Firstname = p2.Firstname
AND p1.Lastname = p2.Lastname
AND p1.Birth = p2.Birth
WHERE p1.Source like 'DRV%'
AND p2.Source not like 'DRV%';
此时,所有带有DRV记录的DUP已被清理,只剩下DRV记录。
如果唯一的其他记录类型是SA记录,则不应再有DUP,我们完成了。
5)如果我们想选择填充最多信息的记录,或者如果我们完成了3和4,并且仍然有多个记录类型导致DUP。对于所有非FIN记录,我们需要将任何DUP集中具有PersonalKey的记录复制到任何没有PersonalKey的记录中:
UPDATE Pruebas p1
INNER JOIN Pruebas p2
ON p1.Firstname = p2.Firstname
AND p1.Lastname = p2.Lastname
AND p1.Birth = p2.Birth
SET p1.PersonalKey = p2.PersonalKey
WHERE p1.Source not like 'FIN%'
AND p1.PersonalKey is null
AND p2.PersonalKey is not null;
6) 删除除了信息得分计算列定义的最具信息量记录之外的所有记录:
DELETE p5
FROM Pruebas p5
INNER JOIN (SELECT p3.Firstname
, p3.Lastname
, p3.Birth
, MIN(p3.Id) AS min_id
FROM Pruebas p3
INNER JOIN (SELECT p1.Firstname
, p1.Lastname
, p1.Birth
, count(*) AS c
, MAX((p1.Name is not null) + (p1.RFC is not null) + (p1.Source is not null) + (p1.PersonalKey is not null)) AS info_score
FROM Pruebas p1
GROUP BY p1.Firstname
, p1.Lastname
, p1.Birth
HAVING count(*) > 1) p2
ON p3.Firstname = p2.Firstname
AND p3.Lastname = p2.Lastname
AND p3.Birth = p2.Birth
AND ((p3.Name is not null) + (p3.RFC is not null) + (p3.Source is not null) + (p3.PersonalKey is not null)) = p2.info_score
GROUP BY p3.Firstname
, p3.Lastname
, p3.Birth) p4
ON p4.Firstname = p5.Firstname
AND p4.Lastname = p5.Lastname
AND p4.Birth = p5.Birth
AND p4.min_id <> p5.Id;
此时,如果可用,则已保存PersonalKey,并保存了FIN记录(如果存在),否则将保存DRV记录或具有最多信息的记录。
如果您对上述任何内容有疑问,请告诉我。
希望能帮到您,
- Dipin