Code d'identification de groupe de Doublons

Version imprimable

1 pièce(s) jointe(s)

Bonjour,

Je réalise un traitement de gestion des doublons dans des données clients.

Pour ce faire, je cherche les doublons sur le nom, le prénom et le code postal.
Mais on a beaucoup de doublons avec inversion du nom et du prénom.
Donc mon traitement ressemble à cela :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
--Autojointure avec :
								WHERE 
									t1.CodeClient<> t2.CodeClient
									AND   t1.Nom= t2.Nom
									AND   t1.Prenom= t2.Prenom
									AND   t1.CPostal = t2.CPostal 	
UNION
--Autojointure avec :
								WHERE 
									t1.CodeClient<> t2.CodeClient
									AND   t1.Prenom= t2.Nom
									AND   t1.Nom= t2.Prenom
									AND   t1.CPostal = t2.CPostal

Cela fonctionne très bien pour générer ma table de doublons.

Mais je souhaite avoir un ID pour identifier les groupes de doublons
Qu'ils soient issus du premier test ou du deuxième

L'idée serait d'avoir une sortie de ce type:

Pièce jointe 640537

Si vous avez une idée ou une piste à me donner cela sera top !!!
Merci beaucoup
Jean

PS : Mon code complet est ci-dessous

Code:

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
 
	INSERT INTO QDD.dbo.QDD_Doublons_ICAR 
			SELECT 				CONVERT(VARCHAR(10),'''+@Nom_Instance_Icar+''') AS Nom_Instance_Icar 
								, t3.Nombre As Nom_Collaborateur
								, t3.Pers AS Id_Collaborateur
								, t3.Descrip AS Type_Poste_Collaborateur 
								, t1.AltaUsuario AS Id_Utilisateur
								, t1.*
								, CASE 
									WHEN t4.Codigo IS NULL THEN 0
									WHEN t4.Codigo IS NOT NULL THEN 1
									ELSE 2
									END AS Top_tgClienteFac
								, 0 AS Top_NomPrenom					
								, '''+@Vision_Ctrx +''' AS Vision_Ctrx
								, '''+ CONVERT(VARCHAR, @Date_Ctrx , 103) + ''' AS Date_Ctrx
								, SYSDATETIME() AS Date_Execution
			FROM (SELECT * FROM ['+@Id_Serveur+'].['+@Nom_BDD_Icar+'].[dbo].tgCliente WHERE BAJA = 0 AND AltaFec < '''+ CONVERT(VARCHAR, @Date_Ctrx , 31) + ''') t1
			LEFT JOIN [QDD].[dbo].[Table_Utilisateurs_ICAR] t3 ON t1.AltaUsuario = t3.Pers AND t3.Nom_Instance_Icar = '''+@Nom_Instance_Icar+'''
			LEFT JOIN (SELECT DISTINCT Codigo FROM ['+@Id_Serveur+'].['+@Nom_BDD_Icar+'].[dbo].tgClienteFac) t4 ON t1.Codigo = t4.Codigo
			WHERE EXISTS	(
								SELECT *
								FROM (SELECT * FROM ['+@Id_Serveur+'].['+@Nom_BDD_Icar+'].[dbo].tgCliente WHERE BAJA = 0 AND AltaFec < '''+ CONVERT(VARCHAR, @Date_Ctrx , 31) + ''') t2
								WHERE 
									t1.Codigo <> t2.Codigo
									AND   t1.Apellido1 = t2.Apellido1
									AND   t1.Nombre = t2.Nombre
									AND   t1.CPostal = t2.CPostal 				
							)
		UNION
			SELECT 				CONVERT(VARCHAR(10),'''+@Nom_Instance_Icar+''') AS Nom_Instance_Icar 
								, t3.Nombre As Nom_Collaborateur
								, t3.Pers AS Id_Collaborateur
								, t3.Descrip AS Type_Poste_Collaborateur 
								, t1.AltaUsuario AS Id_Utilisateur
								, t1.*
								, CASE 
									WHEN t4.Codigo IS NULL THEN 0
									WHEN t4.Codigo IS NOT NULL THEN 1
									ELSE 2
									END AS Top_tgClienteFac
								, 1 AS Top_NomPrenom					
								, '''+@Vision_Ctrx +''' AS Vision_Ctrx
								, '''+ CONVERT(VARCHAR, @Date_Ctrx , 103) + ''' AS Date_Ctrx
								, SYSDATETIME() AS Date_Execution
			FROM (SELECT * FROM ['+@Id_Serveur+'].['+@Nom_BDD_Icar+'].[dbo].tgCliente WHERE BAJA = 0 AND AltaFec < '''+ CONVERT(VARCHAR, @Date_Ctrx , 31) + ''') t1
			LEFT JOIN [QDD].[dbo].[Table_Utilisateurs_ICAR] t3 ON t1.AltaUsuario = t3.Pers AND t3.Nom_Instance_Icar = '''+@Nom_Instance_Icar+'''
			LEFT JOIN (SELECT DISTINCT Codigo FROM ['+@Id_Serveur+'].['+@Nom_BDD_Icar+'].[dbo].tgClienteFac) t4 ON t1.Codigo = t4.Codigo
			WHERE EXISTS	
							(
								SELECT *
								FROM (SELECT * FROM ['+@Id_Serveur+'].['+@Nom_BDD_Icar+'].[dbo].tgCliente WHERE BAJA = 0 AND AltaFec < '''+ CONVERT(VARCHAR, @Date_Ctrx , 31) + ''') t2
								WHERE 
									t1.Codigo <> t2.Codigo
									AND   t1.Apellido1 = t2.Nombre
									AND   t1.Nombre = t2.Apellido1
									AND   t1.CPostal = t2.CPostal 
 
							)
					AND 
					NOT EXISTS 
							(	
								SELECT Codigo 
								FROM (SELECT * FROM ['+@Id_Serveur+'].['+@Nom_BDD_Icar+'].[dbo].tgCliente WHERE BAJA = 0 AND AltaFec < '''+ CONVERT(VARCHAR, @Date_Ctrx , 31) + ''') t2
								WHERE 
									t1.Codigo <> t2.Codigo
									AND   t1.Apellido1 = t2.Apellido1
									AND   t1.Nombre = t2.Nombre
									AND   t1.CPostal = t2.CPostal 
							)

