%% nav-debut : généré par gen_navigation.py, ne pas modifier à la main %% > [!nav] > ↩ [[Code et Développement/Développement d'Applications/SQL IV/SQL IV|Accueil · SQL IV]] > > | [[Code et Développement/Développement d'Applications/SQL IV/12_Cumuls et ligne précédente - LAG\|← Cumuls et ligne précédente : LAG]] | | > |:---|---:| %% nav-fin %% [[Code et Développement/Développement d'Applications/SQL IV/12_Cumuls et ligne précédente - LAG|Cumuls et ligne précédente - LAG]] # Répondre à une demande « projet » ## Introduction Dans un vrai projet, personne ne vous dira « utilisez une sous-requête dans le `HAVING` ». On vous posera une question en français, et c'est à vous de choisir les bons outils. Ce chapitre reprend la méthode de SQL I et l'enrichit avec tout ce que nous avons vu dans SQL IV. ### Objectifs du chapitre À la fin de ce chapitre, vous serez capable de : - Repérer, dans une demande, les mots qui orientent vers un outil SQL. - Construire une requête complète en suivant une méthode. - Vérifier votre résultat avant de le rendre. Nous utilisons les tables *Clients* et *Commandes* présentées au chapitre [[Code et Développement/Développement d'Applications/SQL IV/01_Supprimer les doublons - DISTINCT|Supprimer les doublons : DISTINCT]]. --- ## 1. Rappel de la méthode La méthode du chapitre [[Code et Développement/Développement d'Applications/SQL I/05_Comment répondre à une demande|Comment répondre à une demande]] reste la base : 1. **Comprendre la demande** : ouvrir le schéma, et se demander **à quoi correspond un enregistrement du résultat** (un client ? une commande ? une ville ?). 2. **Les données existent-elles ?** Sinon, peut-on les calculer ? 3. **Combien de requêtes ?** Faire le diagramme : chaque calcul intermédiaire est une requête, donc une sous-requête. 4. **Préparer chaque requête** avec la fiche, en commençant par les plus profondes, puis les tester une par une. --- ## 2. Les mots de la demande qui doivent vous alerter | Dans la demande, je lis… | Je pense à… | Chapitre | | --- | --- | --- | | « la liste des … différents », « quels … » (sans calcul), « combien de … différents » | `GROUP BY` (SQL I), ou `DISTINCT`, `COUNT(DISTINCT …)` | [[Code et Développement/Développement d'Applications/SQL IV/01_Supprimer les doublons - DISTINCT\|01]] | | « les 10 premiers », « les 5 dernières » (un nombre de lignes) | `ORDER BY` + `LIMIT` | [[Code et Développement/Développement d'Applications/SQL IV/02_Garder les premières lignes - LIMIT\|02]] | | « le plus… », « le moins… », « le meilleur » (avec ex æquo possibles) | `MAX` / `MIN` dans une sous-requête (dans le `FROM` comme en SQL III, ou avec `=` dans le `WHERE` / `HAVING`) | [[Code et Développement/Développement d'Applications/SQL IV/06_Sous-requête renvoyant une valeur\|06]] | | « … et … » pour réunir deux listes semblables (« clients et prospects ») | `UNION` / `UNION ALL` | [[Code et Développement/Développement d'Applications/SQL IV/03_Combiner deux résultats - UNION\|03]] | | « tous les … et tous les …, même sans correspondance » | jointure complète | [[Code et Développement/Développement d'Applications/SQL IV/04_La jointure complète - FULL JOIN\|04]] | | « supérieur à la moyenne », « plus que le total de… » | sous-requête renvoyant une valeur (dans le `FROM` comme en SQL I, ou dans le `WHERE` / `HAVING`) | [[Code et Développement/Développement d'Applications/SQL IV/06_Sous-requête renvoyant une valeur\|06]] | | « part », « pourcentage du total » | sous-requête dans le `FROM` (SQL III) ou dans le `SELECT` | [[Code et Développement/Développement d'Applications/SQL IV/06_Sous-requête renvoyant une valeur\|06]] | | « ceux qui ont … » (pour filtrer seulement) | jointure + `GROUP BY` (SQL I), ou `IN` / `EXISTS` | [[Code et Développement/Développement d'Applications/SQL IV/07_Sous-requête renvoyant une liste - IN\|07]], [[Code et Développement/Développement d'Applications/SQL IV/08_Tester l'existence - EXISTS\|08]] | | « jamais », « aucun », « ceux qui n'ont pas … » | `LEFT JOIN … IS NULL` (SQL I), ou `NOT EXISTS` / `NOT IN` avec `IS NOT NULL` | [[Code et Développement/Développement d'Applications/SQL IV/08_Tester l'existence - EXISTS\|08]] | | « pour chaque …, le plus … » | jointure avec une sous-requête `GROUP BY` + `MAX` dans le `FROM` (SQL III), sous-requête corrélée, ou `RANK()` avec `PARTITION BY` | [[Code et Développement/Développement d'Applications/SQL IV/08_Tester l'existence - EXISTS\|08]] | | « y compris ceux qui n'ont … » | `LEFT JOIN` (SQL I) et `IFNULL(…, 0)` | SQL I | | « … avec, à côté, le total (la moyenne…) de son groupe », « part dans son groupe » | sous-requête `GROUP BY` jointe (SQL III), ou `SUM(…) OVER (PARTITION BY …)` | [[Code et Développement/Développement d'Applications/SQL IV/10_Calculer sur un groupe - PARTITION BY\|10]] | | « classement », « rang », « les 3 meilleurs » (ex æquo compris) | `RANK()` dans une sous-requête du `FROM`, puis `WHERE rang <= 3` | [[Code et Développement/Développement d'Applications/SQL IV/11_Classer - RANK\|11]] | | « les n plus … de chaque … » | `RANK() OVER (PARTITION BY … ORDER BY …)` | [[Code et Développement/Développement d'Applications/SQL IV/11_Classer - RANK\|11]] | | « cumul », « depuis le début », « au fil des … » | auto-jointure (SQL III), ou `SUM(…) OVER (ORDER BY …)` | [[Code et Développement/Développement d'Applications/SQL IV/12_Cumuls et ligne précédente - LAG\|12]] | | « par rapport à la précédente », « évolution », « la suivante » | sous-requête corrélée, ou `LAG` / `LEAD` | [[Code et Développement/Développement d'Applications/SQL IV/12_Cumuls et ligne précédente - LAG\|12]] | > [!warning] Ce tableau donne des pistes, pas des règles > Une même demande peut souvent se résoudre de plusieurs façons. Le tableau vous aide à **démarrer** ; c'est le diagramme des requêtes qui vous dit si la piste tient. --- ## 3. Exemple 1 : une campagne de relance > [!example] La demande > *« Le service commercial veut relancer les clients inactifs : la liste des clients qui n'ont rien commandé depuis le 1er juillet 2023, y compris ceux qui n'ont jamais commandé, avec la date de leur dernière commande. Les clients jamais venus en premier, puis du plus ancien au plus récent. »* ### Étape 1 : comprendre - Un enregistrement du résultat = **un client**. - Champs affichés : le nom du client et la date de sa dernière commande. - « n'ont rien commandé depuis le 1er juillet » = leur **dernière** commande est antérieure au 2023-07-01, ou bien **il n'existe aucune commande** de ce client à partir de cette date. - « y compris ceux qui n'ont jamais commandé » : Robert doit apparaître. ### Étape 2 : les données - La date de la dernière commande n'existe pas : c'est `MAX(date_commande)` **pour chaque client**. ### Étape 3 et 4 : ce que vous savez déjà faire (SQL I) - On garde tous les clients (`LEFT JOIN`), on regroupe par client et on calcule `MAX(date_commande)`. - On filtre les groupes avec `HAVING` : dernière commande avant le 1er juillet, **ou** aucune commande. ```sql SELECT C.nom_client, MAX(Co.date_commande) AS derniere_commande FROM Clients C LEFT JOIN Commandes Co ON C.id_client = Co.id_client GROUP BY C.id_client, C.nom_client HAVING MAX(Co.date_commande) < '2023-07-01' OR MAX(Co.date_commande) IS NULL ORDER BY derniere_commande; ``` | nom_client | derniere_commande | | ---------- | ----------------- | | Robert | NULL | | Martin | 2023-02-18 | | Dupont | 2023-06-01 | - Bernard et Petit ont commandé après le 1er juillet : ils sont écartés. - Robert n'a jamais commandé : sa date est `NULL`. Le `OR … IS NULL` est indispensable pour le garder (une comparaison avec `NULL` n'est jamais vraie). - Avec un tri croissant, les `NULL` sont placés en premier : Robert est en tête, comme demandé. ### Une autre solution : avec des sous-requêtes corrélées On traduit la demande presque mot pour mot : - « il n'existe aucune commande de ce client depuis le 1er juillet » → `NOT EXISTS` dans le `WHERE` ; - « la date de sa dernière commande » → sous-requête corrélée dans le `SELECT`. ```sql SELECT C.nom_client, (SELECT MAX(Co.date_commande) FROM Commandes Co WHERE Co.id_client = C.id_client) AS derniere_commande FROM Clients C WHERE NOT EXISTS ( -- commandes de ce client depuis le 1er juillet 2023 SELECT * FROM Commandes Co WHERE Co.id_client = C.id_client AND Co.date_commande >= '2023-07-01' ) ORDER BY derniere_commande; ``` Elle donne le même résultat, sans jointure ni `GROUP BY`, et sans le piège du `OR … IS NULL`. > [!tip] Laquelle choisir ? > **Une sous-requête n'est pas toujours nécessaire.** Les deux versions sont justes : choisissez celle que vous savez justifier. --- ## 4. Exemple 2 : le bilan par ville > [!example] La demande > *« Pour le bilan annuel, donnez les 3 villes qui rapportent le plus gros chiffre d'affaires, avec leur part du chiffre d'affaires total en %. »* ### Étape 1 : comprendre - Un enregistrement du résultat = **une ville**. - « les 3 villes » : un nombre de lignes → `ORDER BY … LIMIT 3`… mais attention aux ex æquo pour la 3ᵉ place. ### Étape 2 : les données - Le chiffre d'affaires d'une ville : `SUM(montant_total)` en regroupant par ville (jointure *Clients* / *Commandes*). - Le chiffre d'affaires total : une valeur → une sous-requête. - La part : `100.0 × CA de la ville / CA total`. ### Étape 3 et 4 : ce que vous savez déjà faire (SQL I et SQL III) Le total est ajouté à chaque enregistrement par une sous-requête dans le `FROM`, après une virgule. Comme `t.total` est utilisé avec les groupes, il figure aussi dans le `GROUP BY` : ```sql SELECT C.ville_client, SUM(Co.montant_total) AS ca_ville, ROUND(100.0 * SUM(Co.montant_total) / t.total, 1) AS part_pct FROM Clients C INNER JOIN Commandes Co ON C.id_client = Co.id_client, (SELECT SUM(montant_total) AS total FROM Commandes) AS t GROUP BY C.ville_client, t.total ORDER BY ca_ville DESC LIMIT 3; ``` | ville_client | ca_ville | part_pct | | ------------ | -------- | -------- | | Lyon | 950.00 | 50.8 | | Lille | 400.00 | 21.4 | | Paris | 370.00 | 19.8 | ### Une autre solution : la sous-requête dans le `SELECT` On place le calcul du total directement dans le calcul de la part. Le `FROM` ne contient plus que la jointure, et `t.total` disparaît du `GROUP BY` : ```sql SELECT C.ville_client, SUM(Co.montant_total) AS ca_ville, ROUND(100.0 * SUM(Co.montant_total) / (SELECT SUM(montant_total) FROM Commandes), 1) AS part_pct FROM Clients C INNER JOIN Commandes Co ON C.id_client = Co.id_client GROUP BY C.ville_client ORDER BY ca_ville DESC LIMIT 3; ``` Le résultat est le même. > [!tip] Vérifier les ex æquo > Exécutez la requête **sans** le `LIMIT` : la 4ᵉ ville (Marseille, 150 €) est loin derrière Paris, il n'y a donc pas d'ex æquo pour la 3ᵉ place et `LIMIT 3` convient. Si Marseille avait aussi 370 €, il aurait fallu le signaler, ou utiliser `RANK` (voir le chapitre [[Code et Développement/Développement d'Applications/SQL IV/11_Classer - RANK|Classer : RANK, DENSE_RANK, ROW_NUMBER]]) pour garder toutes les villes ex æquo. --- ## 5. Exemple 3 : l'évolution des clients fidèles > [!example] La demande > *« Pour suivre les clients fidèles : pour chaque client ayant passé au moins deux commandes, le montant de sa dernière commande, celui de la commande précédente, et l'évolution entre les deux. »* ### Étape 1 : comprendre - Un enregistrement du résultat = **un client**, représenté par **sa dernière commande**. - « au moins deux commandes » : un client avec une seule commande n'a pas de commande précédente, il ne doit pas apparaître. ### Étape 2 : les données - « la commande précédente » : la valeur de la ligne précédente, pour le même client, dans l'ordre des dates → `LAG` avec `PARTITION BY id_client` (chapitre [[Code et Développement/Développement d'Applications/SQL IV/12_Cumuls et ligne précédente - LAG\|12]]). - « sa dernière commande » : la commande de rang 1 quand on trie les commandes du client de la plus récente à la plus ancienne → `ROW_NUMBER` avec `PARTITION BY id_client` (chapitre [[Code et Développement/Développement d'Applications/SQL IV/11_Classer - RANK\|11]]). On veut **une seule** commande par client, d'où `ROW_NUMBER` plutôt que `RANK`. ### Étape 3 : les requêtes - Une sous-requête dans le `FROM` calcule, pour **chaque commande**, le montant précédent (`LAG`) et le rang de la commande en partant de la plus récente (`ROW_NUMBER`). - La requête principale garde la dernière commande de chaque client (`rang_recent = 1`) qui a une commande précédente (`montant_precedent IS NOT NULL`). Ce filtre ne peut pas être fait dans la sous-requête : les fonctions de fenêtre sont calculées après le `WHERE` (chapitre [[Code et Développement/Développement d'Applications/SQL IV/10_Calculer sur un groupe - PARTITION BY\|10]]). ### Étape 4 : la requête ```sql SELECT nom_client, date_commande, montant_total, montant_precedent, montant_total - montant_precedent AS evolution FROM ( -- pour chaque commande : montant précédent et rang en partant de la plus récente SELECT C.nom_client, Co.date_commande, Co.montant_total, LAG(Co.montant_total) OVER (PARTITION BY Co.id_client ORDER BY Co.date_commande) AS montant_precedent, ROW_NUMBER() OVER (PARTITION BY Co.id_client ORDER BY Co.date_commande DESC) AS rang_recent FROM Clients C INNER JOIN Commandes Co ON C.id_client = Co.id_client ) AS t WHERE rang_recent = 1 AND montant_precedent IS NOT NULL ORDER BY nom_client; ``` | nom_client | date_commande | montant_total | montant_precedent | evolution | | ---------- | ------------- | ------------- | ----------------- | --------- | | Dupont | 2023-06-01 | 310.00 | 80.00 | 230.00 | | Martin | 2023-02-18 | 120.00 | 250.00 | -130.00 | - Bernard et Petit n'ont qu'une commande : leur `montant_precedent` vaut `NULL`, ils sont écartés. - Robert n'a aucune commande : l'`INNER JOIN` l'écarte dès le `FROM`. > [!note] Et sans les partitions ? > C'est possible avec des sous-requêtes corrélées : la date de la dernière commande du client, puis le montant à cette date, puis la date de la commande d'avant, puis son montant… La requête devient longue et difficile à vérifier. C'est typiquement le genre de demande où les partitions simplifient le travail. --- ## 6. Avant de rendre votre requête : la liste de vérification > [!important] 7 questions à vous poser > 1. **À quoi correspond un enregistrement** de mon résultat ? Est-ce bien ce qui est demandé ? > 2. **Le nombre de lignes** est-il plausible ? (Exécutez sans `LIMIT`, comptez les clients, les commandes…) > 3. **Des doublons** ? D'où viennent-ils ? Est-ce que je les cache avec un `DISTINCT` au lieu de comprendre ? > 4. **Des `NULL`** ? Ai-je oublié des enregistrements sans correspondance (`LEFT JOIN`, `IFNULL`) ? Ai-je un `NOT IN` sans `IS NOT NULL` ? > 5. **Des ex æquo** ? Mon `LIMIT` en élimine-t-il ? > 6. **Chaque sous-requête** renvoie-t-elle une table de la bonne forme (table classique, une seule valeur, un seul champ) ? L'ai-je exécutée seule ? > 7. **Une fonction de fenêtre** (`OVER`) ? Ai-je mis la bonne partition (`PARTITION BY`) et le bon ordre (`ORDER BY` dans le `OVER`) ? Si je filtre sur son résultat, est-elle bien dans une sous-requête du `FROM` ? --- ## 7. À vous de jouer Vous avez maintenant tous les outils pour répondre aux questions du niveau IV sur le Codex. Les bases **musée**, **nitrates** et **toxi-infection alimentaire** sont de vraies enquêtes : traitez leurs étapes dans l'ordre, et appliquez la méthode à chacune. [🎯 Accéder au niveau IV sur le Codex](https://codex-skillquest.netlify.app/sql.html?niveau=IV) --- > [!important] Ce qu'il faut retenir > - La méthode de SQL I reste valable : enregistrement, données, diagramme, préparation. > - Les mots de la demande orientent vers les bons outils, mais le diagramme décide. > - Il existe souvent plusieurs requêtes justes, dont une avec ce que vous saviez déjà faire en SQL I et SQL III : choisissez celle que vous savez expliquer. > - Vérifiez toujours : enregistrement, nombre de lignes, doublons, `NULL`, ex æquo, sous-requêtes, fonctions de fenêtre. %% nav-debut : généré par gen_navigation.py, ne pas modifier à la main %% > [!nav] > ↩ [[Code et Développement/Développement d'Applications/SQL IV/SQL IV|Accueil · SQL IV]] > > | [[Code et Développement/Développement d'Applications/SQL IV/12_Cumuls et ligne précédente - LAG\|← Cumuls et ligne précédente : LAG]] | | > |:---|---:| %% nav-fin %% --- 🗓️ Historique Dernière mise à jour : `30 octobre 2026` Rédigé par : [[Vincent TELLIER]]