Détection de clients en double selon plusieurs conditions dans une proc SQL

**MDsas** · 25/02/2014, 22h17

Bonjour à tous,

Je suis à la recherche d'une méthode optimale pour comparer des champs afin de détecter des clients potentiellements en double.
je travail dans SAS EG 5.1.

Contexte : j'ai une table de clients avec toutes ces appellations (les différents noms, prénoms qu'il a eu dans le temps ou dans différents systèmes) qui contient 30 millions de ligne.

En première instance les champs qui déterminent qu'ils sont potentiellement en double sont : SEXE identique, DATE DE NAISSANCE identique, NOM du client identique ou semblable, PRENOM du client identique ou semblable, NOM de la mère identique ou semblable ET numéro de client différents.
En premier lieu on procédait par comparaison SOUNDEX dans un where d'une proc SQL maintenant on veut rafiiner ces critères en rajoutant d'autres blocs de conditions c'est là ou je pêche un peu

Les conditions seraient ainsi :

1 - égalité parfaite des 5 variables et numéro de clients différents
2- son indentique des 5 variables (SOUNDEX) et numéro de clients différents
3- une lettre peu différer dans les 3 variables de noms (COMPLEV) et numéro de clients différents
4- un nom peut en contenir un autre et numéro de clients différents

J'ai tenté les 3 premieres avec des OR dans le WHERE sur 100 000 lignes et là c'est même trop long ...imaginer les 30M.

Je fais appel à vos suggestions pour optimiser, imbriquer les conditions, y aller par élimination (cdtion 1 ok...on passe àa 2 sans relire les 30M ainsi de suite...).

Un exemple de code :

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
 
 
 PROC SQL;
   CREATE TABLE WORK.test_clipot AS 
   SELECT t1.NO_SEQ_CLI, 
		  t2.NO_SEQ_CLI AS NO_SEQ_CLI1, 
          t1.NO_IDN_APP, 
          t1.CO_SOU_APP, 
          t1.CO_TYP_APP, 
          t1.IN_COR,
          t1.NM_APP_CLI, 
		  t2.NM_APP_CLI AS NM_APP_CLI1, 
          t1.PR_APP_CLI, 
		  t2.PR_APP_CLI AS PR_APP_CLI1, 
          t1.DT_NAI_CLI, 
          t1.NM_NAI_MER_CLI, 
		  t2.NM_NAI_MER_CLI AS NM_NAI_MER_CLI1, 
          t1.AD_LIG_1, 
		  t2.AD_LIG_1 AS AD_LIG_11, 
          t1.AD_LIG_2, 
          t2.AD_LIG_2 AS AD_LIG_21, 
          t1.CO_POS, 
		  t2.CO_POS AS CO_POS1, 
          t1.NO_TEL_COM, 
          t2.NO_TEL_COM AS NO_TEL_COM1, 
          t1.NO_TEL_RES,        
          t2.NO_TEL_RES AS NO_TEL_RES1,
		  CAT(t1.AD_LIG_1," " ,t1.AD_LIG_2) as adresse_1,
		  CAT(t2.AD_LIG_1," " ,t2.AD_LIG_2) as adresse_2
      FROM  EXTRACTION_APPELLATION t1, 
			EXTRACTION_APPELLATION t2
 
      WHERE		
 
			/*Bloc egalite parfaite*/
 
		(	t1.DT_NAI_CLI     = t2.DT_NAI_CLI 
			AND t1.CO_SEX_CLI	  = t2.CO_SEX_CLI
			AND t1.NO_SEQ_CLI NOT = t2.NO_SEQ_CLI 
			AND t1.NM_NAI_MER_CLI = t2.NM_NAI_MER_CLI
			AND t1.NM_APP_CLI	  = t2.NM_APP_CLI
			AND t1.PR_APP_CLI	  = t2.PR_APP_CLI)   
 
	 OR
			/*Bloc similarité de son*/
 
		(	t1.DT_NAI_CLI     = t2.DT_NAI_CLI 
			AND t1.CO_SEX_CLI	  = t2.CO_SEX_CLI
			AND t1.NO_SEQ_CLI NOT = t2.NO_SEQ_CLI 
			AND soundex(t1.NM_NAI_MER_CLI) = soundex(t2.NM_NAI_MER_CLI)
			AND soundex(t1.NM_APP_CLI)	   = soundex(t2.NM_APP_CLI)
			AND soundex(t1.PR_APP_CLI)	   = soundex(t2.PR_APP_CLI ))  
 
	OR
			/*Bloc similarité de son et un caractère de différend*/
 
			(	t1.DT_NAI_CLI     = t2.DT_NAI_CLI 
			AND t1.CO_SEX_CLI	  = t2.CO_SEX_CLI
			AND t1.NO_SEQ_CLI NOT = t2.NO_SEQ_CLI 
			AND complev(strip(t1.NM_NAI_MER_CLI),strip(t2.NM_NAI_MER_CLI))<=1
			AND complev(strip(t1.NM_APP_CLI),strip(t2.NM_APP_CLI))<=1
			AND complev(strip(t1.PR_APP_CLI),strip(t2.PR_APP_CLI))<=1
			)  
      ORDER BY t1.NO_SEQ_CLI;