23/06/2023, 16h37
tatayo

Bonjour,
Tu peux par exemple calculer un rang avec la fonction RANK, en partitionnant les lignes sur le triplet prénom/nom/code postal.

Tatayo.
23/06/2023, 17h20
AaâÂäÄàAaâÂäÄàAaâÂäÄ

Rien ne t'interdit d'ajouter une colonne unique id int identity(1,1) dans ta table source pour t'aider à t'y retrouver ;)
C'est souvent bien plus simple et rapide que faire ça en requête.
23/06/2023, 18h08
QuelJean

Citation:

Envoyé par tatayo

Bonjour,
Tu peux par exemple calculer un rang avec la fonction RANK, en partitionnant les lignes sur le triplet prénom/nom/code postal.

Tatayo.

Bonjour Tatayo,

Merci pour ton retour.

Apres analyse, je ne vois pas comment ton idée peut me permettre de regrouper des doublons où il y a eu inversion des nom et prénom.
Peux tu m'apporter des précisions sur ton idée.

Merci beaucoup.
Jean
23/06/2023, 18h10
QuelJean

Citation:

Envoyé par AaâÂäÄàAaâÂäÄàAaâÂäÄ

Rien ne t'interdit d'ajouter une colonne unique id int identity(1,1) dans ta table source pour t'aider à t'y retrouver ;)
C'est souvent bien plus simple et rapide que faire ça en requête.

Bonjour AaâÂäÄàAaâÂäÄàAaâÂäÄ,

Merci pour ton retour mais j'ai déjà une PK dans ma table.
C'est mon code client.

Et cela ne me permet pas, pour un client donné, d'avoir la liste de tout les doublons associé.

J'ai peut être pas bien cerné ton idée.
Peux tu me donner plus de détails.

Merci beaucoup
Jean
24/06/2023, 20h32
AaâÂäÄàAaâÂäÄàAaâÂäÄ

Citation:

Envoyé par QuelJean

Merci pour ton retour mais j'ai déjà une PK dans ma table. C'est mon code client.

Ok donc ma proposition n'est pas pertinente dans ce cas.

Citation:

Envoyé par QuelJean

Et cela ne me permet pas, pour un client donné, d'avoir la liste de tout les doublons associé.

comme on dit, «garbage in, garbage out »
Si les données sont dégueulasses avec plein de doublons, il faut s'assurer dès la saisie qu'il n'y a pas de doublons. Je sais, c'est facile de faire la leçon une fois que c'est fait.

J'avais réussi à refourguer ce genre de tâche de corriger des données dégueulasses à une autre personne, parce qu'à mon taux horaire de dba, mon boss trouvait ça cher pour faire du secrétariat...

Avec RANK, tu peux faire un truc du genre:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 insert into TableDoublon(Nom,Prenom,CodePostal,IdGroupe) select Nom,Prenom,CodePostal,rang from ( select Nom,Prenom,CodePostal,rank over (partition by nom,prenom,codepostal order by Nom,Prenom,CodePostal) as rang from (select nom,prenom,codepostal from LaTable as t1 inner join TaTable as T2 on t1.nom = t2.prenopm and t1.prenom = t2.nom and t1.CodePostal = t2.CodePostal) union select Nom,Prenom,CodePostal from LaTable group by Nom,Prenom,CodePostal having count(*) > 1 ))
La requête va donc insérer des lignes dans la table TableDoublon pour les doublons sur le nom/Prénom/CodePostal, et pour les lignes avec des inversions Nom/Prénom mais le même code postal.
C'est une ébauche, il faut affiner "un peu" la recherche des doublons (vaste sujet, auquel je me suis déjà frotté).
Mais c'est pour montrer le principe du RANK pour affecter un "identifiant" au trio Nom/Prénom/CodePostal.

