[Division relationnelle] Comparaison sur plusieurs tuples

**jowsuket** · 29/04/2008, 16h40

Bonjour,

Je ne pense pas que ça sois bien compliqué mais là je sèche un peu. Etant donné que je ne sais pas comment formuler ma question je vais l'illustrer par un exemple :

J'ai trois table :
CLIENT(Numero int, Nom varchar) où 'Numero' est clé primaire
PRODUIT(Numero int, Nom varchar) où 'Numero' est clé primaire
LIGNE(Numero_client int, Numero_produit int) où 'Numero_client' et 'Numero_produit' sont une clé primaire

Avec les données suivantes
CLIENT
Numero Nom
1 A
2 B
3 C

LIGNE
Numero_client Numero_produit
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 4

PRODUIT
Numero Nom
1 A
2 B
3 C
4 D

Et je souhaiterai faire une requête qui me revoie le ou les clients ayant commandé exactement les mêmes produits que le client Numéro 1 (On devrait obtenir le client Numéro 2 ici)

**fsmrel** · 30/04/2008, 02h11

Envoyé par jowsuket

Je ne pense pas que ça sois bien compliqué mais là je sèche un peu.

Vous êtes confronté à un problème de division relationnelle.

Supposons qu’il existe une table Cli1Prod (NumProduit) contenant tous les produits fournis par le client c1.
Votre question devient la suivante :

"Quels sont les clients (table LIGNE) qui fournissent tous les produits figurant dans la table Cli1Prod"

Que l’on peut donc reformuler ainsi :

"Quels sont les clients pour lesquels, quel que soit Cli1Prod.NumProduit, Cli1Prod.NumProduit = LIGNE.NumProduit".

"Quel que soit" exprime la quantification universelle, que malheureusement SQL ne propose pas. On mettra donc à profit la quantification existentielle (EXISTS), sur la base de l’équivalence :

"Tout homme est mortel" ≡ "Il n’existe pas d’homme non mortel" (∀x Fx ≡ ¬∃x ¬Fx)

Cette dernière affirmation mettant en jeu une double négation.

La question initiale peut s’exprimer ainsi (avec la double négation) :

"Quels sont les clients pour lesquels il n’existe pas de Cli1Prod.NumProduit n’existant pas en tant que LIGNE.NumProduit".

D’où l’instruction SQL (avec double négation), qui paraphrase en fait la division relationnelle :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Select  Distinct NumClient
from    Ligne x
where   not exists 
          (select  *
           from    Cli1Prod y
           where   not exists
                      (select    *
                       from      Ligne z
                       where     x.NumClient = z.NumClient 
                       and       y.NumProduit = z.NumProduit
                      )
          )
And    NumClient <> 'c1'  ;

Instruction dans laquelle on élimine c1 du résultat (cf. dernière ligne).

Maintenant, on peut remplacer CLi1Prod par une expression :

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
Select  Distinct NumClient
from    Ligne x
where   not exists 
          (select  *
           from   (Select NumProduit 
                   From   Ligne 
                   where  NumClient = 'c1'
                  ) AS y
           where   not exists
                      (select    *
                       from      Ligne z
                       where     x.NumClient = z.NumClient 
                       and       y.NumProduit = z.NumProduit
                      )
          )
And    NumClient <> 'c1'  ;

Et si c’est le nom du client qu’il faut fournir :

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  Distinct NomClient
from    Ligne x Inner Join Client t
          On x.NumClient = t.NumClient
where   not exists 
          (select  *
           from    (Select  NumProduit 
                    From    Ligne 
                    where   NumClient = 'c1'
                   ) AS y
           where   not exists
                        (select  *
                         from    Ligne z
                         where   x.NumClient = z.NumClient 
                         and     y.NumProduit = z.NumProduit
                        )
          )
And    x.NumClient <> 'c1' ;

Il existe bien sûr d’autres solutions, voyez les écrits de SQLpro concernant la division relationnelle.

**jowsuket** · 30/04/2008, 12h04

Et bien merci de m'avoir mis sur la voie de la division relationnelle (concept que je ne connaissais pas du tout) ainsi que toutes les explications que vous avez pris la peine de me fournir.

J'ai testé votre 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
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
 
--Les talbes + Valeurs
DECLARE @Client table(Numero int, Nom char(1))
DECLARE @Ligne table(Numero_client int, Numero_produit char(1))
DECLARE @Produit table(Numero int, Nom char(1))
 
INSERT INTO @Client(Numero, Nom) VALUES(1,'a')
INSERT INTO @Client(Numero, Nom) VALUES(2,'b')
INSERT INTO @Client(Numero, Nom) VALUES(3,'c')
INSERT INTO @Client(Numero, Nom) VALUES(4,'d')
 