QUIT;

Pour info j'avais des macros qui font les différents comparaison mais je ne sais pas comment l'intégrer dans mon WHERE...

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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
 
 
%let where_cond= between 0 and 10;
/*********************************************************************/
 /***************************** DD1AA *********************************/
 /*********************************************************************/
 %let trans_to='EEEEAAOOIIIUU';
 %let trans_from='ÊËÉÈÂÀÔÖÎÏÌÛÙ';
 %let remove="() -'.";
 
 /*********************************************************************/
 /* transform                                                         */
 /*                                                                   */
 /* Macro pour transformer une chaîne de caractères avant de faire une*/
 /* comparaison. La macro enlève les accents, les blancs et certains  */
 /* caractères spéciaux, question que ces éléments ne viennent pas    */
 /* gêner la comparaison.                                             */
 /*********************************************************************/
 %macro transform(string);
     strip(
           translate(
                     compress(upcase(&string.), &remove.),
                     &trans_to.,&trans_from.)
                     )
 %mend transform;
 
 
 /*********************************************************************/
 /* pareil                                                            */
 /*                                                                   */
 /* Macro qui indique si 2 chaînes de caractères sont pareils, avec un*/
 /* certain degré de tolérance. Par exemple, voici les cas où les 2   */
 /* chaînes sont considérées comme "pareil" :                         */
 /* - La chaine 1 est inclue dans la chaîne 2, ou vice-versa.         */
 /* - Les 2 chaines sont pareils phonétiquement (avec SOUNDEX).       */
 /* - Il y a une différence de 1 seul caractère entre les 2 chaînes.  */
 /* - Une partie de la nouvelle chaîne sonne (SOUNDEX) ou est presque */
 /*   pareil (différence de 1 caractère acceptée) avec l'ancienne     */
 /*   chaîne. Pour ce point, on vérifie que la nouvelle chaîne dans   */
 /*   l'ancienne, et non l'inverse. Donc l'ordre des paramètres est   */
 /*   important.                                                      */
 /*                                                                   */
 /* USAGE                                                             */
 /* -----                                                             */
 /* Dans un data step.                                                */
 /*                                                                   */
 /* data test;                                                        */
 /*     t1 = "allo";                                                  */
 /*     t2 = "alo";                                                   */
 /*     %pareil(t1, t2, p);                                           */
 /* run;                                                              */
 /*                                                                   */
 /* Une nouvelle colonne "p" est ajoutée au dataset, et elle contient */
 /* la valeur 1 si les colonnes passées en paramètre sont considérées */
 /* pareil, ou 0 sinon.                                               */
 /*                                                                   */
 /*********************************************************************/
 %MACRO pareil(anc,nouv, sortie);
    /* Test 1 : est-ce que le nouveau prénom contient l'ancien */
    t1 = max(find(%transform(&nouv.), %transform(&anc.), 'it'),
             find(%transform(&anc.), %transform(&nouv.), 'it'));
 
    /* Test 2 : est-ce que le nouveau prénom sonne comme l'ancien */
    t2 = (soundex(&nouv.) = soundex(&anc.));
 
    /* Test 3 : On accepte une différence de 1 dans les noms */
    t3 = complev(strip(&nouv.), strip(&anc.));
    if t3 in (0,1) then t3 = 1; else t3 = 0;
 
    /* Tests 4 et 5 : Est-ce qu'une partie du nouveau prénom  */
    /*               sonne ou s'écrit comme l'ancien          */
    n = 1;
    t4 = 0;
    t5 = 0;
    nom = scan(&nouv., n, ,'s');
    do while (strip(nom) ne '' and t4=0 and t5=0);
 
      t4 = (soundex(nom) = soundex(&anc.));
 
      t5 = complev(strip(nom), strip(&anc.));
      if t5 in (0,1) then t5 = 1; else t5 = 0;
 
      n = n + 1;
      nom = scan(&nouv., n, ,'s');
    end;
 
    &sortie. = max(t1, t2, t3, t4, t5);
 %MEND pareil;

