[Oracle 10g] Liste des clients ayant loué au moins deux films de chaque genre

**slideveloppeur2006** · 15/05/2006, 21h00

Bonjour,

Voici mon schéma pour une gestion simplifié de location vidéo sous oracle

CLIENTS------ EXEMPLAIRES----------- FILMS----------EST DE------- GENRES
____________________________________________________________________________
Num_client -- Num_exemplaire --- --Num_Film -- ---Num_genre ----- Num_genre
----------------Num_Client ------------Num_Film------------------------- Libelle
-----------------Date_location

Voici la requete :

Liste des clients ayant loué au moins deux films de chaque genre

J'ai commencé et j'ai trouvé ça,

select num_client from exemplaires e, films f
where e.num_film=f.num_film
group by num_client
having count(distinct numgenre) = (
select count(*) from genre);

Mais ça ne fonctionne pas

Merci de m'aider

**Swoög** · 15/05/2006, 21h18

Salut

que penses-tu de ceci :

Code :

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

1
2
3
4
5
6
7
SELECT C.num_client
FROM clients C
WHERE MIN(<div style="margin-left:40px">SELECT count(*)
FROM films F
<div style="margin-left:40px">INNER JOIN exemplaires E
<div style="margin-left:40px">ON F.Num_Film = E.Num_Film</div></div>GROUP BY F.Num_genre
WHERE E.Num_client = C.Num_client</div>) >= 2

**Médiat** · 15/05/2006, 21h52

Si tu avais posté le DDL de tes tables et les insert nécessaires à un jeu de test, j'aurais pu tester la requête suivante, là elle est sans aucune garantie. :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT c.Num_Client
FROM Clients c CROSS JOIN Genre g
               LEFT  JOIN (SELECT e.Num_Client, f.Num_Genre
                           FROM Exemplaires e INNER JOIN Films f 
                                                    ON e.Num_Film = f.Num_Film
                           GROUP BY e.Num_Client, f.Num_Genre
                           HAVING Count(DISTINCT e.Num_Film) >= 2) l
                     ON  c.Num_Client = l.Num_Client
                     AND g.Num_Genre  = l.Num_Genre
GROUP BY c.Num_Client
HAVING Count(g.Num_Genre) = Count(l.Num_Genre)

Le Count(DISTINCT Num_Film) est là pour éviter de compter deux fois le même film

**slideveloppeur2006** · 15/05/2006, 23h06

Ok je teste tout ça ce soir, merci beaucoup

**slideveloppeur2006** · 16/05/2006, 01h05

Envoyé par Swoög

Salut

que penses-tu de ceci :

Code :

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

1
2
3
4
5
SELECT C.num_client
FROM clients C
WHERE MIN(<div style="margin-left:40px">SELECT count(*)
FROM films F<div style="margin-left:40px">INNER JOIN exemplaires E<div style="margin-left:40px">ON F.Num_Film = E.Num_Film</div></div>GROUP BY F.Num_genre
WHERE E.Num_client = C.Num_client</div>) >= 2

Ben la il me dit ques la fonction de groupe SUM n'est pas autorisé ici

**Swoög** · 16/05/2006, 01h14

