Bonjour.

Je suis actuellement à la partie 3 dans le chapitre "Exercices sur les agrégats".

La "question" n°3 est :

Combien de mâles et de femelles de chaque race avons-nous, avec un compte total intermédiaire pour les races (mâles et femelles confondues) et pour les espèces ? Afficher le nom de la race et le nom courant de l'espèce.

.

La requête SQL proposée est :

SELECT Animal.sexe, Race.nom, Espece.nom_courant, COUNT(*) AS nombre
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
INNER JOIN Race ON Animal.race_id = Race.id
WHERE Animal.sexe IS NOT NULL
GROUP BY Espece.nom_courant, Race.nom, sexe WITH ROLLUP;

J'aimerais modifier cette requête afin de récupérer, en plus de la requête initiale, les animaux "non genrés" (dans la soixantaine d'animaux listés dans la table Animal, il y a des animaux dont le sexe n'est pas encore défini). Ces animaux ont un sexe "NULL".

Le problème est que le "WITH ROLLUP" ajoute des "totaux" qui ont "NULL" dans certaines colonnes.

L'idée est donc de remplacer les "NULL" pour le sexe par une mention du type "Sans Sexe" par exemple et les "NULL" pour les totaux par une mention du type "Total".

J'ai réussi à faire le remplacement avec la requête suivante :

SELECT IF(Animal.sexe IS NULL, 'Sans sexe', Animal.sexe) AS sexe, Race.nom, Espece.nom_courant, COUNT(*) AS nombre
FROM Animal
LEFT JOIN Espece ON Animal.espece_id = Espece.id
LEFT JOIN Race ON Animal.race_id = Race.id
GROUP BY Espece.nom_courant, Race.nom, Animal.sexe WITH ROLLUP;

mais malheureusement, je n'ai pas trouvé comment distinguer les deux types de "NULL". L

Toutes les valeurs "NULL" visées sont remplacées par "sans sexe".

Une idée ? (Pour ceux qui voudraient tester, les requêtes de création de la base de données [Base de donnée : elevage ; tables : Animal, Race, Espece] sont redonnées dans la partie 2 chapitre 1 "Index" au début puis un autre morceau à partir du milieu du chapitre 2 )

A moins qu'il faille utiliser une auto-jointure ou une sous-requête afin de d'abord remplacer les "NULL" pour le sexe puis ensuite remplacer les "NULL" pour les totaux...

-
Edité par Un débutant 10 juillet 2019 à 18:42:58

Bonjour,

Je pense qu'il faut faire le GROUP BY sur le nom "recalculé" et non sur la colonne "brute".

SELECT
	IF( A.sexe IS NULL, 'Sans sexe', A.sexe ) AS sexe,
	R.nom,
	E.nom_courant,
	COUNT(*) AS nombre
FROM
	Animal A
		LEFT JOIN Espece E
			ON A.espece_id = E.id
		LEFT JOIN Race R
			ON A.race_id = R.id
GROUP BY
	E.nom_courant,
	R.nom,
	IF( A.sexe IS NULL, 'Sans sexe', A.sexe )
WITH ROLLUP;

SELECT IF(Animal.sexe IS NULL, 'Sans sexe', Animal.sexe) AS sexe, Race.nom, Espece.nom_courant, COUNT(*) AS nombre
FROM Animal
LEFT JOIN Espece ON Animal.espece_id = Espece.id
LEFT JOIN Race ON Animal.race_id = Race.id
GROUP BY Espece.nom_courant, Race.nom, sexe WITH ROLLUP;

Bonjour.

Merci pour vos propositions.

Je suis passé par une sous-requête :

SELECT COALESCE(new.sexe, 'Total'), Race.nom, Espece.nom_courant, COUNT(*) AS nombre
FROM (
    SELECT IF(Animal.sexe IS NULL, 'Sans sexe', Animal.sexe) AS sexe, Animal.espece_id, Animal.race_id
    FROM Animal
) AS new
LEFT JOIN Espece ON new.espece_id = Espece.id
LEFT JOIN Race ON new.race_id = Race.id
GROUP BY Espece.nom_courant, Race.nom, sexe WITH ROLLUP;

Je pense qu'on pourrait encore l'améliorer pour faire le sous-total d'une race, puis le total d'une espèce, puis le total final. Mais cela demande de trouver plus de "critères" pour les sous-requêtes imbriquées.

