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

Version imprimable

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:

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

salut omelhor.

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

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

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:

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.

Salut skuatamad.

Je n'avais pas envisagé cette solution. Merci de nous l'avoir communiqué ! :D
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:

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:

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:

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

@+

29/08/2015, 22h26
omelhor

cela fonctionne , merci beaucoup