25 exemples de requêtes SQL avancées

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_id	first_name	last_name	dept_id	manager_id	salary	expertise
100	John	White	IT	103	120000	Senior
101	Mary	Danner	Account	109	80000	junior
102	Ann	Lynn	Sales	107	140000	Semisenior
103	Peter	O'connor	IT	110	130000	Senior
106	Sue	Sanchez	Sales	107	110000	Junior
107	Marta	Doe	Sales	110	180000	Senior
109	Ann	Danner	Account	110	90000	Senior
110	Simon	Yang	CEO	null	250000	Senior
111	Juan	Graue	Sales	102	37000	Junior

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 :

product	package_size	price
Corn flake	1 pound box	2.43
Corn flake	3 pound box	7.29
Corn flake	5 pound box	12.15
Sugared corn flake	1 pound box	2.85
Sugared corn flake	3 pound box	8.55
Sugared corn flake	5 pound box	14.25
Rice flake	1 pound box	1.98
Rice flake	3 pound box	5.94
Rice flake	5 pound box	9.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_id	expertise	total_salary
Account	Senior	90000
Account	Junior	80000
Account	NULL	170000
CEO	Senior	250000
CEO	NULL	250000
IT	Senior	250000
IT	NULL	250000
Sales	Junior	110000
Sales	Semisenior	140000
Sales	Senior	180000
Sales	NULL	430000
NULL	NULL	1100000

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 :

day	daily_amount	running_total
Jan 30, 2023	1000.00	1000.00
Jan 31, 2023	800.00	1800.00
Feb 1, 2023	700.00	2500.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 :

id	day	Registered users
1	Jan 25 2023	51
2	Jan 26 2023	46
3	Jan 27 2023	41
4	Jan 30 2023	59
5	Jan 31 2023	73
6	Feb 1 2023	34
7	Feb 2 2023	56
8	Feb 4 2023	34

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 !