Requête avec un ensemble de paramètres

Version imprimable

01/11/2012, 23h45
aelmalki

Requête avec un ensemble de paramètres
Bonjour,

Prenons l'exemple de Clients/Commandes, un client a plusieurs commandes.
Code:

1 2 3 4 5 6 T_CLIENT ID | CODE_CLI 1 | CLIENT_A 2 | CLIENT_B 3 | CLIENT_C
Code:

1 2 3 4 5 6 7 8 9 10 T_COMMANDE ID | ID_CLT | CODE_COM 1 | 1 | Souris 2 | 1 | Clavier 3 | 2 | Souris 4 | 2 | Disque dur 5 | 3 | Clavier 6 | 3 | Souris 7 | 3 | Ecran
Je veux une requête SQL qui me permettra de récupérer les clients via les deux entrées "Souris" ET "Clavier". En fait, je dois extraire les clients qui ont commandé des souris et claviers à la fois.

Est ce que c'est possible avec SQL ? si oui, pouvez-vous me donner une piste ?

Merci pour votre aide.

SQL étant un langage complet au sens de Turing, il n'y a aucune limite d'utilisation du langage pour résoudre n'importe quel problème bien posé...
À lire : http://fr.wikipedia.org/wiki/Turing-complet

Pour votre problème il existe différentes solutions...

1) faire deux requêtes et une intersection :
Code:

1 2 3 4 5 6 7 8 9 10 11 SELECT C.* FROM T_CLIENT AS C INNER JOIN T_COMMANDE AS M ON C.ID = M.ID_CLI WHERE M.CODE_COM = 'Souris' INTERSECT SELECT C.* FROM T_CLIENT AS C INNER JOIN T_COMMANDE AS M ON C.ID = M.ID_CLI WHERE M.CODE_COM = 'Clavier'
2) demander ceux qui ont commandé les deux objets et compter si ce sont bien au moins deux objets différents :
Code:

1 2 3 4 5 6 7 SELECT C.* FROM T_CLIENT AS C INNER JOIN T_COMMANDE AS M ON C.ID = M.ID_CLI WHERE M.CODE_COM IN ('Souris', 'Clavier') GROUP BY C.ID, C.CODE_CLI HAVING COUNT(DISTINCT CODE_COM) >= 2;
Et pour vous former au langage SQL, mon site, comme mon bouquin, peuvent vous y aider !

A +

02/11/2012, 10h02
aelmalki

Bonjour,

Merci pour votre retour,

Je constate que j'ai oublié d'ajouter un enregistrement pour le client 3 pour avoir les commandes : souris, clavier et écran. Mon premier post est mis à jour.

En essayant la deuxième solution, qui est pour moi la plus simple (surtout que je peux avoir des dizaines de commandes dans la recherche), je n'arrive pas à avoir le résultat attendu qui est le client 1 parce que c'est le seul qui à commandé les deux objets 'Souris' et 'Clavier' seulement.

A votre avis, existe il une autre piste ?

Merci beaucoup.
02/11/2012, 14h39
SQLpro

dans ce cas ce n'est pas >= 2, mais =2 qu'il faut mettre dans le filtre de résultat HAVING...

A +
02/11/2012, 16h47
StringBuilder

Dites moi si je me trompe, mais à partir du moment où on a :
- Une clause T_CLIENT AS C INNER JOIN T_COMMANDE AS M dans le FROM.
- Une clause M.CODE_COM IN ('Souris', 'Clavier') dans le WHERE.
- Une clause COUNT(DISTINCT CODE_COM) dans le HAVING.

Qu'on mette => 2 ou = 2 ça devrait faire exactement la même chose puisque COUNT(DISTINCT CODE_COM) sera obligatoirement égal à 1 ou 2 (0 ne serait présent que si T_CLIENT AS C INNER JOIN T_COMMANDE AS M était remplacé par T_CLIENT AS C LEFT OUTER JOIN T_COMMANDE AS M et des valeurs suppérieures à 2 ne seraient présentent que si M.CODE_COM IN ('Souris', 'Clavier') n'était pas présent.

Donc à part une erreur de compilation de la requête qu'aelmalki s'est bien gardé de nous préciser, je ne vois pas en quoi mettre = 2 à la place de => 2 pourrait bien changer.
02/11/2012, 16h59
aelmalki

@StringBuilder : je suis pour ton analyse, aucun changement avec l'=, parce que les clients 1 et 3 ont les deux 'Souris' et 'Clavier' comme commandes.

En fait, ce que tu veux, c'est les clients qui ont commandé une souris et un clavier et rien d'autre, c'est ça ?

Dans ce cas, je dirais qu'il faut user de subtilité :
Code:

1 2 3 4 5 6 7 SELECT C.ID FROM T_CLIENT AS C INNER JOIN T_COMMANDE AS M ON C.ID = M.ID_CLI GROUP BY C.ID, C.CODE_CLI HAVING sum(case CODE_COM when 'Clavier' then 0 when 'Souris' then 0 else 1 end) = 0 and sum(distinct code_dom) = 2;
On peut certainement faire ça de façon bien différente.