**MDsas** · 26/02/2014, 19h19

Bonjour,

J'ai retravaillé le code. Ca prenait 0.53 secondes pour 100 000 donc j'avais bon espoir !
J'ai vite déchanté ca fait 3h15 que j'attends, la bonne nouvelle c'est au moins la police du serveur n'a pas arrêté ma requête.

Voici le nouveau code

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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
 
 
 %let trans_to='EEEEAAOOIIIUU';
 %let trans_from='ÊËÉÈÂÀÔÖÎÏÌÛÙ';
 %let remove="() -'.";
 
 /*********************************************************************/
 /* transform                                                         */
 /*                                                                   */
 /* Macro pour transformer une chaîne de caractères avant de faire une*/
 /* comparaison. La macro enlève les accents, les blancs et certains  */
 /* caractères spéciaux, question que ces éléments ne viennent pas    */
 /* gêner la comparaison.                                             */
 /*********************************************************************/
 %macro transform(string);
     strip(
           translate(
                     compress(upcase(&string.), &remove.),
                     &trans_to.,&trans_from.)
                     )
 %mend transform;
 
 PROC SQL;
   CREATE TABLE WORK.test_clipot AS 
   SELECT t1.NO_SEQ_CLI, 
		  t2.NO_SEQ_CLI AS NO_SEQ_CLI1, 
          t1.NO_IDN_APP, 
          t1.CO_SOU_APP, 
          t1.CO_TYP_APP, 
          t1.IN_COR,
          t1.NM_APP_CLI, 
		  t2.NM_APP_CLI AS NM_APP_CLI1, 
          t1.PR_APP_CLI, 
		  t2.PR_APP_CLI AS PR_APP_CLI1, 
          t1.DT_NAI_CLI, 
          t1.NM_NAI_MER_CLI, 
		  t2.NM_NAI_MER_CLI AS NM_NAI_MER_CLI1, 
          t1.AD_LIG_1, 
		  t2.AD_LIG_1 AS AD_LIG_11, 
          t1.AD_LIG_2, 
          t2.AD_LIG_2 AS AD_LIG_21, 
          t1.CO_POS, 
		  t2.CO_POS AS CO_POS1, 
          t1.NO_TEL_COM, 
          t2.NO_TEL_COM AS NO_TEL_COM1, 
          t1.NO_TEL_RES,        
          t2.NO_TEL_RES AS NO_TEL_RES1,
		  CAT(t1.AD_LIG_1," " ,t1.AD_LIG_2) as adresse_1,
		  CAT(t2.AD_LIG_1," " ,t2.AD_LIG_2) as adresse_2
      FROM SORTIE.EXTRACTION_APPELLATION as t1 
 		JOIN SORTIE.EXTRACTION_APPELLATION as t2
		ON 		t1.NO_SEQ_CLI NOT = t2.NO_SEQ_CLI 
			AND	t1.DT_NAI_CLI     = t2.DT_NAI_CLI 
			AND t1.CO_SEX_CLI	  = t2.CO_SEX_CLI
 
      WHERE		
 
			/*Bloc egalite parfaite*/
 
		(	t1.NM_NAI_MER_CLI = t2.NM_NAI_MER_CLI
			AND t1.NM_APP_CLI	  = t2.NM_APP_CLI
			AND t1.PR_APP_CLI	  = t2.PR_APP_CLI)   
 
	 OR
			/*Bloc similarité de son*/
 
		( 	soundex(t1.NM_NAI_MER_CLI) 	   = soundex(t2.NM_NAI_MER_CLI)
			AND soundex(t1.NM_APP_CLI)	   = soundex(t2.NM_APP_CLI)
			AND soundex(t1.PR_APP_CLI)	   = soundex(t2.PR_APP_CLI ))  
 
	OR
			/*Bloc similarité de son et un caractère de différend*/
 
 
		(	complev(strip(t1.NM_NAI_MER_CLI),strip(t2.NM_NAI_MER_CLI))<=1
			AND complev(strip(t1.NM_APP_CLI),strip(t2.NM_APP_CLI))<=1
			AND complev(strip(t1.PR_APP_CLI),strip(t2.PR_APP_CLI))<=1
			)  
 
	OR
				/*Bloc un inclut dans l'autre */
 
		(	
				(max(find(%transform(t1.PR_APP_CLI), %transform(t2.PR_APP_CLI), 'it'),
             	 find(%transform(t2.PR_APP_CLI), %transform(t1.PR_APP_CLI), 'it')))>=1
			AND  (max(find(%transform(t1.NM_APP_CLI), %transform(t2.NM_APP_CLI), 'it'),
             	  find(%transform(t2.NM_APP_CLI), %transform(t1.NM_APP_CLI), 'it')))>=1
			AND (max(find(%transform(t1.NM_NAI_MER_CLI), %transform(t2.NM_NAI_MER_CLI), 'it'),
             	 find(%transform(t2.NM_NAI_MER_CLI), %transform(t1.NM_NAI_MER_CLI), 'it')))>=1
 
			)  
 
      ORDER BY t1.NO_SEQ_CLI;
 
