Conserver un total correct pour chaque commande

Introduction

PAPER & PIER vend de petits articles sur deux stands de marché. Avant la fermeture, son poste de vente doit rapprocher la valeur des articles de chaque commande des montants réellement encaissés et remboursés. Une commande peut contenir plusieurs articles et plusieurs paiements réels. Joindre ces listes brutes sans précaution peut donner l’impression qu’une commande correctement payée présente une anomalie.

Vous créerez trois vues SQLite réutilisables qui totalisent séparément chaque type de fait. Vous devez savoir naviguer dans un terminal et connaître SELECT/WHERE en SQL. Les agrégats, les jointures, les vues et les valeurs manquantes sont expliqués ici. SQLite et un registre fictif sont prêts ; utilisez Terminal tout au long du TP.

Conservez toutes les tables, tous les identifiants et toutes les valeurs d’origine. Vous pouvez uniquement créer ou remplacer les vues item_totals, payment_totals et refund_totals, et enregistrer leurs définitions dans totals.sql. Les montants sont des centimes entiers ; il s’agit d’un rapprochement simplifié des encaissements, sans taxes, règlement par un prestataire de paiement ni conversion de devises.

Conserver un seul total d’articles par commande

Dans cette étape, vous lirez une commande ordinaire, verrez comment une jointure brute duplique les montants et enregistrerez une vue correcte du total des articles. Commencez dans Terminal. cd change de répertoire ; ce chemin absolu permet aussi de revenir au bon endroit après être entré dans un mauvais répertoire :

cd /home/labex/project/sales-desk

sqlite3 ouvre la base nommée. -header affiche les noms des colonnes, -column aligne les valeurs et le SQL entre guillemets s’exécute avant la fermeture de la connexion. Les guillemets conservent les espaces et les points-virgules du SQL dans un seul argument du shell. Lisez les identités des commandes préparées :

sqlite3 -header -column ledger.db "SELECT * FROM orders ORDER BY order_id;"

Il y a quatre commandes. A1 et A2 sont des commandes différentes, même si les deux libellés client indiquent Alex Kim. Regroupez par identifiant de commande, jamais par nom de client. Lisez les lignes d’articles et les événements de paiement d’A1 :

sqlite3 -header -column ledger.db "SELECT * FROM items WHERE order_id = 'A1'; SELECT * FROM payments WHERE order_id = 'A1';"

Ses articles coûtent 300 + 500 = 800 centimes. Les paiements P1 et P2 sont deux événements distincts et réussis de 400 centimes, soit également 800 au total. P3 a échoué et n’a apporté aucun montant. succeeded signifie que l’événement compte dans les calculs de ce projet ; pending et failed ne comptent pas.

Une jointure combine des lignes liées. AS attribue un alias court à une table : i désigne les articles et p les paiements. ON précise les correspondances d’identifiants de commande. Avant tout total, observez chaque paire correspondante pour A1 :

sqlite3 -header -column ledger.db "
SELECT i.item_id, i.unit_cents, p.payment_id, p.amount_cents
FROM items AS i
JOIN payments AS p ON p.order_id = i.order_id
WHERE i.order_id = 'A1' AND p.status = 'succeeded'
ORDER BY i.item_id, p.payment_id;"

Il y a quatre lignes : chacun des deux articles correspond aux deux paiements. C’est le véritable comportement d’une jointure. Cela ne signifie pas qu’il y a eu quatre articles ou quatre paiements. SUM additionne l’expression sur ses lignes d’entrée. Avec ces entrées dupliquées, les deux sommes sont fausses :

sqlite3 -header -column ledger.db "
SELECT SUM(i.quantity * i.unit_cents) AS joined_due,
       SUM(p.amount_cents) AS joined_paid
FROM items AS i
JOIN payments AS p ON p.order_id = i.order_id
WHERE i.order_id = 'A1' AND p.status = 'succeeded';"

Les deux totaux affichés sont 1600, soit deux fois le véritable montant. DISTINCT conserve les valeurs différentes, pas les identités de paiement différentes. Il ne peut pas corriger ce problème :

sqlite3 -header -column ledger.db "
SELECT SUM(DISTINCT amount_cents) AS wrong_paid
FROM payments WHERE order_id = 'A1' AND status = 'succeeded';"

Le résultat est 400 : un paiement légitime de même montant a été écarté. Totalisez plutôt chaque liste de faits séparément. GROUP BY order_id produit une ligne de résultat pour chaque identifiant de commande. quantity * unit_cents calcule la valeur de chaque article avant l’addition. La granularité obtenue, c’est-à-dire ce que représente une ligne de sortie, est une commande.

Une vue est un SELECT nommé enregistré dans la base. La lire exécute ce SELECT sur les lignes sources actuelles ; elle ne copie ni ne remplace les articles. Enregistrez sa définition dans totals.sql. Dans le shell, cat écrit le here-document littéral suivant dans le fichier ; > remplace son ancien contenu. Collez le SQL final seul sur une ligne. Ce bloc doit être saisi à l’invite du shell, pas à une invite SQLite interactive :

cat > totals.sql <<'SQL'
DROP VIEW IF EXISTS item_totals;
CREATE VIEW item_totals AS
SELECT order_id, SUM(quantity * unit_cents) AS due_cents
FROM items
GROUP BY order_id;
SQL