C'est à dire qu'on vérifie deux choses :
- Que rien d'autre que "Clavier" et "Souris" ont été commandés
- Qu'il y a 2 et seulement 2 références différentes qui ont été commandées

02/11/2012, 17h21
aelmalki

Merci pour votre retour, oui c'est exactement ce que je veux.

Je suis novice dans SQL, donc je prends vos réponses et j'essaye de comprendre un peu comment les implémenter.

Si je met cette instruction :

Code:

HAVING sum(case CODE_COM when 'Clavier' then 0 when 'Souris' then 0 else 1 end) = 0

Est ce que cette instruction devient alors inutile :

Code:

AND sum(DISTINCT code_dom) = 2

Ce que j'ai compris : c'est que avec les cases, on récupère seulement les clients avec 'Clavier' et 'Souris', rien de plus.

Qu'en pensez-vous ?
02/11/2012, 17h30
skuatamad

Citation:

Envoyé par aelmalki

Est ce que cette instruction devient alors inutile :

Code:

AND sum(DISTINCT code_dom) = 2

Ben non, sans cette clause, un client peut n'avoir commandé qu'une souris ou qu'un clavier mais pas les 2, par contre c'est un COUNT et pas une SUM.
02/11/2012, 17h49
aelmalki

@skuatamad : oui vous avez raison, bien vu pour le COUNT au lieu du SUM.

En gros apparemment ça marche sur cet exemple, je vous remercie tous pour votre aide, je viens d'apprendre beaucoup de chose en SQL.

Merci.

Plus optimisé :

Code:

1
2
3
4
5
6
7
8
SELECT C.*
FROM   T_CLIENT AS C
       INNER JOIN T_COMMANDE AS M
             ON C.ID = M.ID_CLI
WHERE  M.CODE_COM IN ('Souris', 'Clavier')
  AND  (SELECT COUNT(DISTINCT CODE_COM)
        FROM   T_COMMANDE AS CD
        WHERE  CD.ID_CLI = C.ID) = 2;

A +

Citation:

Envoyé par SQLpro

Plus optimisé :

Code:

1
2
3
4
5
6
7
8
SELECT C.*
FROM   T_CLIENT AS C
       INNER JOIN T_COMMANDE AS M
             ON C.ID = M.ID_CLI
WHERE  M.CODE_COM IN ('Souris', 'Clavier')
  AND  (SELECT COUNT(DISTINCT CODE_COM)
        FROM   T_COMMANDE AS CD
        WHERE  CD.ID_CLI = C.ID) = 2;

La même en corrigée :

Code:

1
2
3
4
5
6
7
8
9
10
SELECT C.ID
FROM   T_CLIENT AS C
       INNER JOIN T_COMMANDE AS M
             ON C.ID = M.ID_CLT
WHERE  M.CODE_COM IN ('Souris', 'Clavier')
  AND  (SELECT COUNT(DISTINCT CODE_COM)
        FROM   T_COMMANDE AS CD
        WHERE  CD.ID_CLT = C.ID) = 2
GROUP BY C.ID
HAVING COUNT(DISTINCT CODE_COM)=2

Je ne sais pas si elle est plus performante que celle de StringBuilder.

Une autre solution, et cette fois ça devrait être ce qu'il y a de plus performant :

Code:

1
2
3
4
5
6
7
 
select c.id
from t_client c
inner join t_commande m on m.id_cli = c.id and m.code in ('Souris', 'Clavier')
where not exists (select null from t_commande z where z.id_cli = c.id and z.code not in ('Souris', 'Clavier'))
group by c.id
having count(distinct m.code) = 2;

1 pièce(s) jointe(s)

(Grmpf)

Je viens de faire un test sur SQL Server 2012 Express.

Voici le script :

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
 
create table t_client
(
	id int identity not null primary key,
	code_cli varchar(10) not null
);
 
create table t_commande
(
	id int identity not null primary key,
	id_clt int not null references t_client(id),
	code_com varchar(10) not null
);
 
insert into t_client (code_cli) values 
('Client 1'), 
('Client 2'), 
('Client 3'), 
('Client 4'), 
('Client 5');
 
insert into t_commande (id_clt, code_com) values 
(1, 'Clavier'), (1, 'Souris'), (1, 'Ecran'), (1, 'Clavier'), (1, 'Souris'), 
(2, 'Clavier'), (2, 'Souris'), (2, 'Clavier'), 
(3, 'Clavier'), (3, 'Clavier'), 
(4, 'Ecran'), (4, 'Clavier'), 
(5, 'Souris'), (5, 'Clavier');
 
-- Première proposition de StringBuilder
SELECT C.ID
FROM   T_CLIENT AS C
       INNER JOIN T_COMMANDE AS M
             ON C.ID = M.id_clt
GROUP  BY C.ID
HAVING sum(case code_com when 'Clavier' then 0 when 'Souris' then 0 else 1 end) = 0 AND count(DISTINCT code_com) = 2;
 