QUIT;

Si vous avez des options pour optimiser je suis preneuse.

**JeromeMATHIAS** · 27/02/2014, 15h26

Bonjour,

où en es-tu aujourd'hui ?

De façon général les travaux que tu envisage ne sont pas facile.
Tout le problème viens que comparer deux à deux des lignes transforme un problème en N en un problème en N²

Pour N=1000... N²=1 000 000 ça va...

Pour N=30 000 000 alors N²=900 000 000 000 000 et là ça ne va plus

Bon 30 000 000 quand même! Moi qui en était resté à 19 000 000 dans de vieux manuels

Ces considérations passées, tu peux optimiser un peu

déjà
1)

t1.NO_SEQ_CLI NOT = t2.NO_SEQ_CLItu peux lui préférer t1.NO_SEQ_CLI < t2.NO_SEQ_CLI *
ça ne mange pas de pain et ça fait déjà deux fois moins de calcul

2)

Ensuite on peut avoir l'oeil attiré par les conditions

Code :

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

1
2
3
	AND soundex(t1.NM_NAI_MER_CLI) = soundex(t2.NM_NAI_MER_CLI)
			AND soundex(t1.NM_APP_CLI)	   = soundex(t2.NM_APP_CLI)
			AND soundex(t1.PR_APP_CLI)	   = soundex(t2.PR_APP_CLI ))

