Discussion :

[BigDatas]

Bonjour,
J'ai un petit souci de dédoublonnage lorsque je veux exclure certains regroupements d'id connus.
Je m'explique, imaginons que j'ai une table_user

id_user	email	nom
1	aaaa@mail.com	tata
4	aaaa@mail.com	toto
8	aaaa@mail.com	tutu
101	bbbb@mail.com	bobo
1012	aaaa@mail.com	titi

Donc pour dédoublonner cette table en utilisant l'email je fais :

Code :

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

1
2
3
4
SELECT COUNT(DISTINCT id), email, GROUP_CONCAT( id ORDER BY id ) AS ids
FROM table_user 
GROUP BY email
HAVING COUNT(DISTINCT id)>1;

Ce qui retournerait un truc du genre :

COUNT(DISTINCT id)	email	ids
4	aaaa@mail.com	1,4,8,1012

Ok mais ce que je souhaiterais c'est pouvoir exclure du rapprochement des éléments identifiés dans une autre table
Exemple avec une table : user_no_doublon

id	users_no_doublon
1	1.8
2	1.1012

En fait cette table permettrait d'indiquer que :
- le user id 1 ne peut etre rapprocher avec le user id 8
- le user id 1 ne peut etre rapprocher avec le user id 1012

Et en arrivant à joindre ces exclusions à la première requete obtenir :

COUNT(DISTINCT id)	email	ids
2	aaaa@mail.com	1,4

et les 2 users concernés seraient uniquement les users : 1 et 4

Voila j'espère être assez clair, merci d'avance pour votre aide

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
CREATE TABLE IF NOT EXISTS `table_users` (
  `id` int(11) NOT NULL,
  `email` varchar(150) NOT NULL,
  `nom` varchar(150) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `email` (`email`),
  KEY `nom` (`nom`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
INSERT INTO `table_users` (`id`, `email`, `nom`) VALUES
(1, 'aaaa@mail.com', 'tata'),
(4, 'aaaa@mail.com', 'toto'),
(8, 'aaaa@mail.com', 'tutu'),
(101, 'bbbb@mail.com', 'bobo'),
(1012, 'aaaa@mail.com', 'titi');
 
CREATE TABLE IF NOT EXISTS `user_no_doublon` (
  `id` int(11) NOT NULL,
  `users_no_doublon` varchar(150) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `email` (`users_no_doublon`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
INSERT INTO `user_no_doublon` (`id`, `users_no_doublon`) VALUES
(2, '1.1012'),
(1, '1.8');

[CinePhil]

Le problème, c'est votre modélisation de données !
Une liste de valeurs dans une seule colonne n'est jamais facile à exploiter.

Votre cas réel est-il de la même forme, de type [id][point][id à rapprocher du premier id] ?

[BigDatas]

Bonjour,
Pour le moment je suis en test et recherche d'une solution viable, donc j'ai pas spécialement de format spécifique.
On peut effectivement enregistrer les ids de cette manière ou d'une autre :

-- user_no_doublon --

id	user1	user2
1	1	8
2	1	1012

ou

id	user1	user2
1	8	1
2	1012	1

En gros cette requête se résumerait à

Trouve moi les users en doublon via leur mail avec au moins 2 users par regroupement
---- Et parmi ces users assures toi qu'ils ne soient pas recensés dans une des combinaisons de la table user_no_doublon

Un truc dans le genre quoi ?
J'ai pas de contrainte de format, si vous avez plus efficace je suis preneur !

[CinePhil]

Ouf ! Je préfère nettement cette modélisation !

Trouve moi les users en doublon via leur mail avec au moins 2 users par regroupement

Code :

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

1
2
3
4
SELECT u.email, COUNT(*) AS nb_id_users
FROM table_users u
GROUP BY u.email
HAVING COUNT(*) > 1

Et parmi ces users assures toi qu'ils ne soient pas recensés dans une des combinaisons de la table user_no_doublon

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT u.email, COUNT(*) AS nb_id_users
FROM table_users u
WHERE NOT EXISTS
(
	SELECT *
	FROM user_no_doublon d
	WHERE d.user1 = u.id_user
		OR d.user2 = u.id_user
)
GROUP BY u.email
HAVING COUNT(*) > 1

[Artemus24]

Salut à tous.

La solution proposée par CinePhil ne fonctionne pas. Pourquoi ?

1) le cas du having ne doit pas se faire après l'exclusion mais avant.
Car si nous avons deux lignes, et que nous devons en rejeter une, il en restera forcement une.
Et celle-ci ne sera pas affichée !

2) le résultat des exclusions dépend de la façon dont la table "users_no_doublon" a été construite !

Une piste à suivre est donnée par BigDatas :

En fait cette table permettrait d'indiquer que :
- le user id 1 ne peut être rapprocher avec le user id 8
- le user id 1 ne peut être rapprocher avec le user id 1012

En regardant son tableau, je crois comprendre que la relation se fait dans la colonne "no_doublon".
Et que la colonne "id" ne sert à rien, sinon à identifier et rendre unique les lignes dans la table "users_no_doublon".

Or si le couple à traiter est (1;8), je peux lire cela dans les deux sens, à savoir :
--> si je rencontre le user 1, je dois exclure le user 8.
--> si je rencontre le user 8, je dois exclure le user 1.
Autrement dit, dois-je exclure aussi bien le user 1 que le user 8 ?

Mais d'après le résultat attendu par BigDatas, il conserve le user 1 mais exclue le user 8.
Ce qui implique donner un sens dans la lecture quand on a le couple (1 ==> 8).

3) comment dois-je construire la table "users_no_doublon" ?

Il nous faut deux colonnes, à savoir une colonne servant de point d'entrée (la clef) et une colonne servant à exclure.
Ce qui dans l'exemple de BigDatas est :

Code :

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

1
2
3
4
5
6
+------+-----------+
| clef | exclusion |
+------+-----------+
|   1  |       8   |
|   1  |    1012   |
+------+-----------+

Sauf que, nous constatons que la colonne "clef" ne peux pas servir de clef primaire. Pourquoi ? Parce que nous n'avons pas unicité !
Dans ce cas, l'unicité devra se faire sur le couple (clef, exclusion).

4) voici ce que je propose :

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
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `user`
--------------
 
--------------
CREATE TABLE `user`
( `id`    integer  unsigned  NOT NULL primary key,
  `email` char(20)           NOT NULL,
  `nom`   char(04)           NOT NULL,
  index `idx_1` (`email`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `user` (`id`, `email`, `nom`) VALUES
  (   1, 'aaaa@mail.com', 'tata'),
  (   4, 'aaaa@mail.com', 'toto'),
  (   8, 'aaaa@mail.com', 'tutu'),
  ( 101, 'bbbb@mail.com', 'bobo'),
  (1012, 'aaaa@mail.com', 'titi')
--------------
 
--------------
select * from `user`
--------------
 
+------+---------------+------+
| id   | email         | nom  |
+------+---------------+------+
|    1 | aaaa@mail.com | tata |
|    4 | aaaa@mail.com | toto |
|    8 | aaaa@mail.com | tutu |
|  101 | bbbb@mail.com | bobo |
| 1012 | aaaa@mail.com | titi |
+------+---------------+------+
--------------
DROP TABLE IF EXISTS `no_doublon`
--------------
 
--------------
CREATE TABLE `no_doublon`
( `clef`       integer unsigned  NOT NULL,
  `exclusion`  integer unsigned  NOT NULL,
  primary key (`clef`,`exclusion`),
  index `idx_2` (`exclusion`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `no_doublon` (`clef`,`exclusion`) VALUES
(1, 8),
(1, 1012)
--------------
 
--------------
select * from `no_doublon`
--------------
 
+------+-----------+
| clef | exclusion |
+------+-----------+
|    1 |         8 |
|    1 |      1012 |
+------+-----------+
--------------
select    email,
          count(*) as nbre,
          group_concat(id order by `id`) as ids
    from  `user`
group by  email
  having  nbre > 1
--------------
 
+---------------+------+------------+
| email         | nbre | ids        |
+---------------+------+------------+
| aaaa@mail.com |    4 | 1,4,8,1012 |
+---------------+------+------------+
--------------
select      t2.email,
            group_concat(t2.id order by t2.id) as ids
 
      from  (  select  email,
                       count(*) as nbre
                 from  `user`
             group by  email
               having  nbre > 1
            ) as t1
 
inner join  `user` as t2
        on  t2.email = t1.email
 
     where  not exists (  select  1
                            from  `no_doublon`as t3
                           where  t3.exclusion = t2.id
                             and  exists (  select  1
                                              from  `user` as t4
                                             where  t4.email = t1.email
                                               and  t4.id    = t3.clef
                                         )
                       )
 
  group by  email
  order by  email
--------------
 
+---------------+------+
| email         | ids  |
+---------------+------+
| aaaa@mail.com | 1,4  |
+---------------+------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

C'est une requête plus compliquée que celle de CinePhil, car il a oublié de faire le lien entre les id à exclure et les id qui ne doivent pas l'être.

@+

[BigDatas]

Bonjour, merci pour votre aide !

En vous relisant je me rends compte que c'est pas si évident d'être clair sur une problématique qui semble toute simple...
On est pas loin MAIS !

Or si le couple à traiter est (1;8), je peux lire cela dans les deux sens, à savoir :
--> si je rencontre le user 1, je dois exclure le user 8.
--> si je rencontre le user 8, je dois exclure le user 1.
Autrement dit, dois-je exclure aussi bien le user 1 que le user 8 ?

Oui en fait, il faudrait exclure 1 comme 8 car cette association doit ce lire dans les 2 sens

Les clés --> 1-8 ou 8-1 et 1-1012 ou 1012-1 doivent forcer le fait de ne jamais considérer les regroupements : 1 avec 8 et 1 avec 1012

Mais si j'exclue 1 il nous reste plus que 4 et ce n'est pas bon car au final il devrait me garder les rapprochements :

Code :

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

1
2
3
4
5
6
7
8
1-4 ou 4-1
4-8 ou 8-4
4-1012 ou 1012-4
8-1012 ou 1012-4
#ELEMENTS ELIMINES-->
1-8 ou 8-1
1-1012 ou 1012-1
#<--ELEMENTS ELIMINES

Donc je me rends compte que ce que je voulais initialement comme résultat n'est pas bon ->

COUNT(DISTINCT id)	email	ids
2	aaaaa@mail.com	1,4

Car en fait ce que je recherche ceux sont les associations possibles autour d'une meme clé et cela devrait me remonter ->

nb_association	email	associations_possibles
4	aaaaa@mail.com	1-4, 4-8, 4-1012, 8-1012

En gros je me demande si je ne dois pas faire une premiere requête :

Code :

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

1
2
3
4
SELECT COUNT(DISTINCT id), email, GROUP_CONCAT( id ORDER BY id ) AS ids
FROM table_user 
GROUP BY email
HAVING COUNT(DISTINCT id)>1;

qui me remonterait :

COUNT(DISTINCT id)	email	ids
4	aaaaa@mail.com	1,4,8,1012

Et pour chaque lignes remontées un algo traite les rapprochements possibles avec les éléments remontés le champ 'ids' ? :

Code :

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

1
2
3
4
1-4 ou 4-1
4-8 ou 8-4
4-1012 ou 1012-4
8-1012 ou 1012-4

Je voulais éviter cette méthode et le traduire directement en requête sql pour éviter de faire des boucles dans des boucles...

Bonne journée

[Artemus24]

Salut Bigdatas.

En vous relisant je me rends compte que c'est pas si évident d'être clair sur une problématique qui semble toute simple...

Nous decouvrons votre problème en lisant votre sujet. Selon vos explications, nous pouvons comprendre autre chose que ce que vous cherchez à faire.
Il n'y a rien de simple en informatique car il faut être rigiureux et être clair dans la rédaction d'un problème.

il faudrait exclure 1 comme 8 car cette association doit ce lire dans les 2 sens

Ce n'est pas ce que j'ai compris en vous lisant, puisque votre exemple me confirmait de conserver id=1 et exclure id=8.

Mais même cette explication reste encore floue.
Admettons que nous contruisons la table "users_no_doublon" où chaque ligne est un couple.
Si je rencontre le couple (1;8) je dois exclure les deux id.
Mais si je rencontre l'un des deux, par exemple 8, dois-je l'exclure ? A priori, je pense que non.

Dans ce cas, la table doit possèder deux colonnes :

Code :

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

1
2
3
4
5
6
7
8
9
10
DROP TABLE IF EXISTS `no_doublon`;
 
CREATE TABLE `no_doublon`
( `id1`  integer unsigned  NOT NULL,
  `id2`  integer unsigned  NOT NULL,
  primary key (`id1`,`id2`),
  index `idx` (`id2`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;

Et vous devez faire deux "not exists".

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
--------------
select      t2.email,
            group_concat(t2.id order by t2.id) as ids
 
      from  (  select  email,
                       count(*) as nbre
                 from  `user`
             group by  email
               having  nbre > 1
            ) as t1
 
inner join  `user` as t2
        on  t2.email = t1.email
 
     where  not exists (  select  1
                            from  `no_doublon`as t3
                           where  t3.id2 = t2.id
                       )
       and  not exists (  select  1
                            from  `no_doublon`as t3
                           where  t3.id1 = t2.id
                       )
 
  group by  email
  order by  email
--------------
 
+---------------+------+
| email         | ids  |
+---------------+------+
| aaaa@mail.com | 4    |
+---------------+------+

Maintenant, les poins que vous devez étudier :

1) dois-je rechercher tous les emails en doublon ?

2) que dois je exclure ?

3) qu'est-ce que j'attends comme résultat ?

@+