-- Première proposition de Fred_34
SELECT C.ID
FROM   T_CLIENT AS C
       INNER JOIN T_COMMANDE AS M
             ON C.ID = M.id_clt
WHERE  M.code_com IN ('Souris', 'Clavier')
  AND  (SELECT COUNT(DISTINCT code_com)
        FROM   T_COMMANDE AS CD
        WHERE  CD.id_clt = C.ID) = 2
GROUP BY C.ID
HAVING COUNT(DISTINCT code_com)=2;
 
-- Seconde proposition de StringBuilder
SELECT c.id
FROM t_client c
INNER JOIN t_commande m ON m.id_clt = c.id AND m.code_com IN ('Souris', 'Clavier')
WHERE NOT EXISTS (SELECT NULL FROM t_commande z WHERE z.id_clt = c.id AND z.code_com NOT IN ('Souris', 'Clavier'))
GROUP BY c.id
HAVING count(DISTINCT m.code_com) = 2;

Aucune des requêtes de SQLPro n'y figurent, car y'en a aucune qui retourne le bon résultat (pas en forme en ce moment...)

Voici le plan d'exécution du lot en pièce jointe.

Chose assez surprenante, ma première requête est la plus performance, talonnée par ma seconde proposition.
Quant à la proposition de Fred_34 basée sur la suggestion de SQLPro, elle prend à elle toute seule plus de la moitié du lot.

Moralité : j'ai peut-être pour habitude de travailler avec un MCD de merde et un SGBD qui vaut pas mieux, mais je sais tout de même écrire des requêtes performantes et qui marchent.
Oui, je sais, le quidam est aigri, mais il est blessé.

1 pièce(s) jointe(s)

Pour mettre les requêtes dans une situation plus réaliste, j'ai aussi ajouté l'index suivant :
Code:

1 2 create index ix_code_com on t_commande (code_com);
J'ai pas regardé en détail l'impact sur les plans d'exécution, mais les proportions restent les mêmes.

En revanche, avec l'index suivant, on a des changements intéressants :
Code:

1 2 create index ix_code_com on t_commande (code_com, id_clt);
En pièce jointe les nouveaux plans d'exécution.

On remarque que ma première requête est alors complètement à la ramasse (elle passe à près de 50% du lot) alors que ma seconde ainsi que celle de Fred_34 deviennent à peu de chose aussi performantes l'une que l'autre.

Etant donné que la seconde requête est dans les deux cas autour de 25% du lot, et donc qu'elle tire aussi bien parti de la présence d'index que de leur absence, je pense qu'il sera judicieux de privilégier cette syntaxe, qui a en plus l'avantage de couler de source.

-- Edit : Je remarque aussi que la version "NOT IN" est légèrement plus performante que la version "NOT EXISTS" :
Code:

1 2 3 4 5 6 7 SELECT c.id FROM t_client c INNER JOIN t_commande m ON m.id_clt = c.id AND m.code_com IN ('Souris', 'Clavier') WHERE c.id not in (SELECT z.id_clt FROM t_commande z WHERE z.id_clt = c.id AND z.code_com NOT IN ('Souris', 'Clavier')) GROUP BY c.id HAVING count(DISTINCT m.code_com) = 2;

Un autre principe, en enrichissant le jeu de données.

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
with T_CLIENT as
(select 1 ID,  'CLIENT_A'CODE_CLI from dual union
select 2 ,  'CLIENT_B' from dual union
select 3,   'CLIENT_C' from dual union
select 4,   'CLIENT_D' from dual
), 
T_COMMANDE as
(
select 1 ID,    1 ID_CLT    ,'Clavier' CODE_COM from dual union
select 2     ,1     ,'Souris' from dual union
select 3     ,2     ,'Souris' from dual union
select 4     ,2     ,'Disque dur' from dual union
select 5     ,3     ,'Clavier' from dual union
select 6     ,3     ,'Souris' from dual union
select 7     ,3     ,'Ecran' from dual union
select 8     ,4     ,'Souris' from dual union
select 9     ,4     ,'Clavier' from dual
 
)
 
 
 
 
select  C.ID_CLT,C.CODE_CLI from
(
select B.ID_CLT,A.CODE_CLI ,
 rtrim(REPLACE(REPLACE(XMLAgg(XMLElement("x", B.CODE_COM  ) ORDER BY B.CODE_COM ASC), '<x>', ''), '</x>', '+'), '+') article
 from T_client A join T_commande B on  A.ID=B.ID_CLT
 group by B.ID_CLT,A.CODE_CLI 
 
 )C
 where C.article='Clavier+Souris'

Bon courage

15/11/2012, 12h33
SQLpro

Citation:

Envoyé par StringBuilder

(Grmpf)
Aucune des requêtes de SQLPro n'y figurent, car y'en a aucune qui retourne le bon résultat (pas en forme en ce moment...).

Tu as raison : entorse il y a 5 semaines, plus sciatique il y a 10 jours... En sus de beaucoup trop de boulot !!!!

A +