Comparer à une valeur calculée

Vous voulez repérer les œuvres les plus empruntées, mais un seuil choisi au hasard ne permet pas de les comparer au niveau général de la médiathèque. Vous allez calculer le nombre d’emprunts de chaque œuvre et le confronter à une moyenne calculée par SQL. Le résultat dépend toutefois de la population choisie pour cette moyenne : ici, toutes les œuvres sont prises en compte, y compris celles sans emprunt.

Définir la moyenne de référence

Une sous-requête scalaire est une requête placée à l’intérieur d’une autre requête et qui fournit une seule valeur. Dans cette leçon, la requête principale calcule le nombre d’emprunts associé à l’œuvre courante ; la sous-requête fournit la moyenne générale à comparer à ce nombre. Vous avez déjà vu les jointures et les regroupements nécessaires dans les leçons précédentes : ils ne sont pas redéveloppés ici.

La moyenne du nombre d’emprunts par œuvre peut être obtenue en divisant le nombre total d’emprunts par le nombre total d’œuvres. Cette forme est équivalente à la moyenne des comptes individuels lorsque chaque œuvre compte dans le dénominateur, même si son compte vaut zéro. Elle évite d’écarter silencieusement les œuvres sans emprunt.

-- Nombre d'emprunts par œuvre, comparé à la moyenne de toutes les œuvres.
SELECT
    o.oeuvre_id,
    o.titre,
    COUNT(e.emprunt_id) AS nombre_emprunts
FROM oeuvres AS o
LEFT JOIN exemplaires AS ex
    ON ex.oeuvre_id = o.oeuvre_id
LEFT JOIN emprunts AS e
    ON e.exemplaire_id = ex.exemplaire_id
GROUP BY
    o.oeuvre_id,
    o.titre
HAVING COUNT(e.emprunt_id) > (
    SELECT
        AVG(comptes.nombre_emprunts)
    FROM (
        SELECT
            o2.oeuvre_id,
            COUNT(e2.emprunt_id) AS nombre_emprunts
        FROM oeuvres AS o2
        LEFT JOIN exemplaires AS ex2
            ON ex2.oeuvre_id = o2.oeuvre_id
        LEFT JOIN emprunts AS e2
            ON e2.exemplaire_id = ex2.exemplaire_id
        GROUP BY o2.oeuvre_id
    ) AS comptes
)
ORDER BY nombre_emprunts DESC, o.titre;

Exemple de résultat, selon les données présentes :
+-----------+----------------------+-----------------+
| oeuvre_id | titre                | nombre_emprunts |
+-----------+----------------------+-----------------+
| 12        | Le jardin des saisons| 5               |
| 4         | Cartes du monde      | 3               |
+-----------+----------------------+-----------------+

Seules les œuvres dont le compte dépasse la moyenne sont affichées.

Cette première écriture rend le calcul explicite, mais elle utilise une table dérivée dans la sous-requête : cette notion est réservée à la leçon « Joindre une table dérivée ». Pour rester sur la notion de sous-requête scalaire présentée ici, vous pouvez calculer la moyenne comme le total des emprunts divisé par le nombre d’œuvres. Les deux comptes sont des sous-requêtes qui renvoient chacun une valeur.

Comparer les comptes dans WHERE

-- Version sans table dérivée : la moyenne inclut les œuvres à zéro emprunt.
SELECT
    o.oeuvre_id,
    o.titre,
    (
        SELECT COUNT(*)
        FROM emprunts AS e
        INNER JOIN exemplaires AS ex
            ON ex.exemplaire_id = e.exemplaire_id
        WHERE ex.oeuvre_id = o.oeuvre_id
    ) AS nombre_emprunts
FROM oeuvres AS o
WHERE (
    SELECT COUNT(*)
    FROM emprunts AS e
    INNER JOIN exemplaires AS ex
        ON ex.exemplaire_id = e.exemplaire_id
    WHERE ex.oeuvre_id = o.oeuvre_id
) > (
    SELECT COUNT(*) / NULLIF(COUNT(*), 0)
    FROM emprunts
    CROSS JOIN oeuvres
)
ORDER BY nombre_emprunts DESC, o.titre;

La requête renvoie les œuvres dont le nombre d’emprunts est supérieur au total des emprunts divisé par le nombre d’œuvres. Si la base contient 24 emprunts et 8 œuvres, la moyenne vaut 3 ; seules les œuvres ayant plus de 3 emprunts sont retenues.

Dans chaque sous-requête de comptage, la condition ex.oeuvre_id = o.oeuvre_id relie le calcul à l’œuvre de la ligne extérieure. Le compte local est donc recalculé pour l’œuvre examinée ; la sous-requête de droite, elle, calcule une référence globale. La comparaison est stricte : une œuvre qui atteint exactement la moyenne n’apparaît pas. Remplacez > par >= si votre règle métier doit inclure l’égalité.

Pour vérifier le seuil séparément, adaptez les comptes aux données de votre base. Par exemple, avec 24 emprunts au total et 8 œuvres, la moyenne est de 3 emprunts par œuvre, zéro compris. Les alias de colonnes rendent le résultat lisible, mais ils ne remplacent pas les colonnes nécessaires au calcul de la sous-requête dans WHERE.

À retenir

• Une sous-requête scalaire fournit une valeur utilisable dans une comparaison de WHERE.
• Définissez clairement la population qui compose la moyenne ; inclure toutes les œuvres inclut aussi les comptes nuls.
• Comparez le compte de l’œuvre courante à une référence globale, en choisissant entre > et >= selon la règle attendue.
• La leçon suivante utilisera EXISTS pour tester la présence d’emprunts sans produire une ligne par correspondance.