Casse Tête pour Associer des individus

Version imprimable

30/05/2018, 16h09
Troyan

1 pièce(s) jointe(s)

Casse Tête pour Associer des individus

Bonjour à tous,

je me prend la tête pour retrouver la même personne.
je m'explique j'ai 3 colonnes de différent canaux.
Email, TelFix, TelPort

J'aimerais pouvoir me flaguer dans une colonne toute les ligne qui sont en fait la même personne.
Pièce jointe 384625

En gros j'aimerais me rajouter la colonne FLAG car toutes ces lignes sont la même personne ?

HELP je vois pas comment faire sans partir sur une usine à gaz.
Peut-être que les récursive pourraient m'aider mais sur 3 colonnes je ne sais pas comment faire.

Merci d'avance
cH.
31/05/2018, 23h15
soazig

Bonjour,
Je n'ai pas compris ce que tu voulais faire et sur quel critère tu déterminais qu'il s'agissait d'une même personne.
Par ailleurs qu'est ce que ce flag.
Cordialement
Soazig
01/06/2018, 09h58
Troyan

Bonjour Soazig,

alors en fait chaque ligne est une entrée en base qui peut contenir 1,2 ou 3 canaux pas moins pas plus.
on cherche a déterminer qui est la même personne ma colonne Flag (F1) pour identifier que tout les F1 sont la même personnes.

comment on peux déterminer que c'est la même personne
parce que chaque colonne canal peux contenir le même canal qu'une autre ligne.

exemple :
la ligne 1 et deux on exactement les même canaux. donc même personne.
la ligne 3 même email donc même personne (F1 dans flag)
sur la ligne 4 on trouve la même email mais avec un nouveau téléphone qui m'ouvre un autre champ de recherche sur cette personne via un autre téléphone ....
etc...

En fait je crois qu'il faut faire une boucle jusqu'a ce qu'il n'y ai plus de nouveaux canaux pour cette personne.
mais du coup c'est une boucle inconnue qui peut faire 3 passage comme X

voilà l'idée.
cH.
01/06/2018, 14h37
Sebwar

Hello,
quelle est la volumétrie de la table ?
01/06/2018, 17h44
Waldar

Citation:

Envoyé par soazig

Je n'ai pas compris ce que tu voulais faire et sur quel critère tu déterminais qu'il s'agissait d'une même personne.

Je pense que j'ai compris.
On part de la première ligne, on voit qu'il y a un email et un téléphone.

Toutes les lignes de la table ayant cet email (E1) ou ce téléphone (T1) sont la même personne.
Mais on voit aussi un peu plus loin que E1 est associé à T2. Du coup c'est aussi la même personne (via E1), et tous ceux qui ont T2 comme téléphone sont aussi la même personne.
Plus loin, TE1 est associé à T2, du coup toujours la même personne, et cetera.

Je pense qu'une requête récursive ne suffit pas, il faut faire un programme qui va identifier une personne qui sera exécuté tant qu'il y a des lignes non flagées.
Une boucle WHILE en T-SQL avec une requête récursive devrait faire l'affaire.

En tout cas au niveau du modèle, c'est très mauvais ; je pense enfoncer une porte ouverte.

Bonjour,

