15th Mar 2023 25 minutes de lecture 25 exemples de requêtes SQL avancées Ignacio L. Bisso sql apprendre sql pratique en ligne advanced sql L'une des meilleures façons d'apprendre le langage SQL avancé est d'étudier des exemples de requêtes. Dans cet article, nous présentons 25 exemples de requêtes SQL avancées, de complexité moyenne à élevée. Vous pouvez les utiliser pour rafraîchir vos connaissances en SQL avancé ou pour les réviser avant un entretien SQL. La plupart des exemples de cet article sont basés sur le tableau suivant employee suivante. Seuls quelques exemples seront basés sur d'autres tables ; dans ces cas, les tables seront expliquées en même temps que l'exemple. employee_idfirst_namelast_namedept_idmanager_idsalaryexpertise 100JohnWhiteIT103120000Senior 101MaryDannerAccount10980000junior 102AnnLynnSales107140000Semisenior 103PeterO'connorIT110130000Senior 106SueSanchezSales107110000Junior 107MartaDoeSales110180000Senior 109AnnDannerAccount11090000Senior 110SimonYangCEOnull250000Senior 111JuanGraueSales10237000Junior Même pour les personnes ayant des connaissances en SQL, un bon cours interactif en ligne sur le langage SQL peut s'avérer très utile. Vous trouverez l'ensemble le plus complet de cours interactifs sur le langage SQL dans notre piste Le SQL de A à Z . Il contient 7 cours interactifs sur le langage SQL avec plus de 850( !) exercices organisés de manière logique pour vous permettre de passer du statut de débutant à celui d'utilisateur avancé du langage SQL. Les cours pour débutants couvrent les fondements de SQL et sont un moyen parfait pour réviser et rafraîchir vos connaissances de base en SQL. Les cours SQL avancés vous apprendront des concepts tels que les fonctions de fenêtre, les requêtes récursives et les rapports SQL complexes. Créez un compte gratuit sur LearnSQL.fr et essayez nos cours interactifs sans avoir à dépenser d'argent. Ensuite, si vous aimez ce que vous apprenez, vous pouvez acheter un accès complet. Ok, plongeons dans nos requêtes SQL avancées ! 25 SQL avancé Exemples de requêtes avec explications Exemple #1 - Classer les lignes en fonction d'un critère d'ordre spécifique Parfois, nous avons besoin de créer une requête SQL pour afficher un classement des lignes en fonction d'un critère d'ordre spécifique. Dans cet exemple de requête, nous afficherons une liste de tous les employés classés par salaire (le salaire le plus élevé en premier). Le rapport comprendra la position de chaque employé dans le classement. Voici le code : SELECT employee_id, last_name, first_name, salary, RANK() OVER (ORDER BY salary DESC) as ranking FROM employee ORDER BY ranking Dans la requête ci-dessus, nous utilisons la fonction RANK(). Il s'agit d'une fonction fenêtre qui renvoie la position de chaque ligne dans l'ensemble de résultats, en fonction de l'ordre défini dans la clause OVER (1 pour le salaire le plus élevé, 2 pour le deuxième plus élevé, et ainsi de suite). Nous devons utiliser une clause de classement ORDER BY à la fin de la requête pour indiquer l'ordre dans lequel l'ensemble des résultats sera affiché. Si vous souhaitez en savoir plus sur les fonctions de classement en SQL, je vous recommande notre article Qu'est-ce que la fonction RANK() en SQL et comment l'utiliser ? Exemple #2 - Lister les 5 premières lignes d'un ensemble de résultats La requête SQL suivante crée un rapport contenant les données des employés pour les 5 premiers salaires de la société. Ce type de rapport doit être ordonné sur la base d'un critère donné ; dans notre exemple, le critère d'ordre sera à nouveau salary DESC: WITH employee_ranking AS ( SELECT employee_id, last_name, first_name, salary, RANK() OVER (ORDER BY salary DESC) as ranking FROM employee ) SELECT employee_id, last_name, first_name, salary FROM employee_ranking WHERE ranking <= 5 ORDER BY ranking La clause WITH de la requête précédente crée un CTE appelé employee_ranking, qui est une sorte de table virtuelle consommée dans la requête principale. La sous-requête de l'ETC utilise la fonction RANK() pour obtenir la position de chaque ligne dans le classement. La clause OVER (ORDER BY salary DESC) indique comment la valeur RANK() doit être calculée. La fonction RANK() pour la ligne avec le salaire le plus élevé renvoie 1, et ainsi de suite. Enfin, dans la clause WHERE de la requête principale, nous demandons les lignes dont la valeur de classement est inférieure ou égale à 5. Cela nous permet d'obtenir uniquement les 5 premières lignes par valeur de classement. Une fois de plus, nous utilisons une clause ORDER BY pour afficher l'ensemble des résultats, qui sont classés par ordre croissant. Exemple 3 - Afficher les 5 dernières lignes d'un ensemble de résultats Cette requête est similaire à la requête top 5, mais nous voulons les 5 dernières lignes. Il suffit de modifier le type d'ordre, c'est-à-dire d'utiliser ASC au lieu de DESC. Dans le CTE, nous créerons une colonne de classement basée sur l'ordre croissant des salaires (le salaire le plus bas en premier) : WITH employee_ranking AS ( SELECT employee_id, last_name, first_name, salary, RANK() OVER (ORDER BY salary ASC) as ranking FROM employee ) SELECT employee_id, last_name, first_name, salary FROM employee_ranking WHERE ranking <= 5 ORDER BY ranking Dans la requête principale, nous utilisons WHERE ranking <= 5 pour filtrer les lignes contenant les 5 salaires les plus bas. Ensuite, nous utilisons ORDER BY ranking pour ordonner les lignes du rapport par valeur de classement. Exemple n° 4 - Répertorier la deuxième ligne la plus élevée d'un ensemble de résultats Supposons que nous souhaitions obtenir les données de l'employé dont le salaire est le deuxième plus élevé de l'entreprise. Nous pouvons appliquer une approche similaire à notre requête précédente : WITH employee_ranking AS ( SELECT employee_id, last_name, first_name, salary, RANK() OVER (ORDER BY salary DESC) as ranking FROM employee ) SELECT employee_id, last_name, first_name, salary FROM employee_ranking WHERE ranking = 2 La condition WHERE ranking = 2 est utilisée pour filtrer les lignes dont le salaire se trouve en position 2. Notez qu'il peut y avoir plus d'un employé en position 2 s'ils ont le même salaire. À ce stade, il est important de comprendre le comportement de la fonction RANK() ainsi que d'autres fonctions disponibles telles que ROW_NUMBER() et DENSE_RANK(). Ce sujet est traité en détail dans notre article intitulé Vue d'ensemble des fonctions de classement en SQL. Je vous recommande vivement de lire cet article si vous devez travailler avec différents types de classements. Exemple #5 - Lister le deuxième salaire le plus élevé par département Ajoutons une variation à la requête SQL précédente. Comme chacun de nos employés appartient à un département, nous voulons maintenant un rapport montrant l'ID du département et le nom de l'employé ayant le deuxième salaire le plus élevé dans ce département. Nous voulons un enregistrement pour chaque département de la société. Voici la requête : WITH employee_ranking AS ( SELECT employee_id, last_name, first_name, salary, dept_id RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as ranking FROM employee ) SELECT dept_id, employee_id, last_name, first_name, salary FROM employee_ranking WHERE ranking = 2 ORDER BY dept_id, last_name Le principal changement introduit dans cette requête est la clause PARTITION BY dept_id dans OVER. Cette clause regroupe les lignes ayant le même dept_id, en classant les lignes de chaque groupe par salaire DESC. La fonction RANK() est ensuite calculée pour chaque département. Dans la requête principale, nous renvoyons les données dept_id et les données des employés qui se trouvent à la position 2 du classement de leur département. Pour les lecteurs qui souhaitent en savoir plus sur la recherche de la Nièmeligne la plus élevée dans un groupe, je recommande l'article Comment trouver le Nième salaire le plus élevé par département avec SQL. Exemple #6 - Lister les 50% premières lignes d'un ensemble de résultats Dans certains cas, il peut être intéressant d'obtenir les premiers 50% de l'ensemble de résultats (ou tout autre pourcentage). Pour ce type de rapport, il existe une fonction SQL appelée NTILE() qui reçoit un paramètre entier indiquant le nombre de sous-ensembles dans lesquels nous voulons diviser l'ensemble des résultats. Par exemple NTILE(2) divise l'ensemble des résultats en 2 sous-ensembles ayant la même quantité d'éléments ; pour chaque ligne, il renvoie un 1 ou un 2 selon le sous-ensemble où se trouve la ligne. Voici la requête : WITH employee_ranking AS ( SELECT employee_id, last_name, first_name, salary, NTILE(2) OVER (ORDER BY salary ) as ntile FROM employee ) SELECT employee_id, last_name, first_name, salary FROM employee_ranking WHERE ntile = 1 ORDER BY salary La requête ci-dessus renvoie uniquement les lignes de la première moitié d'un rapport sur les employés classés par salaire dans l'ordre croissant. Nous utilisons la condition ntile = 1 pour filtrer uniquement les lignes de la première moitié du rapport. Si vous êtes intéressé par la fonction de fenêtre NTILE(), consultez l'article Common SQL Fonctions de fenêtrage: Using Partitions With Ranking Functions. Exemple n° 7 - Lister les 25 derniers rangs d'un ensemble de résultats Comme pour la requête précédente, dans cet exemple, nous utiliserons NTILE(4) pour diviser l'ensemble de résultats en 4 sous-ensembles ; chaque sous-ensemble contiendra 25 % de l'ensemble de résultats total. En utilisant la fonction NTILE(), nous allons générer une colonne appelée ntile avec les valeurs 1, 2, 3 et 4 : WITH employee_ranking AS ( SELECT employee_id, last_name, first_name, salary, NTILE(4) OVER (ORDER BY salary) as ntile FROM employee ) SELECT employee_id, last_name, first_name, salary FROM employee_ranking WHERE ntile = 4 ORDER BY salary La condition WHERE ntile = 4 filtre uniquement les lignes du dernier trimestre du rapport. La dernière clause ORDER BY salary ordonne l'ensemble de résultats à renvoyer par la requête, tandis que OVER (ORDER BY salary) ordonne les lignes avant de les diviser en 4 sous-ensembles à l'aide de NTILE(4). Exemple n° 8 - Numéroter les lignes d'un ensemble de résultats Parfois, nous voulons créer un classement qui attribue à chaque ligne un numéro indiquant la position de cette ligne dans le classement : 1 pour la première ligne, 2 pour la deuxième, et ainsi de suite. SQL propose plusieurs façons de procéder. Si nous voulons une simple séquence de nombres de 1 à N, nous pouvons utiliser la fonction ROW_NUMBER(). Toutefois, si nous voulons un classement qui permette à deux lignes d'occuper la même position (c'est-à-dire parce qu'elles partagent la même valeur), nous pouvons utiliser la fonction RANK() ou DENSE_RANK(). La requête suivante crée un rapport dans lequel chaque ligne a une valeur de position : SELECT employee_id, last_name, first_name, salary, ROW_NUMBER() OVER (ORDER BY employee_id) as ranking_position FROM employee Si vous souhaitez en savoir plus sur les différentes fonctions de classement avancées, je vous recommande l'article Vue d'ensemble des fonctions de classement en SQL. Exemple #9 - Lister toutes les combinaisons de lignes de deux tables Dans certains cas, nous pouvons avoir besoin d'une jointure qui inclut toutes les combinaisons possibles de lignes de deux tables. Supposons que nous ayons une entreprise alimentaire qui vend 3 sortes de céréales : les corn flakes, les corn flakes sucrés et les rice flakes. Toutes ces céréales sont vendues dans trois conditionnements différents : 1 livre, 3 livres et 5 livres. Comme nous proposons 3 produits dans 3 formats d'emballage différents, nous disposons de 9 combinaisons différentes. Nous disposons d'un product table avec 3 enregistrements (corn flakes, corn flakes sucrés et rice flakes) et une autre table appelée box_size avec 3 enregistrements, un pour 1 livre et deux pour 3 et 5 livres, respectivement. Si nous voulons créer un rapport avec la liste des prix pour nos neuf combinaisons, nous pouvons utiliser la requête suivante : SELECT grain.product_name, box_size.description, grain.price_per_pound * box_size.box_weight FROM product CROSS JOIN box_sizes Le résultat de la requête sera : productpackage_sizeprice Corn flake1 pound box2.43 Corn flake3 pound box7.29 Corn flake5 pound box12.15 Sugared corn flake1 pound box2.85 Sugared corn flake3 pound box8.55 Sugared corn flake5 pound box14.25 Rice flake1 pound box1.98 Rice flake3 pound box5.94 Rice flake5 pound box9.90 La clause CROSS JOIN sans aucune condition produit un tableau avec toutes les combinaisons de lignes des deux tableaux. Notez que nous calculons le prix sur la base du prix à la livre stocké dans la table product et le poids de box_sizes avec l'expression : grain.price_per_pound * box_size.box_weight Une étude approfondie de CROSS JOIN est disponible dans Guide illustré du CROSS JOIN SQL. Exemple #10 - Joindre une table à elle-même Dans certains cas, il est nécessaire de joindre une table à elle-même. Pensez à la employee table. Chaque ligne comporte une colonne appelée manager_id qui contient l'ID du manager qui supervise cet employé. En utilisant une jointure à soi-même, nous pouvons obtenir un rapport avec les colonnes employee_name et manager_name; cela nous montrera qui gère chaque employé. Voici la requête : SELECT e1.first_name ||’ ‘|| e1.last_name AS manager_name, e2.first_name ||’ ‘|| e2.last_name AS employee_name FROM employee e1 JOIN employee e2 ON e1.employee_id = e2.manager_id Dans la requête ci-dessus, nous pouvons voir que la table employee est référencée deux fois comme e1 et e2, et la condition de jointure est e1.employee_id = e2.manager_id. Cette condition lie chaque ligne d'employé à la ligne du manager. L'article Qu'est-ce qu'une jointure automatique en SQL ? Une explication avec sept exemples vous donnera plus d'idées sur les cas où vous pouvez appliquer des jointures automatiques dans vos requêtes SQL. Exemple n° 11 - Afficher toutes les lignes dont la valeur est supérieure à la moyenne Nous avons besoin d'un rapport montrant tous les employés dont le salaire est supérieur à la moyenne de l'entreprise. Nous pouvons d'abord créer une sous-requête pour obtenir le salaire moyen de l'entreprise, puis comparer le salaire de chaque employé avec le résultat de la sous-requête. C'est ce que montre l'exemple suivant : SELECT first_name, last_name, salary FROM employee WHERE salary > ( SELECT AVG(salary) FROM employee ) Vous pouvez voir la sous-requête qui obtient le salaire moyen dans la clause WHERE. Dans la requête principale, nous sélectionnons le nom et le salaire de l'employé. Pour en savoir plus sur les sous-requêtes, consultez l'article Comment pratiquer les sous-requêtes SQL. Exemple n° 12 - Employés dont le salaire est supérieur à la moyenne de leur département Supposons que nous souhaitions obtenir les enregistrements des employés dont le salaire est supérieur au salaire moyen de leur département. Cette requête est différente de la précédente car nous avons besoin d'une sous-requête pour obtenir le salaire moyen du département de l'employé actuel plutôt que celui de l'ensemble de la société. On parle de sous-requête corrélée car la sous-requête fait référence à une colonne de la ligne actuelle de la table principale de la requête. Voici le code : SELECT first_name, last_name, salary FROM employee e1 WHERE salary > (SELECT AVG(salary) FROM employee e2 WHERE e1.departmet_id = e2.department_id) Dans la sous-requête, nous pouvons voir une référence à la colonne e1.department_id, qui est une colonne référencée dans la requête principale. La condition e1.departmet_id = e2.department_id est la clé de la sous-requête car elle nous permet d'obtenir la moyenne de tous les employés du département de la ligne actuelle. Une fois que nous avons obtenu le salaire moyen du département, nous le comparons au salaire de l'employé et nous filtrons en conséquence. Exemple n° 13 - Obtenir toutes les lignes où une valeur figure dans le résultat d'une sous-requête Supposons que Jean Dupont gère plusieurs départements et que nous souhaitions obtenir la liste de tous les employés de ces départements. Nous utiliserons une sous-requête pour obtenir les identifiants des services gérés par Jean Dupont. Nous utiliserons ensuite l'opérateur IN pour trouver les employés travaillant dans ces services : SELECT first_name, last_name FROM employee e1 WHERE department_id IN ( SELECT department_id FROM department WHERE manager_name=‘John Smith’) La sous-requête précédente est une sous-requête à plusieurs lignes : elle renvoie plus d'une ligne. En fait, elle renvoie plusieurs lignes parce que Jean Dupont gère plusieurs services. Lorsque vous travaillez avec des sous-requêtes à plusieurs lignes, vous devez utiliser des opérateurs spécifiques (comme IN) dans la condition WHERE impliquant la sous-requête. Exemple n° 14 - Recherche de lignes dupliquées en SQL Si une table contient des lignes en double, vous pouvez les trouver avec SQL. Utilisez une requête avec une clause GROUP BY incluant toutes les colonnes du tableau et une clause HAVING pour filtrer les lignes qui apparaissent plus d'une fois. Voici un exemple : SELECT employee_id, last_name, first_name, dept_id, manager_id, salary FROM employee GROUP BY employee_id, last_name, first_name, dept_id, manager_id, salary HAVING COUNT(*) > 1 Les lignes qui ne sont pas dupliquées auront un COUNT(*) égal à 1, mais les lignes qui existent plusieurs fois auront un COUNT(*) renvoyant le nombre de fois que la ligne existe. Je vous suggère l'article Comment trouver des valeurs dupliquées en SQL si vous voulez trouver plus de détails sur cette technique. Exemple #15 - Compter les lignes dupliquées Si vous souhaitez compter les lignes dupliquées, vous pouvez utiliser la requête suivante. Elle est similaire à la précédente, mais nous ajoutons un COUNT(*) dans la liste SELECT pour montrer combien de fois chaque ligne dupliquée apparaît dans la table : SELECT employee_id, last_name, first_name, dept_id, manager_id, salary, COUNT(*) AS number_of_rows FROM employee GROUP BY employee_id, last_name, first_name, dept_id, manager_id, salary HAVING COUNT(*) > 1 Là encore, vous trouverez des informations précieuses sur la gestion des enregistrements en double dans l'article Comment trouver des enregistrements en double en SQL. Exemple n° 16 - Recherche d'enregistrements communs entre deux tables Si vous avez deux tables avec le même schéma ou si deux tables ont un sous-ensemble de colonnes en commun, vous pouvez obtenir les lignes qui apparaissent dans les deux tables avec l'opérateur set INTERSECT. Supposons que nous ayons un instantané de la table employee prise en janvier 2020 et appelée employee_2020_jan et que nous voulons obtenir la liste des employés qui existent dans les deux tableaux. Nous pouvons le faire avec cette requête : SELECT last_name, first_name FROM employee INTERSECT SELECT last_name, first_name FROM employee_2020_jan Nous obtiendrons ainsi la liste des employés qui figurent dans les deux tables. Ils auront peut-être des valeurs différentes dans les colonnes telles que salary ou dept_id. En d'autres termes, nous obtenons les employés qui travaillaient pour l'entreprise en janvier 2020 et qui travaillent toujours pour l'entreprise. Si vous souhaitez en savoir plus sur les opérateurs d'ensemble, je vous suggère de lire l'article Introduction aux opérateurs d'ensemble SQL : Union, Union Tout, Moins et Intersection. Exemple n° 17 - Regroupement de données avec ROLLUP La clause GROUP BY en SQL est utilisée pour agréger des lignes dans des groupes et appliquer des fonctions à toutes les lignes du groupe, en renvoyant une seule valeur de résultat. Par exemple, si nous voulons obtenir un rapport avec le montant total des salaires par département et par niveau d'expertise, nous pouvons effectuer la requête suivante : SELECT dept_id, expertise, SUM(salary) total_salary FROM employee GROUP BY dept_id, expertise La clause GROUP BY est facultative ROLLUP, ce qui lui permet d'inclure des regroupements supplémentaires dans une seule requête. L'ajout de la clause ROLLUP à notre exemple pourrait nous donner la somme totale des salaires pour chaque département (quel que soit le niveau d'expertise de l'employé) et la somme totale des salaires pour l'ensemble de la table (quels que soient le département et le niveau d'expertise de l'employé). La requête modifiée est la suivante : SELECT dept_id, expertise, SUM(salary) total_salary FROM employee GROUP BY ROLLUP (dept_id, expertise) Et le résultat sera : dept_idexpertisetotal_salary AccountSenior90000 AccountJunior80000 AccountNULL170000 CEOSenior250000 CEONULL250000 ITSenior250000 ITNULL250000 SalesJunior110000 SalesSemisenior140000 SalesSenior180000 SalesNULL430000 NULLNULL1100000 Les lignes de l'ensemble de résultats comportant un NULL sont les lignes supplémentaires ajoutées par la clause ROLLUP. Une valeur NULL dans la colonne expertise signifie un groupe de lignes pour une valeur spécifique de dept_id mais sans valeur spécifique de expertise. En d'autres termes, il s'agit du montant total des salaires pour chaque dept_id. De la même manière, la dernière ligne du résultat ayant une valeur NULL pour les colonnes dept_id et expertise signifie le total général pour tous les départements de l'entreprise. Si vous souhaitez en savoir plus sur la clause ROLLUP et d'autres clauses similaires comme CUBE, l'article Grouping, Rolling, and Cubing Data contient de nombreux exemples. Exemple n° 18 - Somme conditionnelle Dans certains cas, nous devons résumer ou compter des valeurs en fonction de certaines conditions. Par exemple, si nous voulons obtenir le total des salaires dans les départements Ventes et Ressources humaines combinés et dans les départements Informatique et Support combinés, nous pouvons exécuter la requête suivante : SELECT SUM (CASE WHEN dept_id IN (‘SALES’,’HUMAN RESOURCES’) THEN salary ELSE 0 END) AS total_salary_sales_and_hr, SUM (CASE WHEN dept_id IN (‘IT’,’SUPPORT’) THEN salary ELSE 0 END) AS total_salary_it_and_support FROM employee La requête renvoie une seule ligne avec deux colonnes. La première colonne indique le salaire total pour les départements Ventes et Ressources humaines. Cette valeur est calculée à l'aide de la fonction SUM() sur la colonne salary - mais uniquement lorsque l'employé appartient au département des ventes ou des ressources humaines. Un zéro est ajouté à la somme lorsque l'employé appartient à un autre département. Le même principe est appliqué à la colonne total_salary_it_and_support. Les articles Patrons SQL utiles : Compression conditionnelle avec CASE et Comment utiliser CASE WHEN avec SUM() en SQL fournissent plus de détails sur cette technique. Exemple #19 - Grouper les lignes par plage Dans l'exemple de requête suivant, nous allons créer les plages de salaires low, medium, et high. Nous allons ensuite compter le nombre d'employés dans chaque tranche de salaire : SELECT CASE WHEN salary <= 750000 THEN ‘low’ WHEN salary > 750000 AND salary <= 100000 THEN ‘medium’ WHEN salary > 100000 THEN ‘high’ END AS salary_category, COUNT(*) AS number_of_employees FROM employee GROUP BY CASE WHEN salary <= 750000 THEN ‘low’ WHEN salary > 750000 AND salary <= 100000 THEN ‘medium’ WHEN salary > 100000 THEN ‘high’ END Dans cette requête, nous utilisons l'instruction CASE pour définir la fourchette de salaires de chaque employé. Vous pouvez voir deux fois la même instruction CASE. La première définit les fourchettes, comme nous venons de le dire ; la seconde, dans le GROUP BY, agrège les enregistrements et applique la fonction COUNT(*) à chaque groupe d'enregistrements. Vous pouvez utiliser l'instruction CASE de la même manière pour calculer des comptes ou des sommes pour d'autres niveaux personnalisés. Comment utiliser l'instruction CASE en SQL explique d'autres exemples d'instructions CASE comme celle utilisée dans cette requête. Exemple n°20 - Calculer un total courant en SQL Un total courant est un modèle SQL très courant, fréquemment utilisé en finance et dans l'analyse des tendances. Lorsque vous disposez d'une table qui stocke une mesure quotidienne, telle qu'une table sales avec les colonnes day et daily_amount, vous pouvez calculer le total courant en tant que somme cumulée de toutes les valeurs daily_amount précédentes. SQL fournit une fonction de fenêtre appelée SUM() pour effectuer cette opération. Dans la requête suivante, nous calculons les ventes cumulées pour chaque jour : SELECT day, daily_amount, SUM (daily_amount) OVER (ORDER BY day) AS running_total FROM sales La fonction SUM() utilise la clause OVER() pour définir l'ordre des lignes ; toutes les lignes antérieures au jour actuel sont incluses dans la clause SUM(). Voici un résultat partiel : daydaily_amountrunning_total Jan 30, 20231000.001000.00 Jan 31, 2023800.001800.00 Feb 1, 2023700.002500.00 Les deux premières colonnes day et daily_amount sont des valeurs extraites directement du tableau sales. La colonne running_total est calculée par l'expression : SUM (daily_amount) OVER (order by day) Vous voyez clairement que le running_total est la somme cumulée des daily_amounts précédents. Si vous souhaitez approfondir ce sujet, je vous suggère l'article Qu'est-ce qu'un total mobile SQL et comment le calculer ? qui comprend de nombreux exemples explicatifs. Exemple #21 - Calculer une moyenne mobile en SQL Une moyenne mobile est une technique de séries temporelles permettant d'analyser les tendances des données. Elle est calculée comme la moyenne de la valeur actuelle et d'un nombre spécifié de valeurs immédiatement précédentes pour chaque point dans le temps. L'idée principale est d'examiner comment ces moyennes se comportent dans le temps au lieu d'examiner le comportement des points de données originaux ou bruts. Calculons la moyenne mobile pour les 7 derniers jours en utilisant le tableau de l'exemple précédent sales de l'exemple précédent : SELECT day, daily_amount, AVG (daily_amount) OVER (ORDER BY day ROWS 6 PRECEDING) AS moving_average FROM sales Dans la requête ci-dessus, nous utilisons la fonction de fenêtre AVG() pour calculer la moyenne en utilisant la ligne actuelle (aujourd'hui) et les 6 lignes précédentes. Comme les lignes sont classées par jour, la ligne actuelle et les 6 lignes précédentes définissent une période d'une semaine. L'article Qu'est-ce qu'une moyenne mobile et comment la calculer en SQL aborde ce sujet en détail ; consultez-le si vous souhaitez en savoir plus. Exemple #22 - Calculer une différence (Delta) entre deux colonnes sur des lignes différentes Il y a plusieurs façons de calculer la différence entre deux lignes en SQL. Une façon de le faire est d'utiliser les fonctions de fenêtre LEAD() et LAG(), comme nous le ferons dans cet exemple. Supposons que nous voulions obtenir un rapport avec le montant total vendu chaque jour, mais que nous voulions également obtenir la différence (ou delta) par rapport au jour précédent. Nous pouvons utiliser une requête comme celle-ci : SELECT day, daily_amount, daily_amount - LAG(daily_amount) OVER (ORDER BY day) AS delta_yesterday_today FROM sales L'expression clé de cette requête est : daily_amount - LAG(daily_amount) OVER (ORDER BY day) Les deux éléments de la différence arithmétique proviennent de lignes différentes. Le premier élément provient de la ligne actuelle et LAG(daily_amount) provient de la ligne du jour précédent. LAG() renvoie la valeur de n'importe quelle colonne de la ligne précédente (sur la base de ORDER BY spécifié dans la clause OVER ). Si vous voulez en savoir plus sur LAG() et LEAD(), je vous suggère l'article Comment calculer la différence entre deux lignes en SQL. Exemple #23 - Calculer la différence d'une année sur l'autre Les comparaisons d'une année sur l'autre (YOY) ou d'un mois sur l'autre sont un moyen populaire et efficace d'évaluer la performance de plusieurs types d'organisations. Vous pouvez calculer la comparaison en valeur ou en pourcentage. Dans cet exemple, nous utiliserons le tableau sales qui contient des données d'une granularité quotidienne. Nous devons d'abord agréger les données à l'année ou au mois, ce que nous ferons en créant un CTE avec des montants agrégés par année. Voici la requête : WITH year_metrics AS ( SELECT extract(year from day) as year, SUM(daily_amount) as year_amount FROM sales GROUP BY year) SELECT year, year_amount, LAG(year_amount) OVER (ORDER BY year) AS revenue_previous_year, year_amount - LAG(year_amount) OVER (ORDER BY year) as yoy_diff_value, ((year_amount - LAG(year_amount) OVER (ORDER BY year) ) / LAG(year_amount) OVER (ORDER BY year)) as yoy_diff_perc FROM year_metrics ORDER BY 1 La première expression à analyser est celle utilisée pour calculer yoy_diff_value: year_amount - LAG(year_amount ) OVER (ORDER BY year) Elle est utilisée pour calculer la différence (en tant que valeur) entre le montant de l'année en cours et celui de l'année précédente en utilisant la fonction de fenêtre LAG() et en classant les données par année. Dans l'expression suivante, nous calculons la même différence en pourcentage. Ce calcul est un peu plus complexe car nous devons diviser par le montant de l'année précédente. (Remarque : nous utilisons l'année précédente comme base pour le calcul du pourcentage, l'année précédente est donc égale à 100 %). ((year_amount-LAG(year_amount ) OVER(ORDER BY year))/LAG(year_amount ) OVER(ORDER BY year)) Dans l'article Comment calculer les différences d'une année sur l'autre en SQL, vous trouverez plusieurs exemples de calcul des différences d'une année sur l'autre et d'un mois sur l'autre. Exemple #24 - Utiliser Requêtes récursives pour gérer les hiérarchies de données Certaines tables en SQL peuvent avoir une sorte de hiérarchie de données implicite. Par exemple, notre table employee contient une adresse manager_id pour chaque employé. Nous avons un manager qui est responsable d'autres managers, qui à leur tour ont d'autres employés sous leur responsabilité, et ainsi de suite. Dans ce type d'organisation, nous pouvons avoir une hiérarchie à plusieurs niveaux. Dans chaque ligne, la colonne manager_id fait référence à la ligne du niveau immédiatement supérieur dans la hiérarchie. Dans ce cas, une requête fréquente est d'obtenir la liste de tous les employés qui dépendent (directement ou indirectement) du PDG de l'entreprise (qui, dans ce cas, a le employee_id de 110). La requête à utiliser est la suivante : WITH RECURSIVE subordinate AS ( SELECT employee_id, first_name, last_name, manager_id FROM employee WHERE employee_id = 110 -- id of the top hierarchy employee (CEO) UNION ALL SELECT e.employee_id, e.first_name, e.last_name, e.manager_id FROM employee e JOIN subordinate s ON e.manager_id = s.employee_id ) SELECT employee_id, first_name, last_name, manager_id FROM subordinate ; Dans cette requête, nous avons créé un CTE récursif appelé subordinate. Il s'agit de la partie clé de cette requête car elle traverse la hiérarchie des données en allant d'une ligne aux lignes de la hiérarchie situées immédiatement en dessous d'elle. Deux sous-requêtes sont reliées par une adresse UNION ALL; la première sous-requête renvoie la ligne supérieure de la hiérarchie et la seconde renvoie le niveau suivant, en ajoutant ces lignes au résultat intermédiaire de la requête. Ensuite, la deuxième sous-requête est exécutée à nouveau pour renvoyer le niveau suivant, qui sera à nouveau ajouté à l'ensemble des résultats intermédiaires. Ce processus est répété jusqu'à ce qu'aucune nouvelle ligne ne soit ajoutée au résultat intermédiaire. Enfin, la requête principale consomme les données de l'ETC subordinate et renvoie les données de la manière attendue. Si vous voulez en savoir plus sur les requêtes récursives en SQL, je vous suggère l'article Comment trouver tous les employés sous chaque manager en SQL. Exemple #25 - Trouver la longueur d'une série en utilisant Fonctions de fenêtrage Supposons que nous ayons une table contenant des données sur l'enregistrement des utilisateurs. Nous stockons des informations sur le nombre d'utilisateurs enregistrés à chaque date. Nous définissons une série de données comme la séquence de jours consécutifs au cours desquels les utilisateurs se sont inscrits. Un jour où aucun utilisateur ne s'inscrit interrompt la série de données. Pour chaque série de données, nous voulons trouver sa longueur. Le tableau ci-dessous présente les séries de données : iddayRegistered users 1Jan 25 202351 2Jan 26 202346 3Jan 27 202341 4Jan 30 202359 5Jan 31 202373 6Feb 1 202334 7Feb 2 202356 8Feb 4 202334 Il y a 3 séries de données différentes, représentées dans des couleurs différentes. Nous cherchons une requête pour obtenir la longueur de chaque série de données. La première série de données commence le 25 janvier et a une longueur de 3 éléments, la deuxième commence le 30 janvier et a une longueur de 4 éléments, et ainsi de suite. La requête est la suivante : WITH data_series AS ( SELECT RANK() OVER (ORDER BY day) AS row_number, day, day - RANK() OVER (ORDER BY day) AS series_id FROM user_registration ) SELECT MIN(day) AS series_start_day, MAX(day) AS series_end_day, MAX(day) - MIN (day) + 1 AS series_length FROM data_series GROUP BY series_id ORDER BY series_start_date Dans la requête précédente, l'ETC contient la colonne series_id, qui est une valeur destinée à être utilisée comme identifiant pour les lignes de la même série de données. Dans la requête principale, la clause GROUP BY series_id est utilisée pour agréger les lignes de la même série de données. Nous pouvons ensuite obtenir le début de la série avec MIN(day) et sa fin avec MAX(day). La longueur de la série est calculée à l'aide de l'expression : MAX(day) - MIN (day) + 1 Si vous souhaitez approfondir ce sujet, l'article Comment calculer la longueur d'une série avec SQL fournit une explication détaillée de cette technique. Pratiquez SQL avancé avec les cours LearnSQL.com SQL est un langage puissant et facile à apprendre. Dans cet article, nous avons montré 25 exemples de requêtes SQL avancées. Tous ces exemples peuvent être expliqués en 5 minutes environ, ce qui montre que SQL est un langage accessible même lorsque vous devez réaliser des rapports ou des requêtes complexes. Si vous souhaitez continuer à apprendre le langage SQL, je vous suggère nos cours de SQL avancé : Fonctions de fenêtrage, Requêtes récursives, et Les extensions GROUP BY en SQL. Ils couvrent tous des domaines complexes du langage SQL avec des mots simples et de nombreux exemples. Augmentez vos compétences et investissez en vous-même avec SQL ! Tags: sql apprendre sql pratique en ligne advanced sql