et

Code :

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

1
2
3
4
5
6
				(max(find(%transform(t1.PR_APP_CLI), %transform(t2.PR_APP_CLI), 'it'),
             	 find(%transform(t2.PR_APP_CLI), %transform(t1.PR_APP_CLI), 'it')))>=1
			AND  (max(find(%transform(t1.NM_APP_CLI), %transform(t2.NM_APP_CLI), 'it'),
             	  find(%transform(t2.NM_APP_CLI), %transform(t1.NM_APP_CLI), 'it')))>=1
			AND (max(find(%transform(t1.NM_NAI_MER_CLI), %transform(t2.NM_NAI_MER_CLI), 'it'),
             	 find(%transform(t2.NM_NAI_MER_CLI), %transform(t1.NM_NAI_MER_CLI), 'it')))>=1

SOUNDEX et le type de transformations que tu fais dans %transform sont gourmandes en temps CPU , il y a lieu de les réaliser le moins de fois possible à mon sens.
Je pense que tu tirais profit de créer dans ton fichier initial et en "dur" les variables correspondantes ex : NM_NAI_MER_CLI_s=SOUNDEX(NM_NAI_MER_CLI) , et de même NM_NAI_MER_CLI_T=%transform(NM_NAI_MER_CLI) pour ne plus avoir les %transform et SOUNDEX() dans tes conditions mais des conditions avec NM_NAI_MER_CLI , NM_NAI_MER_CLI_s et NM_NAI_MER_CLI_T et faire de même avec les 2 autres variables de "NOMS" .

3)

Le gros du problème est d'identifier les doublons (problème qui est en N² ), alourdir les traitements en récupérant tout de suite des variables (et donc se trainer des gros fichiers en entrée de la SQL en plus de la complexité du croisement), je ne suis pas persuadé que ça arrange beaucoup les choses, je me contenterais déjà d'identifier les croisement pertinents (avec juste les variables nécessaires) et dans un second temps (dans une autre SQL) aller chercher les informations complémentaires

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
t1.NO_IDN_APP, 
          t1.CO_SOU_APP, 
          t1.CO_TYP_APP, 
          t1.IN_COR,
          t1.AD_LIG_1, 
		  t2.AD_LIG_1 AS AD_LIG_11, 
          t1.AD_LIG_2, 
          t2.AD_LIG_2 AS AD_LIG_21, 
          t1.CO_POS, 
		  t2.CO_POS AS CO_POS1, 
          t1.NO_TEL_COM, 
          t2.NO_TEL_COM AS NO_TEL_COM1, 
          t1.NO_TEL_RES,        
          t2.NO_TEL_RES AS NO_TEL_RES1,
		  CAT(t1.AD_LIG_1," " ,t1.AD_LIG_2) AS adresse_1,
		  CAT(t2.AD_LIG_1," " ,t2.AD_LIG_2) AS adresse_2

Ayant testé sur des données réelles proche des tiennes, j'ai déjà sur mes fichiers un gain de 6 avec la 1) et la 2).

La 3) pas testé (pas convaincu que ça augmente franchement les temps de traitement, mais me parait être une recommandation de bonne "hygiène" pour celui/celle qui est soucieux des temps de traitement).

Le fond du problème reste cependant complet, comment retomber sur des calculs plus de l'ordre de N ou N.Log(N) (à défaut on s'en contenterait bien) plutôt que des temps de l'ordre de N² ?

Une question, la date de naissance est une année de naissance ou une date complète ( avec le jour) ?

**MDsas** · 03/03/2014, 15h07

Bonjour Jérôme,

Merci de te pencher sur mon cas.
Tes commentaires me semble avoir du sens. Toute =fois je crains le résultat du " < " sachant qu'il s'agit de variables numériques est-ce vraiment équivalent au "NOT=" ?