D'une part, ce n'est pas très propre de faire un GROUP BY sur une colonne de la sous-requête plutôt que sur une colonne de la requête principale.

D'autre part, tu n'as absolument pas besoin de faire une sous-requête pour ça.

Tu as essayé les requêtes qu'on t'a proposées ?

Par contre l'utilisation de COALESCE est une bonne idée.

Bonjour.

Oui, j'ai essayé vos requêtes.

Cela remplace bien les NULL par "sans sexe", sauf que cela remplace tous les NULL. Or, dans cette situation, certains "NULL" sont attachés à un "vrai" NULL : pas de valeur car ni femelle, ni mâle, donc pas de valeur.

D'autres "NULL" sont ici juste pour remplir la case : il s'agit des cases où devrait être écrit "total" ou "sous-total" puisque c'est associé à la somme intermédiaire ou totale réalisée avec le WITH ROLL UP.

L'intérêt de la sous-requête est que justement, j'ai réussi à distinguer les deux "types" de NULL.

En quoi n'est-ce pas propre ? Je ne comprends pas très bien : le GROUP BY selon Espece.nom_courant est fait sur le premier SELECT, donc la requête principale, non ?

Ensuite, comme je l'ai lu dans le cours, peut-être que je pourrais passer par une auto-jointure au lieu d'une sous-requête, mais je ne suis pas sûr que cela marche : j'ai la sensation qu'il faut d'abord traiter un des deux types de NULL pour s'atteler au deuxième ensuite, d'où l'idée de la sous-requête.

Un débutant a écrit:

En quoi n'est-ce pas propre ? Je ne comprends pas très bien : le GROUP BY selon Espece.nom_courant est fait sur le premier SELECT, donc la requête principale, non ?

En principe la clause GROUP BY permet d'agréger certaines des colonnes qui seront affichées. Autrement dit, on regroupe les colonnes du premier SELECT uniquement.
En SQL standard, tu ne peux même pas utiliser GROUP BY sur un alias, même s'il est dans ton SELECT principal !
MySQL permet de faire tout ça parce qu'il est plus laxiste.

Si tu commences à utiliser GROUP BY pour des colonnes implicites qui ne figurent pas dans l'affichage final, typiquement des colonnes de ta requête imbriquée, alors tu sors des sentiers battus, tu ne peux pas être complètement sûr de ce que tu fais et ta requête sera beaucoup plus difficile à comprendre à la relecture. À vrai dire je ne pensais même pas que ça marchait.
Je n'irais pas jusqu'à dire que c'est une erreur, mais quand on code on essaye toujours de garder un code le plus clair possible. Faire un regroupement sur une colonne implicite, c'est trop tordu pour être "propre".

Par contre je viens de découvrir que les versions récentes de MySQL proposent une fonction GROUPING() qui fait exactement ce que tu recherches :

The NULL values do appear as NULL on the client side and
can be tested as such using any MySQL client programming
interface. However, at this point, you cannot
distinguish whether a NULL represents a regular grouped
value or a super-aggregate value. To test the distinction,
use the GROUPING() function, described later.

For GROUP BY ... WITH ROLLUP queries, to test whether NULL
values in the result represent super-aggregate values, the
GROUPING() function is available for use in the select
list, HAVING clause, and (as of MySQL 8.0.12) ORDER BY
clause. For example, GROUPING(year) returns 1 when NULL in
the year column occurs in a super-aggregate row, and 0
otherwise. Similarly, GROUPING(country) and
GROUPING(product) return 1 for super-aggregate NULL values
in the country and product columns, respectively (...).
(...) you can use GROUPING() to substitute
labels for super-aggregate NULL values:      

mysql> SELECT
         IF(GROUPING(year), 'All years', year) AS year,
         IF(GROUPING(country), 'All countries', country) AS country,
         IF(GROUPING(product), 'All products', product) AS product,
         SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP;