Je pense que ceci doit fonctionner (j'ai ajouté quelques données au jeu d'essai fourni pour obtenir d'autres groupes) :

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
 
WITH LaTable(email, fixe, port) AS (
    SELECT		'E1', NULL, 'T1'
UNION ALL SELECT	'E1', NULL, 'T1'
UNION ALL SELECT	'E1', NULL, NULL
 
UNION ALL SELECT	'E20', NULL, 'T10' 
 
UNION ALL SELECT	'E1', NULL,  'T2'
UNION ALL SELECT	NULL, 'TE1', 'T2'
UNION ALL SELECT	'E2', NULL, 'T2'
UNION ALL SELECT	'E3', NULL, 'T2'
UNION ALL SELECT	'E2', 'TE1', NULL
UNION ALL SELECT	NULL, 'TE1', 'T3'
UNION ALL SELECT	NULL, NULL, 'T3'
 
UNION ALL SELECT	'E10', NULL, 'T10'
 
UNION ALL SELECT	'E30', NULL, 'T30'
UNION ALL SELECT	'E40', NULL, 'T40'
),
N AS (
    SELECT *, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS num
    FROM LaTable
), Rec AS (
 
    SELECT Num, email, fixe , port, Num AS Ori, CAST(CONCAT(';', Num, ';') AS VARCHAR(MAX)) AS chemin 
    FROM N
    UNION ALL
    SELECT N.num, N.email, N.fixe, N.port, rec.Ori, CAST(CONCAT(rec.chemin, N.num, ';')AS VARCHAR(MAX))
    FROM	  Rec
    INNER JOIN N 
	   ON N.email = Rec.email
	   OR N.fixe = Rec.fixe
	   OR N.port = Rec.port
    WHERE Rec.chemin NOT LIKE CONCAT('%;',N.num , ';%')
)
 
SELECT Num, email, fixe, port, MIN(ori) AS Groupe
FROM Rec
GROUP BY Num, email, fixe, port
ORDER BY groupe, num

04/06/2018, 11h02
Troyan

Citation:

Envoyé par Waldar

Je pense que j'ai compris.
On part de la première ligne, on voit qu'il y a un email et un téléphone.

Toutes les lignes de la table ayant cet email (E1) ou ce téléphone (T1) sont la même personne.
Mais on voit aussi un peu plus loin que E1 est associé à T2. Du coup c'est aussi la même personne (via E1), et tous ceux qui ont T2 comme téléphone sont aussi la même personne.
Plus loin, TE1 est associé à T2, du coup toujours la même personne, et cetera.

Je pense qu'une requête récursive ne suffit pas, il faut faire un programme qui va identifier une personne qui sera exécuté tant qu'il y a des lignes non flagées.
Une boucle WHILE en T-SQL avec une requête récursive devrait faire l'affaire.

En tout cas au niveau du modèle, c'est très mauvais ; je pense enfoncer une porte ouverte.

Salut Waldar oui c'est exactement ça.
en fait ce n'est pas vraiment un modele.
Il s'agit de base autonome d'envoi de campagnes et normalement elle on leur vie propre pendant un certain temps (temps de la durée de la campagne)
Y'en a donc environ 100.000 base mySql
Et la, on va dire que la GDPR secouent tout le monde et il me demande de pouvoir restituer les infos d'envoi d'un gars qui en fait la demande.

Donc j'ai parsé toutes les bases pour rassembler des infos dans une table qui me permette de retrouver les personnes et les infos de chaque campagnes, en version simple c'est ce que j'ai donné comme table.
quelques identifiants et nom de bases et serveur plus les canaux, ceux qui me pose probleme pour retrouver la même personne d'ou ma question...
et c'est là que je me suis dis mais comment retrouver quelle info peut appartenir à la meme personne dans ce gros bordel, d'ou ma question...
02/07/2018, 08h23
JeitEmgie

L'approche récursive fonctionne mais elle risque d'exploser si le volume de données à traiter est important et le graphe des doublons un peu complexe.
Le petit exemple ci-dessus génère déjà un graphe de 2798 lignes (taille de Rec avant le GROUP BY).

Il est possible de réduire fortement le graphe est faisant un pré-traitement :
un champ "groupe" doit être ajouté à la table, initialisé avec le row_number() comme dans le query récursif
ensuite en faisant N updates de champ - 1 par colonne susceptible d'être commune, donc ici 3: email, telfixe, telport -
pour regrouper les cas de premier niveau (avec un over partition by ...) : ceux qui ont le même attribut recevrons le min(Groupe ID)
et ensuite seulement appliquer le query récursif proposé ci-dessus.

Si cela ne suffit pas pour éviter l'explosion : faire une procédure qui boucle sur les N updates, tant que le nombre de lignes modifiées est différent de 0.
Dans l'exemple de aieeeuuuuu, cela arriverait à la troisième boucle, donc le coût serait de 1 (init du rownum) + 9 updates (3 x 3 colonnes).