Besoin d'une fonction pour trouver des éléments identiques dans une table

**Jean-Marc68** · 27/10/2009, 20h51

Soit une table contenant un champs nolot, un champ nom et un champs prenom. J'ai besoin, en entrant un nolot en argument de la fonction, quels sont tous les lots ayant toutes les mêmes valeurs pour nom et prénom.

Je pense qu'un exemple serait utile.

Soit la table

J'ai besoin de sortir, pour formule(4000) la liste suivante

nolot
-----
4000
4004

4004 doit sortir parce qu'il contient Albert Einstein ET Mileva Einstein, tout comme 4000 qui est en argument de la formule.
4002 ne devra pas sortir parce qu'il ne contient pas Mileva Einstein.

Je n'ai pas encore fais assez de formules pour PostgreSQL pour réussir à faire celle là, mais j'en ai besoin.
Je sêche, aussi je fais appel à vous.

Merci de vos z'avis z'avisés,
Jean-Marc

**SQLpro** · 27/10/2009, 23h05

Il s'agit d'un cas de division relationnelle assez classique. Lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/divrelationnelle/

A +

**Jean-Marc68** · 28/10/2009, 16h20

bonjour et merci de ce tuto exemplaire.

Je sêche toutefois encore. Je suis dans le dernier cas : le cas de la division exacte.
Je n'arrive pas à ramener tout cela à une seule table source.
Dans votre exemple d'appareils, admettons qu'il faille sortir tous les appareils contenant les mêmes composants, ni plus, ni moins, et que, de plus, un composant pourrait se retrouver plusieurs fois.
Pour se ramener à mon cas, il se peut qu'un lot ait 2 Albert Einstein (car il y a des lots ou des personnes différentes portent le même nom et prénom) et une Milesa Einstein. Il faudrait donc trouver tous les lots contenant 2 Albert Einstein et 1 Milesa Einstein, tout en sachant aussi que le nom et le prénom sont dans des champs séparés. Je ne sais pas si, pour simplifier, il serait bon de concaténer le nom et le prénom ou si cela, dans certains cas, pourrait nous induire en erreur.
En fait, il me faut trouver tous les lots ayant toutes les mêmes personnes que le lot dont le numéro est entré en argument dans la fonction.

Merci toutefois de l'attention que vous portez à ma recherche,

JM

**estofilo** · 02/11/2009, 13h39

Il y a une solution relativement simple en passant par des tableaux intermédiaires. Simple mais peu performante, à voir en fonction des volumes si c'est un problème ou pas.

L'idée est de placer dans un tableau dynamique tous les couples nom-prénom correspondant à chaque lot particulier, par une requête du style:

Code :

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

SELECT nolot, array_agg(row(prenom, nom)) AS liste FROM lot GROUP BY nolot

A combiner avec l'opérateur d'égalité des tableaux pour trouver n'importe quel lot qui a exactement le même contenu qu'un autre lot, par exemple pour le 4000:

Code :

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

1
2
3
select nolot from 
  (select nolot, array_agg(row(prenom, nom)) AS liste from lot group by nolot) s1
 where liste=(select array_agg(row(prenom, nom)) from lot where nolot=4000);

La fonction array_agg est dispo en standard à partir de PG 8.4 seulement, mais si version antérieure il suffit de la créer, voir l'exemple de array_accum dans la documentation.

**SQLpro** · 02/11/2009, 18h24

Visiblement mes articles ne vous ont pas inspiré. Pour autant la solution y figure bien :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE JeanMarc68 
(nolot int, prenom varchar(8), nom varchar(8));
 
INSERT INTO JeanMarc68 VALUES (4000, 'Albert', 'Einstein');
INSERT INTO JeanMarc68 VALUES (4000, 'Mileva', 'Einstein');
INSERT INTO JeanMarc68 VALUES (4001, 'Marie',  'Curie');
INSERT INTO JeanMarc68 VALUES (4001, 'Pierre', 'Curie');
INSERT INTO JeanMarc68 VALUES (4002, 'Albert', 'Einstein');
INSERT INTO JeanMarc68 VALUES (4003, 'Barack', 'Obama');
INSERT INTO JeanMarc68 VALUES (4004, 'Albert', 'Einstein');
INSERT INTO JeanMarc68 VALUES (4004, 'Mileva', 'Einstein ');

-- 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
WITH T AS
(SELECT nom, prenom
 FROM   JeanMarc68
 INTERSECT
 SELECT nom, prenom
 FROM   JeanMarc68
 WHERE  nolot = 4000)