+-----------+---------------+--------------+--------+
| year      | country       | product      | profit |
+-----------+---------------+--------------+--------+
| 2000      | Finland       | Computer     |   1500 |
| 2000      | Finland       | Phone        |    100 |
| 2000      | Finland       | All products |   1600 |
| 2000      | India         | Calculator   |    150 |
| 2000      | India         | Computer     |   1200 |
| 2000      | India         | All products |   1350 |
| 2000      | USA           | Calculator   |     75 |
| 2000      | USA           | Computer     |   1500 |
| 2000      | USA           | All products |   1575 |
| 2000      | All countries | All products |   4525 |
| 2001      | Finland       | Phone        |     10 |
| 2001      | Finland       | All products |     10 |
| 2001      | USA           | Calculator   |     50 |
| 2001      | USA           | Computer     |   2700 |
| 2001      | USA           | TV           |    250 |
| 2001      | USA           | All products |   3000 |
| 2001      | All countries | All products |   3010 |
| All years | All countries | All products |   7535 |
+-----------+---------------+--------------+--------+

-
Edité par Zachee54 18 juillet 2019 à 10:32:05

Merci pour la découverte de GROUPING ... Dommage que ce ne soit pas au standard SQL ...

Re.

Ah oui, effectivement, c'est pile poil ce que je cherche et je peux même faire plus que "deux" distinctions.

Par contre, vous m'excuserez pour ma lenteur d'esprit mais je ne comprends toujours pas un point. J'entends tout ce que tu dis Zachee mais dans mon GROUP BY, il ne me semble pas y avoir de colonne "implicite" puisque Espece.nom_courant, Race.nom et sexe sont bien appelées dans le premier SELECT.

Certes, j'utilise un alias "sexe" pour désigner la colonne "sexe" de ma table intermédiaire "new" mais il me semble bien sélectionner des colonnes sur lesquelles je "group by" ensuite.

Un éclairage pour l'amateur que je suis ?

Je reprends les éléments de ta requête :

SELECT ...
FROM (
    SELECT ... AS sexe, ...
    FROM ...
) AS new
...
GROUP BY ..., sexe WITH ROLLUP;

L'alias "sexe" que tu mentionnes dans GROUP BY est celui d'un champ de ta requête imbriquée. Il n'y a rien qui porte ce nom-là dans le SELECT principal.

Il y a 12 ans, mon travail consistait à produire des statistiques à partir d'un logiciel de requêtage SQL.
Un jour, j'ai eu des chiffres complètement faux parce que je faisais des agrégats sur des champs calculés qui ne correspondaient pas aux colonnes principales. Il m'a fallu longtemps pour m'en rendre compte et comprendre que mes champs calculés comportaient des valeurs aberrantes qui perturbaient le calcul des agrégats.
Heureusement que je n'avais pas envoyé les chiffres à mon chef les yeux fermés !

Ta requête est très bien pour ce que tu voulais faire. Mais personnellement, j'y aurais renoncé à cause du GROUP BY alambiqué et j'aurais préféré la requête de Benzouye, qui marche très bien même si elle n'écrit pas "Total" sur les lignes de rollup. Le mieux est parfois l'ennemi du bien.

-
Edité par Zachee54 18 juillet 2019 à 12:38:10

Ok...

Je vais essayer de reformuler pour voir si j'ai compris ce que tu dis.

Le problème est que le "sexe" du GROUP BY est associée à l'alias "sexe" utilisée dans la requête imbriquée alors que je demande la colonne "new.sexe" dans le SELECT principal.

La question "débile" : si maintenant je mets dans le GROUP BY "new.sexe", présent dans le SELECT principal, au lieu de "sexe", est-ce toujours le même problème ou pas ?

Dit autrement, est-ce simplement un problème de changer "sexe" (alias dans la requête imbriquée) en "new.sexe"  ("vraie" colonne de la table intermédiaire de la requête principale), ou bien même avec ce changement, le problème reste-t-il plus "profond" dans le sens où la colonne "sexe" dans la table intermédiaire "new" est de toute façon attachée à la sous-requête ?

Je ne sais pas si ce que je dis est clair...

En gros, le problème se situe-t-il dans le fait d'utiliser une sous-requête ou bien "seulement" dans le choix de la colonne utilisée dans le group by ("sexe" et "new.sexe" désignent la même chose, ce serait donc un problème de syntaxe...) ?

Au vu de la réponse que tu fais, je présume que c'est la sosu-requête en tant que telle qui pose souci... ou alors il y a moyen d'utiliser une sous-requête mais écrite différemment (si on omet bien entendu la solution du GROUPING) ?

Un débutant a écrit:

En gros, le problème se situe-t-il dans le fait d'utiliser une sous-requête ou bien "seulement" dans le choix de la colonne utilisée dans le group by (...) ?

