Eradiquer les doublons couplés sur plusieurs tables

**kaos189** · 07/10/2008, 23h06

Salut à tous,

Je cherche depuis quelques temps à régler un problème de doublons: j'ai consulté avec interêt le chapitre qui leur sont consacrés mais malgré tout, je ne parvient pas à en venir à bout...

Etant un nouveau membre, je vais faire un effort pour respecter au mieux les règles de présentation afin que ma requète soit la plus claire possible...

Je travaille sur Oracle iSQL*Plus

Voici ma table DDL:

Code :

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

1
2
3
4
5
6
CREATE TABLE liste_agents
( agent_id NUMBER(4),
  first_name VARCHAR2(20),
  last_name VARCHAR2(20),
  birth_date DATE
);

...Et son contenu DDL:

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
INSERT INTO liste_agents VALUES (1,'Coty','Colt',TO_DATE('26-DEC-1985:13:46:08', 'DD-MON-YYYY:HH24:MI:SS'));

INSERT INTO liste_agents VALUES (2,'Diamond','Filmore',TO_DATE('15-JUL-1985:00:40:16', 'DD-MON-YYYY:HH24:MI:SS'));

INSERT INTO liste_agents VALUES (3,'Skye','Breck',TO_DATE('12-JUL-1967:09:08:35', 'DD-MON-YYYY:HH24:MI:SS'));

INSERT INTO liste_agents VALUES (4,'Hazlett','Molyneux',TO_DATE('23-NOV-1961:15:25:12', 'DD-MON-YYYY:HH24:MI:SS'));

INSERT INTO liste_agents VALUES (5,'Ava','Bullions',TO_DATE('26-MAY-1985:01:35:39', 'DD-MON-YYYY:HH24:MI:SS'));

INSERT INTO liste_agents VALUES (6,'Grayson','Lightfoot',TO_DATE('04-FEB-1958:03:51:39', 'DD-MON-YYYY:HH24:MI:SS'));

INSERT INTO liste_agents VALUES (7,'Holman','Boyd',TO_DATE('05-FEB-1957:18:43:13', 'DD-MON-YYYY:HH24:MI:SS'));

INSERT INTO liste_agents VALUES (8,'Fleta','Cheever',TO_DATE('18-JUN-1957:22:30:47', 'DD-MON-YYYY:HH24:MI:SS'));

INSERT INTO liste_agents VALUES (9,'Charlton','Carden',TO_DATE('24-JAN-1985:07:44:28', 'DD-MON-YYYY:HH24:MI:SS'));

INSERT INTO liste_agents VALUES (10,'Skelton','Marsh',TO_DATE('25-JAN-1934:04:58:02', 'DD-MON-YYYY:HH24:MI:SS'));

INSERT INTO liste_agents VALUES (11,'Aswin','Wilbraham',TO_DATE('02-FEB-1957:11:42:57', 'DD-MON-YYYY:HH24:MI:SS'));

INSERT INTO liste_agents VALUES (12,'Egbert','Biddulph',TO_DATE('20-MAR-1971:23:52:29', 'DD-MON-YYYY:HH24:MI:SS'));

INSERT INTO liste_agents VALUES (13,'Darthmouth','Huband',TO_DATE('25-FEB-1971:02:14:12', 'DD-MON-YYYY:HH24:MI:SS'));

INSERT INTO liste_agents VALUES (14,'Madison','Kirkpatrick',TO_DATE('07-MAR-1944:22:58:08', 'DD-MON-YYYY:HH24:MI:SS'));

INSERT INTO liste_agents VALUES (15,'Edda','Keith',TO_DATE('30-JUL-1949:02:21:40', 'DD-MON-YYYY:HH24:MI:SS'));

INSERT INTO liste_agents VALUES (16,'Landon','Cunningham',TO_DATE('03-APR-1971:12:56:46', 'DD-MON-YYYY:HH24:MI:SS'));

INSERT INTO liste_agents VALUES (17,'Radborne','Witherington',TO_DATE('18-MAY-1944:17:04:52', 'DD-MON-YYYY:HH24:MI:SS'));

INSERT INTO liste_agents VALUES (18,'Brooke','Kirtland',TO_DATE('26-APR-1985:21:13:10', 'DD-MON-YYYY:HH24:MI:SS'));

INSERT INTO liste_agents VALUES (19,'Salton','Vibbard',TO_DATE('17-MAY-1971:10:48:24', 'DD-MON-YYYY:HH24:MI:SS'));

INSERT INTO liste_agents VALUES (20,'Rockwell','Cam',TO_DATE('22-JUN-1957:06:53:46', 'DD-MON-YYYY:HH24:MI:SS'));

Ces valeurs ne sont pas exhaustives, mais bon c'est juste pour expliquer le principe...

Mon but est d'identifier tous les couples qui partagent la même année de naisssance:

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
SELECT

      ag1,

      ag2