SELECT nolot
FROM   JeanMarc68 AS JM
       INNER JOIN T
             ON JM.nom = T.nom
                AND JM.prenom = T.prenom
GROUP BY nolot                
HAVING COUNT(*) = (SELECT COUNT(*)
                   FROM   T)

Code :

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

1
2
3
4
5
 
nolot
-----------
4000
4004

Et si vous voulez apprendre le SQL, mon site, comme mes bouquins, peuvent vous y aider !

A +

**estofilo** · 03/11/2009, 10h21

La requête que tu proposes ne convient pas à cause de la contrainte qui est mentionnée sur les doublons de prénom et nom:

Pour se ramener à mon cas, il se peut qu'un lot ait 2 Albert Einstein (car il y a des lots ou des personnes différentes portent le même nom et prénom) et une Milesa Einstein. Il faudrait donc trouver tous les lots contenant 2 Albert Einstein et 1 Milesa

**SQLpro** · 03/11/2009, 18h13

1) si vous ne nous donnez pas dès la départ tous les éléments c'est stupide de votre part. Or je ne voit nullement mentionné ce que vous dite et le jeu de données comme le résultat correspond bien. J'ai donc la désagréable l'impression d'avoir été pris pour un con et d'avoir travaillé pour vous en vain !
2) si votre table admet des doublons purs, votre requête est mathématiquement insolvable en l'état.

A +

**Jean-Marc68** · 03/11/2009, 20h14

Bonjour et merci à tous les 2 de vous être penchés sur mon cas.

Pour SQLPro, c'était estofilo qui vous a répondu en dernier et je pense sincèrement que personne ne vous prend pour un con ici. Pour une référence certainement, pour un con jamais.
Je suis en effet un autodidacte dans le domaine et je ne connaissais pas votre site que j'ai pû découvrir grâce à votre première réponse. J'y vais régulièrement m'instruire depuis ces quelques jours.

Pour la string SQL, je pense en effet que vous avez raison et que la solution était décrite. Je n'ai juste pas été capable de la monter moi-même. C'est l'intersect de la table sur elle même qui ne m'est venu à l'esprit, je pense.

Enfin, pour le doublon, je reconnais l'avoir oublié dans le premier post.
Ce fait arrive rarement mais en effet il est déjà arrivé qu'un lot ait plusieurs propriétaires ayant exactement le même nom et le même prénom.
Toutefois je peux le déterminer a posteriori il me semble.
En effet, admettons que le lot 4000 contienne 1 Mileva Einstein et 2 Albert Einstein. Le lots 4004 sortira mais le 4000 ne sortira plus à cause de la vérification des count (*) si je ne me trompe pas. En effet les 2 lots contiennent les mêmes noms, mais pas le même nombre de noms.
Dès lors, si le lot recherché ne sort pas dans le résultat, on peut déterminer qu'aucun autre lot le lui correspond, à moins que ma logique ne soit pas bonne, ce qui est possible.

Encore une fois merci à tous les 2 et croyez moi sincèrement, je ne prend PERSONNE pour un con.

Jean-Marc

**SQLpro** · 03/11/2009, 21h58

OK pour la méprise !!!! ;-)

Voici la rectification à apporter pour que cela marche avec des doublons :

Code :

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

1
2
HAVING COUNT(DISTINCT JM.nom + '#' + JM.prenom) = (SELECT COUNT(*)
                                                   FROM   T)

A +

**Jean-Marc68** · 06/11/2009, 22h38

Je dois être bien bouché mais je n'y arrive pas.

J'ai créé une nouvelle table avec un id et tous les noms en minuscule (PostgreSQL n'aime pas trop les majuscules, mais cela n'a aucune importance et il n'aime pas les tables sans clé primaire. L'id sert donc juste à avoir une clé primaire dans la table).
J'ai ajouté Un Albert Einstein dans le lot 4000 dont j'ai fais une copie pour avoir le lot 4005. Le lot 4006 est une copie du 4004. Le lot 4007 ne contient que Mileva Einstein et le lot 4008 contient le couple Einstein et Marie Curie (Pour avoir 3 personnes, comme dans 4000 et 4005 mais des personnes différentes)

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
 
CREATE TABLE jm68
(
  id bigint NOT NULL,
  nolot integer,
  prenom character varying(8),
  nom character varying(8),
  CONSTRAINT jm68_pkey PRIMARY KEY (id)
)
 
