Count sur une requete

[fabrice91]

Bonjour,

Pfiou, je sens que mon cas va être difficile à résumer...
J'ai une requête qui ressemble à ça, que je nomme requete1 (voir à la fin du post) :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT element, count(element) AS nb 
FROM compound 
WHERE element IN 
( 
    SELECT element 
    FROM compound 
    WHERE single IN 
    ( 
        SELECT id FROM single 
        WHERE element IN 
        (
            SELECT id 
            FROM element_name 
            WHERE name IN ("element1","element2") 
        )
    ) 
    GROUP BY element 
    HAVING count(element) = 2
) 
GROUP BY element 
HAVING nb=2;

Elle me permet de savoir si un mélange de deux éléments ("element1" et "element2") existe dans ma base.
Je peux aussi la lancer avec deux autres éléments, element3 et element4...

Le résultat de la requête est ainsi :

Code :

1
2
3
4
5
6
+----------+----+
| element  | nb |
+----------+----+
| 374      |  2 |
+----------+----+
1 row IN SET (0.00 sec)

-> j'ai un seul mélange (d'id 374) qui contient à la fois element1 et element2 (nb=2)

Code :

1
2
3
4
5
6
7
+---------+----+
| element | nb |
+---------+----+
| 340     |  2 |
| 447     |  2 |
+------- -+----+
2 rows IN SET (0.00 sec)

-> j'ai deux mélanges (d'id 340 et 447) qui contient à la fois element3 et element4 (nb=2)

Ce qui m'intéresse ici c'est donc de récupérer le résultat du nombre de row de chaque requête donc "1 row" pour la première et "2 rows" pour la seconde.
Je n'arrive pas à ajouter un "count" sur la requête de base pour compter ce nombre de row.
Du coup je suis passé par une vue et cela fonctionne mais cela me semble un peu lourd :

Code :

1
2
3
CREATE VIEW v AS SELECT element, count(element) AS nb FROM compound WHERE element IN ( SELECT element FROM compound WHERE single IN ( SELECT id FROM single WHERE element IN (SELECT id FROM element_name WHERE name IN ("element1","element2") )) GROUP BY element HAVING count(element)=2) GROUP BY element HAVING nb=2;
SELECT count(*) FROM v ;
DROP VIEW v ;

Dans le premier cas j'obtiens :

Code :

1
2
3
4
5
6
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row IN SET (2.89 sec)

et dans le second :

Code :

1
2
3
4
5
6
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row IN SET (2.92 sec)

ce qui est parfait !!!
une idée pour faire le count directement dans ma requete1 ?
j'ai essayé

Code :

SELECT count (*) (requete1) ;

mais j'ai une erreur de syntaxe...

[Maljuna Kris]

Saluton,
Connais-tu la clause WITH ROLLUP de GROUP BY ?
Par ailleurs je pense que, plutôt qu'avec des sous-requêtes comme argument d'opérateurs IN, les performances seraient meilleures avec des jointures externes.

[CinePhil]

Apparemment, fabrice91, tu as besoin d'apprendre les jointures !
Au passage, les valeurs textuelles s'écrivent entre apostrophes et pas entre guillemets.

Les trois sous-requêtes de ta requête donneraient semble t-il ceci :

Code :

1
2
3
4
5
6
7
SELECT c.element
FROM compoud c
INNER JOIN single s ON s.id = c.single
    INNER JOIN element_name en ON s.element = en.id
WHERE en.name IN ('element1', 'element2') 
GROUP BY c.element 
HAVING count(c.element) = 2

Vu que c'est déjà groupé par élément, pourquoi groupes-tu de nouveau par élément dans ta requête principale ?

[fabrice91]

Envoyé par Maljuna Kris

Saluton,
Connais-tu la clause WITH ROLLUP de GROUP BY ?
Par ailleurs je pense que, plutôt qu'avec des sous-requêtes comme argument d'opérateurs IN, les performances seraient meilleures avec des jointures externes.

Je ne connais pas WITH ROLLUP, j'ai regardé un peu mais je ne vois pas en quoi cela peux me servir...
Pour les jointures, je suis on ne peux plus d'accord...

Envoyé par CinePhil

Apparemment, fabrice91, tu as besoin d'apprendre les jointures !