Tatayo.

Bonjour,

Exemple de solution possible pour les critères de rapprochement souhaités, mais attention : décider qu'on a affaire à un vrai doublon en rapprochant uniquement nom+prénom+code postal n'est pas fiable, on peut avoir très facilement des personnes différentes mais homonymes, surtout dans les grandes villes.
Des critères plus fins tels que le SIRET pour les personnes morales ou le NIR pour les personnes physiques seraient préférables.
Code:

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 40 41 42 43 44 45 46 47 -- création d'un jeu d'essai with tab1 (id, nom, prenom, cp) as (select 001, 'martin', 'paul ', '78500' union all select 002, 'abadie', 'celine', '13001' union all select 003, 'paul ', 'martin', '78500' union all select 004, 'dupuis', 'henry ', '32000' union all select 005, 'abadie', 'céline', '13001' union all select 006, 'MARTIN', 'paul ', '78500' union all select 007, 'martin', 'paul ', '78500' union all select 008, 'castor', 'polux ', '99999' union all select 009, 'Henry ', 'Dupuis', '32000' union all select 010, 'martin', 'paul ', '44000' union all select 011, 'Martin', 'Paul ', '44000' ) , tab2 as (select T1.id as T1ID , T1.nom as T1NM , T1.prenom as T1PR , T1.cp as T1CP , T2.id as T2ID , T2.nom as T2NM , T2.prenom as T2PR , case when T1.nom < T1.prenom collate Latin1_General_CI_AI then lower(concat(T1.nom, T1.prenom, T1.cp)) else lower(concat(T1.prenom, T1.nom, T1.cp)) end as NMPR from tab1 T1 inner join tab1 T2 on T2.cp=T1.cp and T2.id>T1.id and ( ( T2.nom = T1.nom COLLATE Latin1_General_CI_AI and T2.prenom = T1.prenom COLLATE Latin1_General_CI_AI) or ( T2.nom = T1.prenom COLLATE Latin1_General_CI_AI and T2.prenom = T1.nom COLLATE Latin1_General_CI_AI) ) ) select dense_rank() over (order by NMPR) as IDNEW , NMPR , T1ID , T1NM , T1PR , T1CP , T2ID , T2NM , T2PR from tab2
Résultat :

IDNEW NMPR T1ID T1NM T1PR T1CP T2ID T2NM T2PR

1 abadieceline13001 2 abadie celine 13001 5 abadie céline

2 dupuishenry 32000 4 dupuis henry 32000 9 Henry Dupuis

3 martinpaul 44000 10 martin paul 44000 11 Martin Paul

4 martinpaul 78500 1 martin paul 78500 3 paul martin

4 martinpaul 78500 1 martin paul 78500 6 MARTIN paul

4 martinpaul 78500 3 paul martin 78500 6 MARTIN paul

4 martinpaul 78500 1 martin paul 78500 7 martin paul

4 martinpaul 78500 3 paul martin 78500 7 martin paul

4 martinpaul 78500 6 MARTIN paul 78500 7 martin paul

EDIT : requête modifiée pour concaténer également le code postal, au cas où deux personnes homonymes habitent une ville différente (cas de Martin Paul dans le jeu d'essai ci-dessus) ;)

27/06/2023, 13h41
QuelJean

Bonjour à tous,

Après plusieurs tests et modifications, cela fonctionne très bien.
Et à ma grande surprise c'est pas si couteux que ça en temps machine (+1.47 min sur un traitement complet de 18 min).

Un grands merci a vous tous.

Bonne journée ;-)

:D

IDNEW	NMPR	T1ID	T1NM	T1PR	T1CP	T2ID	T2NM	T2PR
1	abadieceline13001	2	abadie	celine	13001	5	abadie	céline
2	dupuishenry 32000	4	dupuis	henry	32000	9	Henry	Dupuis
3	martinpaul 44000	10	martin	paul	44000	11	Martin	Paul
4	martinpaul 78500	1	martin	paul	78500	3	paul	martin
4	martinpaul 78500	1	martin	paul	78500	6	MARTIN	paul
4	martinpaul 78500	3	paul	martin	78500	6	MARTIN	paul
4	martinpaul 78500	1	martin	paul	78500	7	martin	paul
4	martinpaul 78500	3	paul	martin	78500	7	martin	paul
4	martinpaul 78500	6	MARTIN	paul	78500	7	martin	paul