Le problème est seulement dans le choix de la colonne utilisée dans le group by.
Ce n'est heureusement pas un problème d'utiliser des requêtes imbriquées ! Mais group by est fait pour désigner des colonnes de la requête principale, et il vaut toujours mieux l'employer pour ce pour quoi il est fait.

Un débutant a écrit:

"sexe" et "new.sexe" désignent la même chose, ce serait donc un problème de syntaxe...

Non, elles ne désignent pas la même chose. La preuve, c'est que tu as eu besoin de les séparer pour obtenir ce que tu voulais.

La colonne de ta requête principale utilise coalesce et rajoute des 'Total' dans les lignes de rollup.
Imagine maintenant que quelqu'un (ou toi-même, dans 6 mois) décide de raffiner la colonne "sexe" de la requête principale en ajoutant une condition, ou en affichant un texte différent suivant l'espèce de l'animal, sa date de naissance ou sa provenance... (peu importe, je sais que ce n'est pas très crédible parce que tu n'en es pas là pour l'instant).
Tu vas alors te retrouver avec une colonne affichée, mais qui sera regroupée selon les valeurs de la colonne new.sexe qui a des valeurs beaucoup moins disparates. Et patatras ! Il va te manquer plein de valeurs dans la colonne affichée.
C'est comme ça que j'ai eu des résultats faux dans une requête que je croyais connaître parce que je la manipulais tous les jours.

Quand c'est à l'affichage, on s'en rend compte assez vite. Mais quand c'est pour faire une sum(), ou un count(), ou un average()... l'erreur peut passer complètement inpaerçue.

Alors oui, bien sûr, "on peut" faire comme ça, ça a "peu de chances" d'arriver. Je te mets simplement en garde sur le fait que c'est une mauvaise pratique qui risque de te jouer un tour un jour ou l'autre.
D'autant que comme c'est très éloigné du standard SQL, tu vas aussi être embêté si tu changes de SGBD.

D'accord.

Ce que j'ai dit est effectivement faux.

Les deux colonnes sont différentes puisque celle de la requête imbriquée contient M, F ou sans sexe alors que celle de la requête principale contient en plus total.

Quand je disais que cela désignait la même chose, j'ai voulu dire, maladroitement, qu'il s'agissait de la même colonne mais avec un peu plus de "raffinement", pour reprendre ce que tu disais. Mais ajouter un group by sur cette colonne est un peu plus que du raffinement je présume, puisqu'on fait des sommes au lieu de "seulement" changer les premiers types de "NULL" par "sans sexe".

Donc ce n'est pas pareil.

Alors si au final, je mets dans le group by new.sexe au lieu de sexe, cela respectera les standards SQL et évitera ce que tu anticipes comme problème ?

Oui... sauf que le résultat obtenu n'est pas celui que tu recherchais !
À la place du mot 'Total', tu n'as que des 'NULL'.

Donc on a 3 solutions :

• utiliser GROUPING(), de loin la meilleure solution si tu es sûr de rester sur MySQL (parce qu'elle ne fait pas partie des standards)
• utiliser ta requête telle quelle, mais ce n'est pas très académique
• utiliser ta requête en désignant la colonne principale dans GROUP BY, et là on a bien le texte 'Sans sexe' mais pas 'Total'. Si tu veux le mot 'Total', ça t'oblige à le faire ajouter par l'application qui traitera les données reçues, par exemple PHP si tu fais un site internet.

Mhmhmh...

Je suis désolé si je ne comprends pas, mais lorsque je fais la requête initiale avec sexe dans le group by ou la requête suivante avec new.sexe dans le group by, j'obtiens la même chose pour les résultats :

- requête initiale :

SELECT COALESCE(new.sexe, 'Total'), Race.nom, Espece.nom_courant, COUNT(*) AS nombre
FROM (
    SELECT IF(Animal.sexe IS NULL, 'Sans sexe', Animal.sexe) AS sexe, Animal.espece_id, Animal.race_id
    FROM Animal
) AS new
LEFT JOIN Espece ON new.espece_id = Espece.id
LEFT JOIN Race ON new.race_id = Race.id
GROUP BY Espece.nom_courant, Race.nom, sexe WITH ROLLUP;

- nouvelle requête :

SELECT COALESCE(new.sexe, 'Total'), Race.nom, Espece.nom_courant, COUNT(*) AS nombre
FROM (
    SELECT IF(Animal.sexe IS NULL, 'Sans sexe', Animal.sexe) AS sexe, Animal.espece_id, Animal.race_id
    FROM Animal
) AS new
LEFT JOIN Espece ON new.espece_id = Espece.id
LEFT JOIN Race ON new.race_id = Race.id
GROUP BY Espece.nom_courant, Race.nom, new.sexe WITH ROLLUP;

Ou alors quelque chose m'échappe encore...

Oui, c'est parce que tu as une seule colonne qui a un alias "sexe", et c'est celle de la requête imbriquée.
La colonne de ta requête principale n'a pas d'alias ; il faut lui en donner un pour y faire référence.

SELECT COALESCE(new.sexe, 'Total') AS sexe_total, Race.nom, Espece.nom_courant, COUNT(*) AS nombre
FROM (
    SELECT IF(Animal.sexe IS NULL, 'Sans sexe', Animal.sexe) AS sexe, Animal.espece_id, Animal.race_id
    FROM Animal
) AS new
LEFT JOIN Espece ON new.espece_id = Espece.id
LEFT JOIN Race ON new.race_id = Race.id
GROUP BY Espece.nom_courant, Race.nom, sexe_total WITH ROLLUP;

Ah oui... que je suis bête... pardon...

L'alias "sexe" ou "new.sexe" désignent la même chose (enfin, si j'ai bien compris...) mais l'alias "sexe_total" désigne la "nouvelle" colonne issue du SELECT principal.