J'étais rendu à 4h en tant d'éxécution et j'avais pris pour acquis qu'à défaut d'avoir mieux je stockais la table résultat afin de ne pas relancer cette étape car ce n'est que le début d'un long processus afin d'accumuler des "preuves" sur ce bassin de population pour savoir réellement s'il s'agit de double (notamment comparer les enfants, les conjoints, les gains, les adresses....).

Je testerai les améliorations proposées et reviendrai mentionner les évolutions.

À problème complexe ...solution complexe même si on souhaite fortement des solutions simples.

**JeromeMATHIAS** · 03/03/2014, 15h13

Bonjour,

si tes fonctions de comparaisons (tes clauses "where" ) sont symétriques alors avec un NOT = tu fais deux fois trop de calculs car tu compare (id1,id2) puis (id2,id1), pour savoir si les couples sont éventuellement redondant (et avec des fonctions de comparaison symétriques) il te suffit de vérifier (id1,id2) avec id1<id2 ou vice-versa d'où ma proposition de mettre < à la place du NOT = .

**MDsas** · 04/03/2014, 14h05

Bonjour,

Je voudrais confirmer que tes propositions réduisent bien le temps de calcul. Par contre en testant le avec le "<" je n'ai pas tout à fait le résultat escompté j'ai donc remis le " NOT =" sur lequel je procède après à un tri avec nodupkey (même là j'ai plus d'observations qu'àvec le "<"), j'ai pas creusé pour savoir lesquelles diffèrent

. Pour la suite du traitement j'ai besoin des deux entrées donc c'est pas grave en soi ( ab ou ba).

Donc je passe de 4 h à 1h30 environ.

Le code :

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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
 
 
%let trans_to='EEEEAAOOIIIUU';
 %let trans_from='ÊËÉÈÂÀÔÖÎÏÌÛÙ';
 %let remove="() -'.";
 
 /*********************************************************************/
 /* transform                                                         */
 /*                                                                   */
 /* Macro pour transformer une chaîne de caractères avant de faire une*/
 /* comparaison. La macro enlève les accents, les blancs et certains  */
 /* caractères spéciaux, question que ces éléments ne viennent pas    */
 /* gêner la comparaison.                                             */
 /*********************************************************************/
 %macro transform(string);
     strip(
           translate(
                     compress(upcase(&string.), &remove.),
                     &trans_to.,&trans_from.)
                     )
 %mend transform;
 
 DATA base_clipot;
 SET  SORTIE.EXTRACTION_APPELLATION;
	NM_NAI_MER_CLI_S = soundex(NM_NAI_MER_CLI);
	PR_APP_CLI_S =	  soundex(PR_APP_CLI);
	NM_APP_CLI_S =    soundex(NM_APP_CLI);
	NM_NAI_MER_CLI_T = %transform(NM_NAI_MER_CLI);
	PR_APP_CLI_T =%transform(PR_APP_CLI);
	NM_APP_CLI_T = %transform(NM_APP_CLI);
 
RUN;
 
 
 PROC SQL;
   CREATE TABLE clipot_potentiels_test AS 
   SELECT t1.NO_SEQ_CLI, 
		  t2.NO_SEQ_CLI AS NO_SEQ_CLI1, 
          t1.NO_IDN_APP, 
          t1.CO_SOU_APP, 
          t1.CO_TYP_APP, 
          t1.IN_COR,
          t1.NM_APP_CLI, 
		  t2.NM_APP_CLI AS NM_APP_CLI1, 
          t1.PR_APP_CLI, 
		  t2.PR_APP_CLI AS PR_APP_CLI1, 
          t1.DT_NAI_CLI, 
          t1.NM_NAI_MER_CLI, 
		  t2.NM_NAI_MER_CLI AS NM_NAI_MER_CLI1, 
          t1.AD_LIG_1, 
		  t2.AD_LIG_1 AS AD_LIG_11, 
          t1.AD_LIG_2, 
          t2.AD_LIG_2 AS AD_LIG_21, 
          t1.CO_POS, 
		  t2.CO_POS AS CO_POS1, 
          t1.NO_TEL_COM, 
          t2.NO_TEL_COM AS NO_TEL_COM1, 
          t1.NO_TEL_RES,        
          t2.NO_TEL_RES AS NO_TEL_RES1,
		  CAT(t1.AD_LIG_1," " ,t1.AD_LIG_2) as adresse_1,
		  CAT(t2.AD_LIG_1," " ,t2.AD_LIG_2) as adresse_2
      FROM base_clipot as t1 
 		JOIN base_clipot as t2
		ON 		t1.NO_SEQ_CLI not = t2.NO_SEQ_CLI 
			AND	t1.DT_NAI_CLI     = t2.DT_NAI_CLI 
			AND t1.CO_SEX_CLI	  = t2.CO_SEX_CLI
 
      WHERE		
 
			/*Bloc egalite parfaite*/
 
		(	t1.NM_NAI_MER_CLI = t2.NM_NAI_MER_CLI
			AND t1.NM_APP_CLI	  = t2.NM_APP_CLI
			AND t1.PR_APP_CLI	  = t2.PR_APP_CLI)   
 
	 OR
			/*Bloc similarité de son*/
 
		( 	t1.NM_NAI_MER_CLI_S    = t2.NM_NAI_MER_CLI_S
			AND t1.NM_APP_CLI_S	   = t2.NM_APP_CLI_S
			AND t1.PR_APP_CLI_S	   = t2.PR_APP_CLI_S )  
 
	OR
			/*Bloc similarité de son et un caractère de différend*/
 
 
		(	complev(strip(t1.NM_NAI_MER_CLI),strip(t2.NM_NAI_MER_CLI))<=1
			AND complev(strip(t1.NM_APP_CLI),strip(t2.NM_APP_CLI))<=1
			AND complev(strip(t1.PR_APP_CLI),strip(t2.PR_APP_CLI))<=1
			)  
 
	OR
				/*Bloc un inclut dans l'autre */
 
		(	
				(max(find(t1.PR_APP_CLI_T, t2.PR_APP_CLI_T, 'it'),
             	 	 find(t2.PR_APP_CLI_T, t1.PR_APP_CLI_T, 'it')))>=1
			AND (max(find(t1.NM_APP_CLI_T, t2.NM_APP_CLI_T, 'it'),
             	     find(t2.NM_APP_CLI_T, t1.NM_APP_CLI_T, 'it')))>=1
			AND (max(find(t1.NM_NAI_MER_CLI_T, t2.NM_NAI_MER_CLI_T, 'it'),
             	     find(t2.NM_NAI_MER_CLI_T, t1.NM_NAI_MER_CLI_T, 'it')))>=1
 
			)  
 
      ORDER BY t1.NO_SEQ_CLI;
 
QUIT;

Extrait première log :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
NOTE: Compressing data set SORTIE.CLIPOT_POTENTIELS decreased size by 77.47 percent. 
      Compressed is 1590 pages; un-compressed would require 7058 pages.
3                                                          Le Système SAS                          07:14 Thursday, February 27, 2014
 
NOTE: Table SORTIE.CLIPOT_POTENTIELS created, with 148198 rows and 25 columns.
 
111        
112        QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           4:02:52.34
      cpu time            3:51:29.84

Extrait de la log du programme optimisé :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
122              ORDER BY t1.NO_SEQ_CLI;
NOTE: Compressing data set WORK.CLIPOT_POTENTIELS_TEST decreased size by 77.47 percent. 
      Compressed is 1590 pages; un-compressed would require 7058 pages.
NOTE: Table WORK.CLIPOT_POTENTIELS_TEST created, with 148212 rows and 25 columns.
 
123        
124        QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           1:56:13.70
      cpu time            1:38:53.06

Merci.

Détection de clients en double selon plusieurs conditions dans une proc SQL [SQL]

SAS Base

Discussions similaires

Partager

Partager