INSERT INTO jm68 VALUES (0,4000, 'Albert', 'Einstein');
INSERT INTO jm68 VALUES (1,4000, 'Albert', 'Einstein');
INSERT INTO jm68 VALUES (2,4000, 'Mileva', 'Einstein');
INSERT INTO jm68 VALUES (3,4001, 'Marie',  'Curie');
INSERT INTO jm68 VALUES (4,4001, 'Pierre', 'Curie');
INSERT INTO jm68 VALUES (5,4002, 'Albert', 'Einstein');
INSERT INTO jm68 VALUES (6,4003, 'Barack', 'Obama');
INSERT INTO jm68 VALUES (7,4004, 'Albert', 'Einstein');
INSERT INTO jm68 VALUES (8,4004, 'Mileva', 'Einstein ');
INSERT INTO jm68 VALUES (9,4005, 'Albert', 'Einstein');
INSERT INTO jm68 VALUES (10,4005, 'Albert', 'Einstein');
INSERT INTO jm68 VALUES (11,4005, 'Mileva', 'Einstein');
INSERT INTO jm68 VALUES (12,4006, 'Albert', 'Einstein');
INSERT INTO jm68 VALUES (13,4006, 'Mileva', 'Einstein');
INSERT INTO jm68 VALUES (14,4007, 'Mileva', 'Einstein');
INSERT INTO jm68 VALUES (15,4008, 'Albert', 'Einstein');
INSERT INTO jm68 VALUES (16,4008, 'Mileva', 'Einstein');
INSERT INTO jm68 VALUES (17,4008, 'Marie', 'Curie');

Je devrais donc avoir 4000 et 4005 si je cherche les lots ayant les mêmes propriétaires que 4000 et avoir 4004 et 4006 si je cherche les lots ayant les mêmes propriétaires que 4004.

J'ai PostrgreSQL 8.3 et il sembe le pas aimer les With.
J'ai donc fais ceci sur base des tes posts précédents

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
 
SELECT nolot
FROM   jm68 JM
       INNER JOIN (SELECT nom, prenom
                   FROM   jm68
                   INTERSECT
                      SELECT nom, prenom
                      FROM   jm68
                      WHERE  nolot = 4000) T
        ON JM.nom = T.nom
        AND JM.prenom = T.prenom
GROUP BY nolot                
HAVING COUNT(DISTINCT JM.nom || '#' || JM.prenom) = (SELECT COUNT(*)
                                                     FROM (SELECT nom, prenom
                                                           FROM   jm68
                                                           INTERSECT
                                                              SELECT nom, prenom
                                                              FROM   jm68
                                                              WHERE  nolot = 4000) T
            )

Cette string sql me sort, pour le lot 4000 : 4000,4004,4005,4006, 4008

Je me dis que c'est dû au Distinct dans le count puisque celui-ci regroupe toutes les personnes de même prénom et même nom. Je pense qu'il y aurait donc lieu de retirer le distinc des 2 bords du = dans le having, afin de ne plus regrouper ces personnes.
Je pense aussi que malgré la suppression de ces Distinct, un nombre égal de personnes différentes (Style Albert Einstein, Marie Curie et Barack Obama, ce qui fait 3 personnes aussi) ne devrait pas sortir puisqu'on est dans un having d'une table qui ne contient plus que les personnes du lot d'origine (suis-je clair ??? Je ne suis pas sur ...)
Cette réflexion m'amène ici :

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
 
SELECT nolot
FROM   jm68 AS JM
       INNER JOIN (SELECT nom, prenom
                   FROM   jm68
                   INTERSECT
                      SELECT nom, prenom
                      FROM   jm68
                      WHERE  nolot = 4000) T
        ON JM.nom = T.nom
        AND JM.prenom = T.prenom
GROUP BY nolot                
HAVING COUNT(JM.nom || '#' || JM.prenom) = (SELECT COUNT(*)
                                            FROM (SELECT nom, prenom
                                                  FROM   jm68
                                                  WHERE  nolot = 4000) T
            )

Ici la requête sur le lot 4000 me sort bien 4000,4005.

Par contre la requête sur 4004 me sort 4004,4008,4006.
La requête sur le lot 4002 me retourne 4002,4004,4008,4006.

Aussi, je ne comprend pas en quoi l'intersect d'une table sur elle-même change qqch. Qqch m'échappe parce que pour moi

Code :

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

1
2
3
4
5
6
7
 
SELECT nom, prenom
 FROM   jm68
 INTERSECT
 SELECT nom, prenom
 FROM   jm68
 WHERE  nolot = 4000

revient au même que

Code :

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