Je pense que le "ce sont les mêmes choses" de tout à l'heure vient de cette confusion : la colonne new.sexe est différente de la colonne COALESCE(new.sexe, 'Total'), enfin en théorie...

Parce que là... Si mettre un alias à cette colonne empêche l'affichage de "Total" depuis le COALESCE, mon COALESCE ne sert donc à rien ?

Pourquoi le COALESCE ne "fonctionne" plus ?

Un débutant a écrit:

Ah oui... que je suis bête... pardon...

Un débutant a écrit:

L'alias "sexe" ou "new.sexe" désignent la même chose (enfin, si j'ai bien compris...)

Exactement. Pour le coup il s'agit de la même colonne, avec ou sans précision de la table d'origine.
S'il y avait plusieurs colonnes avec l'alias "sexe", MySQL t'aurait dit que c'était ambigu.

Un débutant a écrit:

Je pense que le "ce sont les mêmes choses" de tout à l'heure vient de cette confusion : la colonne new.sexe est différente de la colonne COALESCE(new.sexe, 'Total'), enfin en théorie...

Elles sont différentes par principe puisque la deuxième est un calcul à partir de l'autre.
Rien n'empêche qu'elles aient les mêmes valeurs... mais ce sont quand même deux colonnes distinctes.

Un débutant a écrit:

Pourquoi le COALESCE ne "fonctionne" plus ?

COALESCE fonctionne bien, mais puisque tu as supprimé toutes les valeurs NULL dans new.sexe en les remplaçant par 'Sans sexe', alors il n'y a plus de valeur NULL et sexe_total ne vaut jamais 'Total'.

La question est plutôt : pourquoi ça fonctionnait avant ?
Parce que l'algorithme construisait la requête dans cet ordre :

• Remplacement des NULL de la colonne animal.sexe par 'Sans sexe' --> cela donne une nouvelle colonne avec l'alias new.sexe
• Regroupement en fonction des valeurs de new.sexe et insertion de lignes de total, dans lesquelles la colonne new.sexe affiche NULL.
• Affichage d'une nouvelle colonne sexe_total qui vaut la même chose que new.sexe, sauf pour les lignes où elle est NULL (c'est-à-dire les lignes de total), où on affiche 'Total'.

En fait c'est une super trouvaille du point de vue technique, ce regroupement qui se fait sur une requête imbriquée et l'affichage qui supprime les NULL après regroupement !
Je ne pensais pas que ma remarque de départ ferait couler autant d'encre.
C'est seulement qu'on peut construire de grands châteaux de cartes qui marchent parfaitement, sans que ce soit pour autant une bonne solution en termes de bonnes pratiques professionnelles. Ca m'a énervé quand on m'a dit ça les premières fois, mais maintenant que je dois relire les programmes que j'ai écrits il y a 7 ans je me dis que c'est fichtrement vrai.

-
Edité par Zachee54 19 juillet 2019 à 9:27:08