J'ai appris SQL à une époque où les jointures n'existaient pas, puis je n'ai plus utilisé SQL pendant longtemps, je m'y remets depuis quelques temps mais j'ai du mal avec les jointures, je trouve plus claires mes imbrications !
Mais bon, j'ai déjà commencé à migrer des requêtes en jointures donc je m'y mets petit à petit...

Envoyé par CinePhil

Les trois sous-requêtes de ta requête donneraient semble t-il ceci:

Humph, pas tout à fait, au lieu d'avoir 2 rows pour un de mes exemples, j'en ai 3 et le troisième correspond à priori à celui qui est supprimé avec mon deuxième group by...

[Maljuna Kris]

Envoyé par fabrice91

Je ne connais pas WITH ROLLUP, j'ai regardé un peu mais je ne vois pas en quoi cela peux me servir....

Justement à faire le comptage global que tu demandais.

[fabrice91]

Envoyé par Maljuna Kris

Justement à faire le comptage global que tu demandais.

Ah ok mais d'après ce que j'ai vu, cela sors sous forme d'une ligne supplémentaire donc si j'ai 2 rows normalement, en ajoutant le WITH ROLLUP j'aurai une ligne supplémentaire indiquant le nombre de rows...
mais cette solution ne me donne pas DIRECTEMENT par le select lui-même le nombre de rows...

[fabrice91]

bon alors voilà une petit analogie :
3 tables :
element_name (id int, name varchar)
single (id int, name varchar)
assemblage (id int, element_name_id int, single_id int) avec element_name_id et single_id des clés étrangères sur les id respectivement des tables element_name et single.

Exemple d'element_name : skateboard, voiture, moto, velo
Exemple de single : guidon, volant, roue, frein, porte

Je vais avoir comme "assemblage"s :
skateboard/roue
velo/roue
velo/frein
velo/guidon
moto/roue
moto/frein
moto/guidon
voiture/roue
voiture/frein
voiture/volant
voiture/porte

La question posée par ma requête est :
- combien de "assemblage" sont constitués EXACTEMENT de roue+frein+guidon :réponse = 2 (velo et moto)
- combien de "assemblage" sont constitués EXACTEMENT de roue : réponse = 1 (skateboard)

[fab256]

L'idée de base c'est d'avoir a partir de la table assemblage
une table avec un groupe de ce genre
skateboard : roue
velo : roue, frein, guidon
moto : roue, frein, guidon
voiture : roue, frein, volant, porte

Pour commencer, si on veux que notre requête reçois comme paramètres des noms et non des codes, on fait un LEFT JOIN entre la table assemblage, element_name et single

Code :

1
2
3
4
SELECT ee.id ee_id, ee.name ee_name,ss.name ss_name
FROM assemblage aa
     LEFT JOIN (element_name ee,single ss)
     ON (ee.id=aa.element_name_id AND ss.id=aa.single_id)

La fonction qui permet de concaténer plusieurs valeurs dans mysql est group_concat.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT DISTINCT a.element_name_id , 
       (
       SELECT group_concat( ss_name ORDER BY ss_name)
       FROM (
             SELECT ee.name ee_name,ss.name ss_name, ee.id ee_id
             FROM assemblage aa
                  LEFT JOIN(element_name ee,single ss)
                  ON (ee.id=aa.element_name_id AND ss.id=aa.single_id)
             ) t
       WHERE t.ee_id = a.element_name_id
       ) composants
FROM assemblage a

Il ne reste qu'ajouter notre filtre et de compter les occurrences

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT count(*) FROM 
(
SELECT DISTINCT a.element_name_id , 
       (
       SELECT group_concat( ss_name ORDER BY ss_name)
       FROM (
             SELECT ee.name ee_name,ss.name ss_name, ee.id ee_id
             FROM assemblage aa
                  LEFT JOIN(element_name ee,single ss)
                  ON (ee.id=aa.element_name_id AND ss.id=aa.single_id)
             ) t
       WHERE t.ee_id = a.element_name_id
       ) composants
FROM assemblage a
) b
WHERE b.composants='frein,guidon,roue'

Pour le dernier filtre, il faut juste respecter l'ordre alphabétique car le résultat de group_concat est une chaine de caractères d'élément single triées et donc il faut la comparer aussi avec une chaine qui respecte les mêmes contraintes. C'est ca ou bien mettre toutes les combinaisons possibles avec un OR.

[fabrice91]

merci beaucoup !