DROP VIEW IF EXISTS supprime uniquement une ancienne définition de cette vue, si elle existe. Les tables et les données sources restent intactes. CREATE VIEW enregistre la nouvelle définition. < fournit le fichier enregistré comme entrée de SQLite :

sqlite3 ledger.db < totals.sql

Aucune sortie n’est attendue en cas de réussite. Lisez la véritable vue :

sqlite3 -header -column ledger.db "SELECT * FROM item_totals ORDER BY order_id;"

Vous devez obtenir A1 = 800, A2 = 250, A3 = 500 et A4 = 200 centimes, chacun sur une ligne. La quantité de deux pour A3 est prise en compte. Vous disposez maintenant d’un premier livrable utile : une définition de vue enregistrée qui peut s’exécuter sur un autre registre compatible.

Si un fichier manque, ls liste les fichiers de ce répertoire. Vérifiez le cd absolu et l’orthographe de totals.sql. Une erreur de syntaxe SQL indique l’instruction en échec ; répétez le bloc de création complet pour remplacer un fichier mal saisi, puis appliquez-le à nouveau. Si le shell attend toujours après le here-document, saisissez la ligne finale SQL manquante. Ne supprimez pas le registre pour récupérer.

Combiner des totaux monétaires indépendants

Dans cette étape, vous ajouterez les totaux des paiements et remboursements réussis au script enregistré, puis combinerez les trois faits à une ligne par commande en conservant les commandes impayées. Commencez dans Terminal, dans /home/labex/project/sales-desk.

La même règle d’agrégation s’applique aux paiements et aux remboursements : chaque événement véritable compte, y compris les montants égaux avec des identifiants différents. Filtrez status = 'succeeded' à l’intérieur du SELECT de chaque fait, avant le regroupement. Un remboursement en attente ne doit pas réduire les montants encaissés. Lisez les données de remboursement :

sqlite3 -header -column ledger.db "SELECT * FROM refunds ORDER BY refund_id;"

A3 possède deux remboursements distincts et réussis de 50 centimes et un remboursement en attente de 250 centimes. Son véritable total remboursé est 100, ni 50 ni 350.

Ajoutez les deux autres définitions de vue. >> ajoute à la fin d’un fichier existant au lieu de remplacer la vue des articles. Si vous répétez cet ajout pour récupérer après une erreur, reconstruisez d’abord totals.sql avec le bloc complet de l’étape précédente, puis ajoutez ce bloc une seule fois :

cat >> totals.sql <<'SQL'
DROP VIEW IF EXISTS payment_totals;
CREATE VIEW payment_totals AS
SELECT order_id, SUM(amount_cents) AS paid_cents
FROM payments
WHERE status = 'succeeded'
GROUP BY order_id;

DROP VIEW IF EXISTS refund_totals;
CREATE VIEW refund_totals AS
SELECT order_id, SUM(amount_cents) AS refunded_cents
FROM refunds
WHERE status = 'succeeded'
GROUP BY order_id;
SQL

sqlite3 ledger.db < totals.sql

Le script enregistré peut être réappliqué : chaque définition est supprimée et recréée à partir des faits sources inchangés. Lisez les vues monétaires :

sqlite3 -header -column ledger.db "SELECT * FROM payment_totals ORDER BY order_id; SELECT * FROM refund_totals ORDER BY order_id;"

Les paiements indiquent A1 = 800, A3 = 600 et A4 = 250. Les remboursements ne contiennent qu’A3 = 100. A2 est absent des deux, car aucun paiement ni remboursement admissible n’existe. L’absence d’un total de faits ne doit pas retirer la commande du rapprochement.

LEFT JOIN conserve chaque ligne située à sa gauche, même sans correspondance à droite. Commencez par orders, puis joignez chaque vue déjà agrégée par identifiant de commande. Une correspondance manquante fournit SQL NULL, qui signifie aucune valeur, plutôt que zéro. COALESCE(value, 0) remplace par zéro uniquement lorsque ce total est NULL. Les conditions sur l’état des événements se trouvent déjà à l’intérieur des vues monétaires ; ajouter une condition WHERE sur une ligne jointe manquante pourrait éliminer la commande impayée.

sqlite3 -header -column ledger.db "
SELECT o.order_id, o.customer,
       COALESCE(i.due_cents, 0) AS due_cents,
       COALESCE(p.paid_cents, 0) AS paid_cents,
       COALESCE(r.refunded_cents, 0) AS refunded_cents
FROM orders AS o
LEFT JOIN item_totals AS i ON i.order_id = o.order_id
LEFT JOIN payment_totals AS p ON p.order_id = o.order_id
LEFT JOIN refund_totals AS r ON r.order_id = o.order_id
ORDER BY o.order_id;"

Interprétez chaque ligne dans le contexte du fonctionnement normal :

Chaque vue de faits possède au plus une ligne par commande avant les jointures ; un fait ne peut donc pas en multiplier un autre. Conservez les quatre tables d’origine et tous leurs enregistrements ; les trois vues et totals.sql sont les seules définitions de base et le seul fichier à ajouter. Le prochain TP démarre avec son propre registre neuf et transforme ce raisonnement en requête complète de rapprochement.

Résumé

Vous avez mis en évidence deux pièges dans les totaux monétaires, enregistré des vues de faits réutilisables à une ligne par commande et les avez combinées avec LEFT JOIN et COALESCE. Les événements distincts de même montant sont restés des événements réels, et les commandes impayées sont restées visibles.