Cours MariaDB / Vérifier ventes mensuelles, achats clients et produits invendus
Les ventes mensuelles, les achats par client et les produits invendus n’ont pas la même unité de résultat, même lorsqu’ils utilisent les mêmes données. Définissez le statut retenu, les montants inclus et le sens d’« invendu », puis vérifiez que les agrégats mensuels et ceux des clients correspondent au même total de produits.
Définir les indicateurs avant d’écrire les rapports
Dans cet exercice, le montant vendu est la quantité multipliée par le prix unitaire enregistré lors de l’achat, uniquement pour les détails des commandes paid. Les frais de livraison et les commandes annulées sont exclus. Il s’agit d’un exercice de requêtes, pas d’une politique complète de reconnaissance comptable du revenu, des remboursements ou des taxes.
Un produit invendu désigne ici un produit enregistré qui n’est apparu dans aucune commande payée. Le guide GROUP BY et HAVING explique les regroupements, et les jointures multiples sans double comptage aident à vérifier la multiplication des lignes.
| Rapport | Une ligne représente | Population retenue |
|---|---|---|
| Ventes mensuelles | Une année et un mois | Détails payés en 2026 |
| Achats par client | Un client | Tous les clients, y compris ceux à montant nul |
| Produits invendus | Un produit | Absence d’historique dans les commandes payées |
Notez aussi la période de chaque indicateur. Le rapport mensuel ci-dessous filtre 2026 ; la requête par client additionne l’historique payé sans filtre de date, et la recherche d’invendus porte également sur tout l’historique. Les montants se réconcilient dans ce jeu de données parce que toutes ses commandes datent de 2026. Avec d’autres années, alignez explicitement les périodes avant d’exiger des totaux identiques.
Préparer des commandes que l’on peut vérifier à la main
Connectez-vous au serveur d’apprentissage avec course_user dans shopcourse. Le compte doit pouvoir créer, remplir, lire et nettoyer les objets d’exercice. Le guide du client MariaDB et des fichiers SQL UTF-8 présente la connexion. Vérifiez le serveur et la base avec SELECT VERSION(), DATABASE();, puis les noms avec SHOW TABLES;.
Les tables c42_customers, c42_products, c42_orders et c42_items sont propres à cet exercice. Si elles existent, examinez les définitions et les lignes et sauvegardez ce qui doit être conservé avant toute suppression. Ne remplacez pas des données utiles.
Créez un dossier, puis enregistrez le bloc complet suivant en UTF-8, par exemple dans C:/mariadb-lab/c42-rapports.sql. Dans le client MariaDB, lancez SOURCE C:/mariadb-lab/c42-rapports.sql; avec le chemin réel. Le SQL s’exécute dans la connexion au serveur, pas directement dans l’invite du système.
USE shopcourse;
CREATE TABLE c42_customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c42_products (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c42_orders (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
ordered_at DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
CONSTRAINT fk_c42_order_customer FOREIGN KEY (customer_id)
REFERENCES c42_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c42_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_c42_item_order FOREIGN KEY (order_id)
REFERENCES c42_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c42_item_product FOREIGN KEY (product_id)
REFERENCES c42_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c42_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c42_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c42_orders
(order_id,customer_id,ordered_at,status,shipping_fee) VALUES
(101,1,'2026-01-10 10:00:00','paid',1000),
(102,1,'2026-01-20 11:00:00','paid',1000),
(103,2,'2026-02-05 12:00:00','paid',1000),
(104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c42_items VALUES
(101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
(103,1,1,3500),(103,3,2,8000),(104,2,1,1500);
Les tables contiennent quatre clients, quatre produits, quatre commandes et six détails. Les chaînes coréennes restent identiques entre langues pour reproduire les résultats. Les produits 1 à 4 correspondent à un carnet, un stylo, une tasse et une lampe ; les clients 1 et 3 ont la ville Séoul, les clients 2 et 4 Busan et Daejeon. Gardez les littéraux UTF-8 et suivez les identifiants.
Les commandes payées donnent 11500 pour 101, 8000 pour 102 et 19500 pour 103, soit 39000 au total. Le montant de 1500 de la commande annulée 104 est exclu. Calculez ces valeurs avant les rapports pour disposer d’un contrôle indépendant. Les noms distincts des tables évitent qu’un essai de transaction d’un autre article change ce point de départ.
Regrouper par année-mois et compter les commandes
Le rapport suivant conserve les détails payés de 2026 et les regroupe par année et mois :
SELECT DATE_FORMAT(o.ordered_at,'%Y-%m') AS month_label,
COUNT(DISTINCT o.order_id) AS paid_orders,
SUM(i.quantity*i.unit_price) AS goods_sales
FROM c42_orders o JOIN c42_items i ON i.order_id=o.order_id
WHERE o.status='paid' AND o.ordered_at>='2026-01-01' AND o.ordered_at<'2027-01-01'
GROUP BY DATE_FORMAT(o.ordered_at,'%Y-%m') ORDER BY month_label;
| Mois | Commandes payées | Montant des produits |
|---|---|---|
| 2026-01 | 2 | 19500.00 |
| 2026-02 | 1 | 19500.00 |
La jointure contient cinq lignes de détail payées, mais seulement trois commandes. COUNT(DISTINCT o.order_id) compte les commandes distinctes plutôt que les détails. La référence COUNT DISTINCT explique le dénombrement des valeurs distinctes non NULL.
Utiliser seulement le numéro du mois pourrait mélanger janvier de plusieurs années. DATE_FORMAT avec %Y-%m garde cette distinction et l’intervalle va du début de 2026 inclus au début de 2027 exclu. GROUP BY ne crée pas automatiquement les mois absents avec un montant zéro ; une population de calendrier et une jointure seraient nécessaires si le rapport doit montrer les douze mois.
Conserver les clients sans achat payé
Cette requête part de tous les clients et additionne seulement les détails payés :
SELECT c.customer_id,c.customer_name,
COALESCE(SUM(CASE WHEN o.status='paid' THEN i.quantity*i.unit_price ELSE 0 END),0) AS paid_goods
FROM c42_customers c LEFT JOIN c42_orders o ON o.customer_id=c.customer_id
LEFT JOIN c42_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name ORDER BY c.customer_id;
| customer_id | paid_goods |
|---|---|
| 1 | 19500.00 |
| 2 | 19500.00 |
| 3 | 0.00 |
| 4 | 0.00 |
LEFT JOIN maintient la population des clients. CASE choisit les montants des commandes paid et COALESCE fournit zéro quand la somme est NULL. Le client 3 a seulement une commande annulée ; le client 4 n’a aucune commande. Leur présence avec zéro répond à la définition du rapport.
Déplacer le statut vers un WHERE o.status='paid' peut éliminer ces clients au lieu de leur attribuer zéro. Si vous ajoutez une période, placez-la de façon à préserver la population voulue, par exemple dans une étape d’agrégation des montants payés ou dans la condition qui choisit les montants ; ne copiez pas un filtre extérieur sans vérifier ses effets sur les LEFT JOIN.
Ne faites pas non plus SUM des frais de livraison d’une commande directement sur les détails : ils se répètent pour chaque détail associé. Le rapport additionne uniquement les produits. Pour inclure un coût au niveau commande, calculez-le à ce niveau avant de le rapprocher des montants clients. La somme de valeurs DISTINCT n’est pas une correction générale : des commandes différentes peuvent avoir les mêmes frais.
Trouver les produits sans vente payée
Pour chaque produit enregistré, cherchez l’absence d’un détail lié à une commande payée :
SELECT p.product_id,p.product_name FROM c42_products p
WHERE NOT EXISTS(SELECT 1 FROM c42_items i JOIN c42_orders o ON o.order_id=i.order_id
WHERE i.product_id=p.product_id AND o.status='paid') ORDER BY p.product_id;
Seul le produit 4, la lampe, est retourné. NOT EXISTS vérifie qu’aucune ligne correspondant au produit et au statut paid n’existe dans la sous-requête. Le nombre de ventes n’a pas besoin d’être calculé pour exprimer cette absence.
Un produit présent uniquement dans des commandes annulées serait également invendu selon cette définition. À l’inverse, apparaître dans une commande paid suffit ici à constituer un historique de vente, même si le montant était nul. Si l’exigence porte sur un montant positif, une période ou tous les statuts, la condition doit exprimer cette autre règle. La documentation des sous-requêtes EXISTS et NOT EXISTS décrit cette logique.
Réconcilier les totaux et vérifier les absences
Dans les données initiales, les deux montants mensuels donnent 39000, tout comme les quatre montants clients. Vérifiez aussi les trois commandes payées, les deux clients à zéro et le seul produit invendu. Un total global plausible peut masquer des erreurs de répartition ou de population.
Si les rapports ne se réconcilient pas, comparez les clés, les périodes, les statuts et la source du prix unitaire. Examinez ensuite la multiplication des jointures et les filtres appliqués après LEFT JOIN. La référence GROUP BY précise le regroupement et le rôle des filtres.
Pour des rapports réels, définissez un même instant d’observation lorsque c’est nécessaire, la politique de fuseau horaire et le traitement des annulations et remboursements. Cet exercice utilise des DATETIME fixes et ne fait pas varier les bornes avec la date d’exécution. Paramétrez les périodes demandées sans les concaténer à une chaîne SQL.
Avant de recommencer, contrôlez la base, les définitions et les données réelles, et sauvegardez ce qui doit être conservé. Supprimez seulement les objets jetables ci-dessous, les enfants avant les tables référencées. DROP TABLE enlève structure et données et n’est pas annulé par un ROLLBACK ordinaire.
DROP TABLE shopcourse.c42_items;
DROP TABLE shopcourse.c42_orders;
DROP TABLE shopcourse.c42_products;
DROP TABLE shopcourse.c42_customers;




