Code d'identification de groupe de Doublons

**QuelJean** · 23/06/2023, 16h18

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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:

Nom : Capture d’écran 2023-06-23 101318.png
Affichages : 124
Taille : 10,2 Ko

Nom : Capture d’écran 2023-06-23 101318.png
Affichages : 124
Taille : 10,2 Ko

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 
							)

**tatayo** · 23/06/2023, 16h37

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.

**AaâÂäÄàAaâÂäÄàAaâÂäÄ** · 23/06/2023, 17h20

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.

**QuelJean** · 23/06/2023, 18h10

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

**AaâÂäÄàAaâÂäÄàAaâÂäÄ** · 24/06/2023, 20h32

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.

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...

**tatayo** · 26/06/2023, 11h11

Avec RANK, tu peux faire un truc du genre:

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

**escartefigue** · 26/06/2023, 11h27

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 SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

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)

**QuelJean** · 23/06/2023, 18h08

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

Code d'identification de groupe de Doublons

Développement SQL Server

Vue hybride

Discussions similaires

Partager

Partager