INSERT INTO @Produit(Numero, Nom) VALUES(1,'e')
INSERT INTO @Produit(Numero, Nom) VALUES(2,'f')
INSERT INTO @Produit(Numero, Nom) VALUES(3,'g')
INSERT INTO @Produit(Numero, Nom) VALUES(4,'h')
 
INSERT INTO @Ligne(Numero_client, Numero_produit) VALUES(1,1)
INSERT INTO @Ligne(Numero_client, Numero_produit) VALUES(1,2)
INSERT INTO @Ligne(Numero_client, Numero_produit) VALUES(1,3)
INSERT INTO @Ligne(Numero_client, Numero_produit) VALUES(2,1)
INSERT INTO @Ligne(Numero_client, Numero_produit) VALUES(2,2)
INSERT INTO @Ligne(Numero_client, Numero_produit) VALUES(2,3)
INSERT INTO @Ligne(Numero_client, Numero_produit) VALUES(3,1)
INSERT INTO @Ligne(Numero_client, Numero_produit) VALUES(3,2)
INSERT INTO @Ligne(Numero_client, Numero_produit) VALUES(3,4)
INSERT INTO @Ligne(Numero_client, Numero_produit) VALUES(4,1)
INSERT INTO @Ligne(Numero_client, Numero_produit) VALUES(4,2)
 
--Le client pour lequel on cherche les autres clients ayant EXACTEMENT TOUS les même produits
DECLARE @Numero_client int
SET @Numero_client = 4
 
--La requete
SELECT DISTINCT Numero_client
FROM    @Ligne x
WHERE   NOT EXISTS 
	(SELECT  *
		FROM	(SELECT Numero_produit 
					FROM   @Ligne 
					WHERE  Numero_client = @Numero_client
				) AS y
		WHERE NOT EXISTS
			(SELECT    *
				FROM      @Ligne z
				WHERE     x.Numero_client = z.Numero_client 
				AND       y.Numero_produit = z.Numero_produit
			)                
	)
AND Numero_client <> @Numero_client

Mais ici le résultat rendu est :
Numero_client
1
2
2

Seulement je souhaitais obtenir que les clients ayant EXACTEMENT les mêmes produits que le client passé en paramètre (ni plus ni moins). Et suivant votre conseil, j'ai donc lu la doc de SqlPro et j'y est trouvé mon compte :

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
 
SELECT DISTINCT Numero_client
FROM    @Ligne x
WHERE   NOT EXISTS 
	(SELECT  *
		FROM	(SELECT Numero_produit 
					FROM   @Ligne 
					WHERE  Numero_client = @Numero_client
				) AS y
		WHERE NOT EXISTS
			(SELECT    *
				FROM      @Ligne z
				WHERE     x.Numero_client = z.Numero_client 
				AND       y.Numero_produit = z.Numero_produit
			)                
	)
AND Numero_client <> @Numero_client
GROUP BY Numero_client
HAVING COUNT (*) = (SELECT COUNT(DISTINCT Numero_produit) FROM @Ligne WHERE Numero_client=@Numero_client)

Merci encore
++

**fsmrel** · 30/04/2008, 14h44

Envoyé par jowsuket

ici le résultat rendu est :
Numero_client
1
2
2

Le DISTINCT ne permet pas d'obtenir ce résultat, mais le suivant :
1
2
3

Envoyé par jowsuket

je souhaitais obtenir que les clients ayant EXACTEMENT les mêmes produits que le client passé en paramètre (ni plus ni moins).

La requête que je vous ai fournie permet d'obtenir les clients ayant AU MOINS les mêmes produits que le client passé en paramètre. Il est évident que si vous souhaitez n'obtenir que les clients ayant EXACTEMENT les mêmes produits, alors il est nécessaire de compléter la requête, comme vous l'avez fait, en y ajoutant une opération relationnelle supplémentaire, à savoir un RESTRICT, ce qui est parfaitement rendu par le couple (GROUP BY, HAVING).

**jowsuket** · 30/04/2008, 15h49

Citation:
Envoyé par jowsuket Voir le message
ici le résultat rendu est :
Numero_client
1
2
2

Le DISTINCT ne permet pas d'obtenir ce résultat, mais le suivant :
1
2
3

Autant pour moi, c'est bien ce que j'obtenais, j'ai juste mal recopié le résultat dans mon message.

[Division relationnelle] Comparaison sur plusieurs tuples

Langage SQL

Discussions similaires

Partager

Partager