FROM
(
    SELECT

                        a1.first_name||' '||a1.last_name ag1,

                        a2.first_name||' '||a2.last_name ag2

                  FROM
                              liste_agents a1

                        INNER JOIN
                              liste_agents a2

                        ON
to_char(a1.birth_date,'YYYY') = to_char(a2.birth_date,'YYYY')

                  WHERE
                        a1.agent_id <> a2.agent_id

)

ORDER BY         ag1;

Comme vous l'avez peut-être deviné je cherche à éliminer les doublons de couples qui apparaissent inéluctablement (en rouge):

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
ag1                               ag2
-----------------            -----------------
 
Aswin Wilbraham              Fleta Cheever  
Aswin Wilbraham              Holman Boyd  
Aswin Wilbraham              Rockwell Cam  
Ava Bullions                    Charlton Carden  
Ava Bullions                    Coty Colt  
Ava Bullions                    Brooke Kirtland  
Ava Bullions                    Diamond Filmore  
Brooke Kirtland                Diamond Filmore  
Brooke Kirtland                Ava Bullions  
Brooke Kirtland                Charlton Carden  
Brooke Kirtland                Coty Colt  
Charlton Carden               Diamond Filmore  
Charlton Carden               Coty Colt  
Charlton Carden               Ava Bullions  
Charlton Carden               Brooke Kirtland  
Coty Colt                        Charlton Carden  
Coty Colt                        Diamond Filmore  
Coty Colt                        Brooke Kirtland  
Coty Colt                        Ava Bullions  
Darthmouth Huband          Salton Vibbard  
Darthmouth Huband          Landon Cunningham  
Darthmouth Huband          Egbert Biddulph  
Diamond Filmore               Ava Bullions  
Diamond Filmore               Coty Colt 

Etc...

Je crois que tout y est.
Si quelq'un aurait une solution à proposer elle serait la bienvenue...

D'avance merci
a+

**deadoralive** · 08/10/2008, 10h30

tu peux rajouter une condition comme
le premier nom < deuxiéme

**orafrance** · 08/10/2008, 12h11

j'ai bien galéré

mais voila qui semble fonctionner :

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
WITH LA
     AS (SELECT   ROW_NUMBER() OVER (ORDER BY A1.FIRST_NAME ||' ' || A1.LAST_NAME) RANG,
                  A1.FIRST_NAME ||' ' || A1.LAST_NAME   AG1,
                  A2.FIRST_NAME ||' ' || A2.LAST_NAME   AG2
         FROM     LISTE_AGENTS A1
                  INNER JOIN LISTE_AGENTS A2
                    ON TO_CHAR(A1.BIRTH_DATE,'YYYY') = TO_CHAR(A2.BIRTH_DATE,'YYYY')
         WHERE    A1.AGENT_ID <> A2.AGENT_ID
   )
SELECT   A.AG1,A.AG2
FROM     LA A
WHERE    A.AG2 NOT IN (SELECT B.AG1
                       FROM   LA B
                       WHERE  B.RANG < A.RANG)
ORDER BY 1,2;

Explications :
J'affecte un numéro d'ordre aux couples par ordre croissant du 1er membre (ROW_NUMBER). Cela constitue mon ensemble LA (avec WITH). Ensuite, je traite cette ensemble en excluant (NOT IN) tous les deuxièmes noms du couple (A.AG2) qui apparaisse déja en premier (B.AG1) dans les couples précédents (B.RANG < A.RANG)

**deadoralive** · 08/10/2008, 14h19

Envoyé par orafrance

j'ai bien galéré

Pourquoi faire simple quand on peut faire compliqué?
Proverbe Shadokéens

**orafrance** · 08/10/2008, 15h07

ta solution ne fonctionne pas... fais un essaye si tu veux comme c'est simple

La critique est facile... etc...

**pacmann** · 08/10/2008, 15h10

Tout à fait d'accord deadoralive.

Je dirais même plus simplement remplacer le filtre par :

Code :

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

a1.agent_id < a2.agent_id

Orafrance :

Si tu veux t'amuser avec de la fonction analytique, j'ai une autre idée tordue :
1) trouver l'expression qui identifie une paire non ordonnée d'id, par exemple

Code :

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

CASE WHEN a1.id < a2.id THEN a1.id * 10000 + a2.id else a2.id * 10000 + a1.id END

Ou un peu plus fun :

Code :

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

 a1.id * power(10, (sign(a1.id - a2.id) + 1) / 2 * 4) + a2.id * power(10, (sign(a2.id - a1.id) + 1) / 2 * 4)

2) tu utilises ça dans la close PARTITION BY

3) tu mets a.id la clause ORDER BY (de OVER) pour avoir ton row_number

4) tu fais une simple sur requête pour filtrer et ne garder que ceux dont le row_number = 1

C'est effectivement beaucoup plus drôle quand c'est pas intuitif !

**orafrance** · 08/10/2008, 16h36

je m'incline...

après relecture c'est exactement ce que j'ai fait en me compliquant considérablement la vie

**kaos189** · 08/10/2008, 21h15

En effet c'est tout à fait le résultat que je voulais obtenir!

Il m'apparait maintenant évident que j'aurais vraiment eu du mal à y arriver
tout seul...