essaie de remplacer WHERE par HAVING (m'étonnerais beaucoup mais bon...) sinon, il va falloir faire une sous-requête de plus...

mais dans ce cas là, peut-ête que la requête de Mediat sera plus performante... enfin, je propose quand même :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT C.num_client
FROM clients C
WHERE (
<div style="margin-left:40px">SELECT MIN(count)
FROM (
<div style="margin-left:40px">SELECT count(*) AS count FROM films F
<div style="margin-left:40px">INNER JOIN exemplaires E
<div style="margin-left:40px">ON F.Num_Film = E.Num_Film</div></div>GROUP BY F.Num_genre
WHERE E.Num_client = C.Num_client</div>)</div>) >= 2

**slideveloppeur2006** · 16/05/2006, 01h34

Envoyé par Médiat

Si tu avais posté le DDL de tes tables et les insert nécessaires à un jeu de test, j'aurais pu tester la requête suivante, là elle est sans aucune garantie. :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT c.Num_Client
FROM Clients c CROSS JOIN Genre g
               LEFT  JOIN (SELECT e.Num_Client, f.Num_Genre
                           FROM Exemplaires e INNER JOIN Films f 
                                                    ON e.Num_Film = f.Num_Film
                           GROUP BY e.Num_Client, f.Num_Genre
                           HAVING Count(DISTINCT e.Num_Film) >= 2) l
                     ON  c.Num_Client = l.Num_Client
                     AND g.Num_Genre  = l.Num_Genre
GROUP BY c.Num_Client
HAVING Count(g.Num_Genre) = Count(l.Num_Genre)

Le Count(DISTINCT Num_Film) est là pour éviter de compter deux fois le même film

Ben la il me met des erreurs de clé, d attribut n'appartienne pas au bonne tables, je met le listing de la création de la base/alimentation

**Swoög** · 16/05/2006, 01h39

!!!

tu aurais du mettre tout ça dans un fichier join, fait le STP ! (en éditant ton message)

**Médiat** · 16/05/2006, 06h13

Envoyé par Swoög

!!!

tu aurais du mettre tout ça dans un fichier join, fait le STP ! (en éditant ton message)

+1 et en enlevant tout ce qui ne concerne pas la question...

**Médiat** · 16/05/2006, 06h30

Il suffisait de changer le nom d'une table :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT c.Num_Client
FROM Clients c CROSS JOIN Genres g
               LEFT  JOIN (SELECT e.Num_Client, f.Num_Genre
                           FROM Exemplaires e INNER JOIN est_de f 
                                                    ON e.Num_Film = f.Num_Film
                           GROUP BY e.Num_Client, f.Num_Genre
                           HAVING Count(DISTINCT e.Num_Film) >= 2) l
                     ON  c.Num_Client = l.Num_Client
                     AND g.Num_Genre  = l.Num_Genre
GROUP BY c.Num_Client
HAVING Count(g.Num_Genre) = Count(l.Num_Genre);

Avec ton jeu d'essai je trouve les clients 0 et 2.

**slideveloppeur2006** · 16/05/2006, 08h14

Le source en pièce jointe, désolé pour le retard, si ça marche comme tu l'a dit, je vous remercie énormément car la requête était de taille.....

**SQLpro** · 16/05/2006, 11h20

Plus compréhensible :

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
SELECT DISTINCT CLI.*
FROM   CLIENTS CLI
       INNER JOIN EXEMPLAIRES EXP
             ON CLI.NUM_CLIENT = EXP.NUM_CLIENT
       INNER JOIN FILMS FLM
             ON EXP.NUM_FILM = FLM.NUM_FILM
       INNER JOIN EST_DE GNR
             ON FLM.NUM_FILM = GNR.NUM_FILM
-- au moins deux films
WHERE EXISTS (SELECT COUNT(DISTINCT NUM_FILM)
              FROM   EXEMPLAIRES EX2
              WHERE  EX2.NUM_CLIENT = CLI.NUM_CLIENT
              HAVING COUNT(DISTINCT NUM_FILM) > 1)
-- deux genres différents
  AND EXISTS (SELECT COUNT(DISTINCT NUM_GENRE)
              FROM   EXEMPLAIRES EX3
                     INNER JOIN FILMS FM3
                           ON EX3.NUM_FILM = FM3.NUM_FILM
                     INNER JOIN EST_DE GR3
                           ON FM3.NUM_FILM = GR3.NUM_FILM
              WHERE  EX3.NUM_CLIENT = CLI.NUM_CLIENT
              HAVING COUNT(DISTINCT NUM_GENRE) > 1)

A +

**Swoög** · 16/05/2006, 11h25

@SQLpro : sauf qu'il veut les clients qui ont loué au moins deux films pour CHAQUE genre...

**Yanika_bzh** · 16/05/2006, 13h05

J'ajoute ma pierre a l'edifice

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT num_client
 FROM (SELECT b.num_client, d.num_genre, COUNT (*)
    FROM exemplaires a,clients b,films c,genres d,est_de e
    WHERE a.num_film = c.num_film
      AND a.num_client = b.num_client
      AND e.num_film = a.num_film
      AND e.num_genre = d.num_genre
    HAVING COUNT (*)>1
    GROUP BY b.num_client, d.num_genre) a
HAVING COUNT (*)=(SELECT COUNT (*)
FROM genres)
GROUP BY num_client

Est ce que cela fonctionne ??

bon courage

**slideveloppeur2006** · 16/05/2006, 19h58

Envoyé par Yanika_bzh

J'ajoute ma pierre a l'edifice

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT num_client
 FROM (SELECT b.num_client, d.num_genre, COUNT (*)
    FROM exemplaires a,clients b,films c,genres d,est_de e
    WHERE a.num_film = c.num_film
      AND a.num_client = b.num_client
      AND e.num_film = a.num_film
      AND e.num_genre = d.num_genre
    HAVING COUNT (*)>1
    GROUP BY b.num_client, d.num_genre) a
HAVING COUNT (*)=(SELECT COUNT (*)
FROM genres)
GROUP BY num_client

Est ce que cela fonctionne ??

bon courage

Merci !!!!!!!!!!!!!!!!!!!!!!
Cette pierre à l'edifice m'aide beaucoup,
en plus tu a la solution la plus clair. il me reste plus qu'à sortir les noms des clients

Encore Merci pour les auters aussi,dont les solutions fonctionnent aussi mais sont un peu + complexe.

Ce qui donne au final :

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 nom_client,prenom_client
FROM clients
WHERE num_client IN ( 
SELECT num_client 
FROM (
SELECT b.num_client, d.num_genre, COUNT (*)  
FROM exemplaires a,clients b,films c,genres d,est_de e  
WHERE a.num_film = c.num_film 
AND b.num_client <> 0 
AND a.num_client = b.num_client  
AND e.num_film = a.num_film  
AND e.num_genre = d.num_genre  
HAVING COUNT (*)>1  
GROUP BY b.num_client, d.num_genre
) a HAVING COUNT (*)=(SELECT COUNT (*) FROM genres) GROUP BY num_client
)

**Médiat** · 17/05/2006, 09h30

Ta requête ne fonctionne pas si un client loue deux fois le même film (cela n'a rien de bizarre) car il sera compté deux fois pour chaque genre auquel il appartient...

Il suffit de remplacer HAVING COUNT (*)>1 par HAVING COUNT (DISTINCT a.num_film)>1.

de plus la requête

Code :

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

1
2
3
4
5
6
7
8
9
SELECT b.num_client, d.num_genre, COUNT (*)  
FROM exemplaires a,clients b,films c,genres d,est_de e  
WHERE a.num_film = c.num_film 
AND b.num_client <> 0 
AND a.num_client = b.num_client  
AND e.num_film = a.num_film  
AND e.num_genre = d.num_genre  
HAVING COUNT (*)>1  
GROUP BY b.num_client, d.num_genre

me paraît inutilement compliquée : les tables clients, films et genres ne servent à rien pour compter les films par client et genre :

Code :

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

1
2
3
4
SELECT a.Num_Client, e.Num_Genre
FROM Exemplaires a INNER JOIN est_de e ON a.Num_Film = e.Num_Film
GROUP BY a.Num_Client, e.Num_Genre
HAVING Count(DISTINCT a.Num_Film) > 1

**Yanika_bzh** · 17/05/2006, 09h51

Envoyé par Médiat

Ta requête ne fonctionne pas si un client loue deux fois le même film (cela n'a rien de bizarre) car il sera compté deux fois pour chaque genre auquel il appartient...

Il suffit de remplacer HAVING COUNT (*)>1 par HAVING COUNT (DISTINCT a.num_film)>1.

de plus la requête

Code :

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

1
2
3
4
5
6
7
8
9
SELECT b.num_client, d.num_genre, COUNT (*)  
FROM exemplaires a,clients b,films c,genres d,est_de e  
WHERE a.num_film = c.num_film 
AND b.num_client <> 0 
AND a.num_client = b.num_client  
AND e.num_film = a.num_film  
AND e.num_genre = d.num_genre  
HAVING COUNT (*)>1  
GROUP BY b.num_client, d.num_genre

me paraît inutilement compliquée : les tables clients, films et genres ne servent à rien pour compter les films par client et genre :

Code :

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

1
2
3
4
SELECT a.Num_Client, e.Num_Genre
FROM Exemplaires a INNER JOIN est_de e ON a.Num_Film = e.Num_Film
GROUP BY a.Num_Client, e.Num_Genre
HAVING Count(DISTINCT a.Num_Film) > 1

Je te l'accorde, au depart j'etais parti sur une requete qui affiche le nom, le libelle du film et le libelle du genre, d'ou la jointure.
L'intitulé parlait de clients qui avaient loué au moins 2 films de tous les genres, meme si ca reste 2 fois le meme film, le postulat reste suivi. Si le postulat de depart était "clients qui avaient loué au moins 2 différents films de tous les genres", alors ok

En effet, sur ce genre de statistiques, je pense que c'est le genre aimé par le client qui est essentiel, pas le film.
Apres, je ne sais pas quel est le point de vue de l'auteur du topic

**Médiat** · 17/05/2006, 10h38

Envoyé par Yanika_bzh

Apres, je ne sais pas quel est le point de vue de l'auteur du topic

Nous en sommes au même point

**slideveloppeur2006** · 17/05/2006, 15h14

Envoyé par Médiat

Nous en sommes au même point

Ben en fait, il me faudrait que le nom du client, c'est tout.

Et par contre j'ai rajouté num_client <> 0 pour enlever le client fictif (c'est-à-dire que le film est loué par personne).

Donc au final je met quelle requete :

Celle-ci :

Code :

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

1
2
3
4
SELECT a.Num_Client, e.Num_Genre
FROM Exemplaires a INNER JOIN est_de e ON a.Num_Film = e.Num_Film
GROUP BY a.Num_Client, e.Num_Genre
HAVING Count(DISTINCT a.Num_Film) > 1

Parce que je ne vois pas où est-ce que la requete calcule "pour chaque genre" ? Il ne doit pas y avoir un count(*) quelque part ou un Exist.

Merci pour tous vos efforts

**slideveloppeur2006** · 17/05/2006, 20h05

Envoyé par Médiat

Ta requête ne fonctionne pas si un client loue deux fois le même film (cela n'a rien de bizarre) car il sera compté deux fois pour chaque genre auquel il appartient...

Il suffit de remplacer HAVING COUNT (*)>1 par HAVING COUNT (DISTINCT a.num_film)>1.

de plus la requête

Code :

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

1
2
3
4
5
6
7
8
9
SELECT b.num_client, d.num_genre, COUNT (*)  
FROM exemplaires a,clients b,films c,genres d,est_de e  
WHERE a.num_film = c.num_film 
AND b.num_client <> 0 
AND a.num_client = b.num_client  
AND e.num_film = a.num_film  
AND e.num_genre = d.num_genre  
HAVING COUNT (*)>1  
GROUP BY b.num_client, d.num_genre

me paraît inutilement compliquée : les tables clients, films et genres ne servent à rien pour compter les films par client et genre :

Code :

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

1
2
3
4
SELECT a.Num_Client, e.Num_Genre
FROM Exemplaires a INNER JOIN est_de e ON a.Num_Film = e.Num_Film
GROUP BY a.Num_Client, e.Num_Genre
HAVING Count(DISTINCT a.Num_Film) > 1

Celle-ci ne fonctionne pas, elle m'a quant meme resorti un client qui avait loué deux films d'un genre mais un seul film d'un autre genre.

Donc je garde l'autre qui apparemment tourne mieux.

Pour ce qui est du postulat, c'est je pense deux films du même genre même si ces films sont identiques

Merci beaucoup en tout cas.

[Oracle 10g] Liste des clients ayant loué au moins deux films de chaque genre

Langage SQL

Discussions similaires

Partager

Partager