Calculer des mesures par œuvre

Un rapport d’inventaire doit distinguer les exemplaires en rayon de ceux qui sont actuellement empruntés. Si vous reliez les œuvres, les exemplaires et tous les emprunts historiques, une même copie peut apparaître plusieurs fois et fausser les totaux. Vous allez construire une mesure par œuvre en limitant la jointure aux emprunts en cours.

Définir les mesures par œuvre

Le rapport aura une ligne par œuvre. Pour chaque œuvre, il affichera son titre, le nombre total d’exemplaires, le nombre d’exemplaires actuellement empruntés et le nombre d’exemplaires disponibles. On suppose ici que emprunts.date_retour vaut NULL tant que l’exemplaire n’a pas été rendu, et qu’un exemplaire ne peut avoir qu’un seul emprunt en cours à la fois.

Le chemin suivi est celui déjà étudié : une œuvre possède des exemplaires, et un emprunt concerne un exemplaire. Vous partez des œuvres et utilisez des LEFT JOIN pour conserver aussi celles qui ne possèdent encore aucune copie. La condition qui limite la jointure aux emprunts actifs est placée dans ON : les emprunts rendus ne participent donc pas aux mesures.

SELECT
    o.id AS oeuvre_id,
    o.titre,
    COUNT(ex.id) AS nombre_exemplaires,
    COALESCE(
        SUM(CASE WHEN e.id IS NOT NULL THEN 1 ELSE 0 END),
        0
    ) AS exemplaires_empruntes,
    COUNT(ex.id) - COALESCE(
        SUM(CASE WHEN e.id IS NOT NULL THEN 1 ELSE 0 END),
        0
    ) AS exemplaires_disponibles
FROM oeuvres AS o
LEFT JOIN exemplaires AS ex
    ON ex.oeuvre_id = o.id
LEFT JOIN emprunts AS e
    ON e.exemplaire_id = ex.id
    AND e.date_retour IS NULL
GROUP BY
    o.id,
    o.titre
ORDER BY
    o.titre;

oeuvre_id | titre                  | nombre_exemplaires | exemplaires_empruntes | exemplaires_disponibles
1         | Le Rivage des Syrtes    | 3                  | 1                     | 2
2         | Les Villes invisibles   | 2                  | 2                     | 0
3         | Atlas des oiseaux       | 0                  | 0                     | 0

Lire l’agrégation conditionnelle

CASE teste chaque ligne produite par les jointures. Lorsqu’un emprunt actif est associé à l’exemplaire, l’expression renvoie 1 ; sinon, elle renvoie 0. SUM additionne ensuite ces valeurs dans le groupe de l’œuvre, ce qui fournit le nombre d’exemplaires empruntés.

Le nombre total de copies est calculé avec COUNT(ex.id), qui ignore la ligne NULL créée par l’absence d’exemplaire. Le nombre disponible est la différence entre le total et le nombre emprunté. Ainsi, une œuvre sans exemplaire obtient zéro pour ces deux mesures, plutôt qu’un total artificiel égal à un.

Traiter les valeurs NULL

Pour une œuvre sans exemplaire, la jointure ne fournit aucun emprunt et l’expression agrégée peut produire NULL. COALESCE(valeur, 0) remplace ce résultat par zéro, afin que le rapport affiche une quantité exploitable. Vous retrouvez ensuite cette mesure dans le calcul des exemplaires disponibles.

Le résultat présenté est un exemple : les nombres affichés dépendent des données réellement présentes dans vos tables. Avant d’utiliser le rapport, vérifiez que la règle métier sur les emprunts actifs est respectée et que la date de retour est renseignée à la restitution. Pour contrôler les totaux, comparez quelques œuvres avec leurs exemplaires et leurs emprunts en cours.

À retenir

• Partez des œuvres avec des jointures gauches pour conserver celles sans exemplaire.
• Limitez la jointure des emprunts aux emprunts en cours pour exclure l’historique.
• Utilisez SUM(CASE ...) pour additionner une mesure définie conditionnellement.
• Appliquez COALESCE pour transformer en zéro un résultat NULL lorsque le rapport attend une quantité.
• Vérifiez les hypothèses métier qui garantissent qu’un exemplaire n’a pas plusieurs emprunts actifs.