c'est vrai que c'est pas simple!

Je vais devoir élargir mes connaissances car je ne connaissais même pas la fonction WITH que tu as utilisé...

En tout cas merci à toi pour ton aide et la résolution de ce problème!

**salim11** · 10/10/2008, 15h11

Envoyé par orafrance

j'ai bien galéré

mais voila qui semble fonctionner :

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
WITH LA
     AS (SELECT   ROW_NUMBER() OVER (ORDER BY A1.FIRST_NAME ||' ' || A1.LAST_NAME) RANG,
                  A1.FIRST_NAME ||' ' || A1.LAST_NAME   AG1,
                  A2.FIRST_NAME ||' ' || A2.LAST_NAME   AG2
         FROM     LISTE_AGENTS A1
                  INNER JOIN LISTE_AGENTS A2
                    ON TO_CHAR(A1.BIRTH_DATE,'YYYY') = TO_CHAR(A2.BIRTH_DATE,'YYYY')
         WHERE    A1.AGENT_ID <> A2.AGENT_ID
   )
SELECT   A.AG1,A.AG2
FROM     LA A
WHERE    A.AG2 NOT IN (SELECT B.AG1
                       FROM   LA B
                       WHERE  B.RANG < A.RANG)
ORDER BY 1,2;

Explications :
J'affecte un numéro d'ordre aux couples par ordre croissant du 1er membre (ROW_NUMBER). Cela constitue mon ensemble LA (avec WITH). Ensuite, je traite cette ensemble en excluant (NOT IN) tous les deuxièmes noms du couple (A.AG2) qui apparaisse déja en premier (B.AG1) dans les couples précédents (B.RANG < A.RANG)

La requête fonctionne trés bien, c'est la même idée mais je l'ai optimisé

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
 
SQL>  WITH la AS
  2       (SELECT a1.first_name || ' ' || a1.last_name ag1, a1.agent_id,
  3               ROW_NUMBER () OVER (ORDER BY a1.first_name || ' '
  4                 || a1.last_name) rang,
  5               TO_CHAR (a1.birth_date, 'YYYY') dd
  6          FROM liste_agents a1)
  7  SELECT   la1.ag1, la2.ag1 ag2, la1.dd
  8      FROM la la1, la la2
  9     WHERE la1.dd = la2.dd
 10          AND la1.rang < la2.rang
 11  ORDER BY 3, 1, 2;
 
AG1                                       AG2                                       DD
----------------------------------------- ----------------------------------------- ----
Madison Kirkpatrick                       Radborne Witherington                     1944
Aswin Wilbraham                           Fleta Cheever                             1957
Aswin Wilbraham                           Holman Boyd                               1957
Aswin Wilbraham                           Rockwell Cam                              1957
Fleta Cheever                             Holman Boyd                               1957
Fleta Cheever                             Rockwell Cam                              1957
Holman Boyd                               Rockwell Cam                              1957
Darthmouth Huband                         Egbert Biddulph                           1971
Darthmouth Huband                         Landon Cunningham                         1971
Darthmouth Huband                         Salton Vibbard                            1971
Egbert Biddulph                           Landon Cunningham                         1971
 
AG1                                       AG2                                       DD
----------------------------------------- ----------------------------------------- ----
Egbert Biddulph                           Salton Vibbard                            1971
Landon Cunningham                         Salton Vibbard                            1971
Ava Bullions                              Brooke Kirtland                           1985
Ava Bullions                              Charlton Carden                           1985
Ava Bullions                              Coty Colt                                 1985
Ava Bullions                              Diamond Filmore                           1985
Brooke Kirtland                           Charlton Carden                           1985
Brooke Kirtland                           Coty Colt                                 1985
Brooke Kirtland                           Diamond Filmore                           1985
Charlton Carden                           Coty Colt                                 1985
Charlton Carden                           Diamond Filmore                           1985
 
AG1                                       AG2                                       DD
----------------------------------------- ----------------------------------------- ----
Coty Colt                                 Diamond Filmore                           1985
 
23 ligne(s) sélectionnée(s).
 
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=10 Card=1 Bytes=78
          )
 
   1    0   SORT (ORDER BY) (Cost=10 Card=1 Bytes=78)
   2    1     HASH JOIN (Cost=9 Card=1 Bytes=78)
   3    2       VIEW (Cost=4 Card=20 Bytes=780)
   4    3         WINDOW (SORT) (Cost=4 Card=20 Bytes=500)
   5    4           TABLE ACCESS (FULL) OF 'LISTE_AGENTS' (TABLE) (Cos
          t=3 Card=20 Bytes=500)
 
   6    2       VIEW (Cost=4 Card=20 Bytes=780)
   7    6         WINDOW (SORT) (Cost=4 Card=20 Bytes=520)
   8    7           TABLE ACCESS (FULL) OF 'LISTE_AGENTS' (TABLE) (Cos
          t=3 Card=20 Bytes=520)
 
 
 
 
SQL>

Eradiquer les doublons couplés sur plusieurs tables

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager