recherche de doublons (même id présent le même jour)

**omelhor** · 20/08/2015, 00h41

Bonjour,

J'ai une table ou sont insèrés des matchs entre 1 équipes.
Je suis tombé sur une équipe qui jouait 2 fois le même jour !!!

Du coup je cherche a faire une requête qui soit capable de repérer les équipes jouant 2 fois le même jour. Mais j'ai aucune idée de comment m'y prendre

La table est comme ceci:

Code :

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

1
2
3
4
5
6
7
8
 
id	|	club1	       |	club2 	|	date
1	|	125		|	65		| 01-10-2016		id 125 le 01-10-2016
2	|	58		|	88		| 11-02-2016
3	|	12		|	55		| 04-03-2016
4	|	78		|	544		| 01-10-2016
5	|	888		|	125		| 01-10-2016		id 125 le 01-10-2016
6	|	555		|	16		| 01-08-2016

Pouvez-vous m'aiguiller, c'est une table qui fait un peut plus de 160 000 lignes !!! avec pas moins de 8000 équipes
L'idée ici étais de retrouver le club 125 qui jouait 2 fois le même jour, en sachant qu'une fois il étais le club1 et dans l'autre match le club2.
Mais cela peut très bien être club1 dans les 2 matchs

Merci beaucoup pour votre aide

**Artemus24** · 24/08/2015, 09h41

salut omelhor.

Avec un peu de retard, je te donne un possible solution à ton problème.

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
--------------
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 `test`
--------------
 
--------------
CREATE TABLE `test`
(
  `id`     integer unsigned  NOT NULL,
  `club1`  integer unsigned  NOT NULL,
  `club2`  integer unsigned  NOT NULL,
  `date`   date              NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`id`,`club1`,`club2`,`date`) VALUES
(1, 125,  65, '2016-10-01'),
(2, 58,   88, '2016-02-11'),
(3, 12,   55, '2016-03-04'),
(4, 78,  544, '2016-10-01'),
(5, 888, 125, '2016-10-01'),
(6, 555, 16,  '2016-08-01')
--------------
 
--------------
select * from `test`
--------------
 
+----+-------+-------+------------+
| id | club1 | club2 | date       |
+----+-------+-------+------------+
|  1 |   125 |    65 | 2016-10-01 |
|  2 |    58 |    88 | 2016-02-11 |
|  3 |    12 |    55 | 2016-03-04 |
|  4 |    78 |   544 | 2016-10-01 |
|  5 |   888 |   125 | 2016-10-01 |
|  6 |   555 |    16 | 2016-08-01 |
+----+-------+-------+------------+
--------------
select * from `test` as tb1
where exists (
        select 1 from `test` as tb2
        where (tb2.club1  = tb1.club1
        or     tb2.club1  = tb1.club2
        or     tb2.club2  = tb1.club1
        or     tb2.club2  = tb1.club2)
        and    tb2.id    != tb1.id
        and    tb2.date   = tb1.date
        )
--------------
 
+----+-------+-------+------------+
| id | club1 | club2 | date       |
+----+-------+-------+------------+
|  1 |   125 |    65 | 2016-10-01 |
|  5 |   888 |   125 | 2016-10-01 |
+----+-------+-------+------------+
--------------
select * from `test` as tb1, `test` as tb2
where (tb2.club1  = tb1.club1
or     tb2.club1  = tb1.club2
or     tb2.club2  = tb1.club1
or     tb2.club2  = tb1.club2)
and    tb2.id    != tb1.id
and    tb2.date   = tb1.date
--------------
 
+----+-------+-------+------------+----+-------+-------+------------+
| id | club1 | club2 | date       | id | club1 | club2 | date       |
+----+-------+-------+------------+----+-------+-------+------------+
|  5 |   888 |   125 | 2016-10-01 |  1 |   125 |    65 | 2016-10-01 |
|  1 |   125 |    65 | 2016-10-01 |  5 |   888 |   125 | 2016-10-01 |
+----+-------+-------+------------+----+-------+-------+------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

@+

**skuatamad** · 24/08/2015, 10h31

Généralement ça se fait avec une agrégation filtrée avec la clause HAVING.
Ici il faut utiliser au préalable UNION ALL pour avoir club1 et club2 dans la même colonne :

Code :

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

1
2
3
4
5
6
7
8
9
10
select t.club, t.date, count(*)
  from (
select club1 as club, date
  from la_table
 union all
select club2 as club, date
  from la_table
       ) t
 group t.club, t.date
having count(*) > 1

C'est potentiellement plus performant que des OR, mais à tester.

**Artemus24** · 24/08/2015, 11h55

Salut skuatamad.

Je n'avais pas envisagé cette solution. Merci de nous l'avoir communiqué !

J'ai fait un explain sur chacune des trois solutions, afin de comparer les performances et voici ce que ça donne :

--> première solution :

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
explain select * from `test` as tb1
where exists (
        select 1 from `test` as tb2
        where (tb2.club1  = tb1.club1
        or     tb2.club1  = tb1.club2
        or     tb2.club2  = tb1.club1
        or     tb2.club2  = tb1.club2)
        and    tb2.id    != tb1.id
        and    tb2.date   = tb1.date
        )
--------------
 
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | tb1   | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
|  2 | DEPENDENT SUBQUERY | tb2   | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+

--> deuxième solution :

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
--------------
explain select * from `test` as tb1, `test` as tb2
where (tb2.club1  = tb1.club1
or     tb2.club1  = tb1.club2
or     tb2.club2  = tb1.club1
or     tb2.club2  = tb1.club2)
and    tb2.id    != tb1.id
and    tb2.date   = tb1.date
--------------
 
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | tb1   | ALL  | NULL          | NULL | NULL    | NULL |    6 | NULL                                               |
|  1 | SIMPLE      | tb2   | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+

--> et ta solution :

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
--------------
explain  select t.club, t.date, count(*)
from (
        select club1 as club, date      from test
        union all
        select club2 as club, date      from test
    ) as t
group by t.club, t.date
having count(*) > 1
--------------
 
+----+--------------+------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+--------------+------------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | PRIMARY      | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |   12 | Using temporary; Using filesort |
|  2 | DERIVED      | test       | ALL  | NULL          | NULL | NULL    | NULL |    6 | NULL                            |
|  3 | UNION        | test       | ALL  | NULL          | NULL | NULL    | NULL |    6 | NULL                            |
| NULL | UNION RESULT | <union2,3> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary                 |
+----+--------------+------------+------+---------------+------+---------+------+------+---------------------------------+

Ta solution, skuatamad, admet quatre niveaux, alors que je n'en ai que deux !

Pour améliorer les performances, il faut mettre des index sur les colonnes 'club1' et 'club2' ainsi que sur la 'date'.

P.S.: j'ai oublié de mettre le test sur la date. J'ai corrigé cette bévue dans mon précédent message.

@+

**omelhor** · 29/08/2015, 22h26

cela fonctionne , merci beaucoup

recherche de doublons (même id présent le même jour)

Requêtes MySQL

Discussions similaires

Partager

Partager