1
2
3
4
 
 SELECT nom, prenom
 FROM   jm68
 WHERE  nolot = 4000

Enfin, pour être clair, je ne dis pas que c'est la même chose, mais je dis que personnellement je ne vois pas la différence. Aussi j'aimerais qu'on m'éclaire sur la chose.

Je suis donc reparti à 0, j'ai relu votre tuto sur la division et j'ai adapté comme ceci :

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
 
SELECT nolot
FROM jm68 Lots
WHERE NOT EXISTS
   (SELECT * 
    FROM (SELECT nom, prenom
          FROM jm68
          WHERE  nolot = 4000) PropriosLot
    WHERE NOT EXISTS
       (SELECT *
        FROM jm68 Proprios2
        WHERE (Lots.nolot = Proprios2.nolot)
               AND (Proprios2.nom = PropriosLot.nom 
                    AND Proprios2.prenom = PropriosLot.prenom)
 )
    )
GROUP BY nolot
HAVING COUNT (*) =
   (SELECT COUNT(*)
    FROM (SELECT nom, prenom
          FROM jm68
          WHERE nolot = 4000) PropriosLot
   )

Ici tout fonctionne sauf pour les 4000 et 4005 qui me sortent 4000,4005,4008.
Par contre le 4008 me sort bien uniquement 4008.

Le problème doit sans doute venir du doublon de Albert Einstein dans le 4000 et 4005. Il trouve bien Albert Einstein et Mileva Einstein dans les 3 lots et le nombre de propriétaires est exact (3 dans tous les cas).

J'ai l'impression d'être proche mais je ne trouve pas et ça me fâche.

Merci de votre attention et de l'aide que vous m'avez déjà apportée,
JM

**estofilo** · 08/11/2009, 14h32

A tout hasard je rappelle que cette requête est une solution exacte à ton problème depuis le début:

Code :

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

1
2
3
SELECT nolot FROM 
  (SELECT nolot, array_agg(row(prenom, nom)) AS liste FROM lot GROUP BY nolot) s1
 WHERE liste=(SELECT array_agg(row(prenom, nom)) FROM lot WHERE nolot=4000);

Pourquoi chercher midi à quatorze heure?
Les requêtes toutes faites de division relationnelle ne s'appliquent pas à ton jeu de données car l'existence de doublons dans le dividende change complètement la donne.

**estofilo** · 08/11/2009, 14h59

A propos de la sous-requête:

Code :

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

1
2
3
4
5
6
SELECT nom, prenom
 FROM   jm68
 INTERSECT
 SELECT nom, prenom
 FROM   jm68
 WHERE  nolot = 4000

et de la question rattachée:

Aussi, je ne comprend pas en quoi l'intersect d'une table sur elle-même change qqch

Effectivement avec ces ensembles là de part et d'autre cet opérateur d'intersection n'apporte rien. En tout cas

Code :

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

SELECT DISTINCT nom, prenom FROM   jm68 WHERE  nolot = 4000

produirait le même résultat. (à noter quand même le DISTINCT dû au fait qu'INTERSECT élimine les doublons).

**Jean-Marc68** · 09/11/2009, 19h56

Salut Estofilo,

Je suis sous 8.3 et j'avais créé la fonction array_agg comme tu me l'avais conseillé mais j'obtenais toujours une erreur (ERROR: could not find array type for data type record
État SQL :42704).
Vu mon niveau je n'arrivais pas à la supprimer et je pensais que c'était sans doute dû au fait que je devais faire qqch de plus dans la 8.3.
Pour finir j'ai fini par me rendre compte que le pbl était dû au row(prenom,nom).
J'ai donc remplacé le row(prenom, nom) par nom || '|' || prenom et j'ai ajouté un where de plus afin de n'obtenir que les autres lots ayant les mêmes personnes sans que le lot à partir duquel on cherche ne sorte.

J'ai donc écris ceci qui fonctionne

:

Code :

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

1
2
3
4
 
SELECT nolot FROM 
  (SELECT nolot, array_agg(nom || '|' || prenom) AS liste FROM jm68 GROUP BY nolot) s1
 WHERE liste=(SELECT array_agg(nom || '|' || prenom) FROM jm68 WHERE nolot=4000) AND nolot<>4000;

Merci à toi Estofilo, et à SQLPro aussi pour le temps que vous avez passé à m'aider, et par la même occasion à m'instruire,

JM

Besoin d'une fonction pour trouver des éléments identiques dans une table

PostgreSQL

Discussions similaires

Partager

Partager