5 exemples pratiques d'ETC SQL

Les expressions de table communes (CTE) ont été introduites dans SQL pour améliorer la lisibilité et la structure des requêtes SQL, en particulier celles qui nécessitent plusieurs étapes pour obtenir les résultats nécessaires. Dans cet article, nous allons passer en revue plusieurs exemples pour montrer comment les CTE SQL peuvent vous aider avec des calculs complexes et des structures de données hiérarchiques.

Expressions de table communes en SQL

Les expressions de table commune (CTE), également appelées clauses WITH, permettent de créer des sous-requêtes nommées qui sont ensuite référencées dans la requête principale. Les CTE ont été introduites dans SQL pour améliorer la lisibilité et la structure d'une instruction SQL.

La syntaxe de base des ETC est la suivante :

WITH subquery_name AS
(SELECT … subquery ...)
SELECT … main query ...

Nous commençons par le mot-clé WITH suivi du nom que nous attribuons à l'ETC (sous-requête). Ensuite, nous plaçons le mot-clé AS et incluons la sous-requête entre parenthèses. Une fois l'ETC défini, nous passons à la requête principale, où nous pouvons faire référence à cet ETC par son nom.

Si vous ne connaissez pas les CTE, vous pouvez consulter cet article qui explique plus en détail le fonctionnement des CTE.

Il est possible d'avoir plusieurs ETC dans une même requête, de référencer un ETC à l'intérieur d'un autre (ETC imbriqués) ou même de référencer un ETC à l'intérieur de lui-même (ETC récursifs). Cela nous donne toute une série d'outils et de possibilités.

Exemples d'ETC SQL

Pour montrer comment les ETC peuvent vous aider dans diverses tâches analytiques, je vais passer en revue cinq exemples pratiques.

Nous commencerons par le tableau ordersavec quelques informations de base comme la date de la commande, l'identifiant du client, le nom du magasin, l'identifiant de l'employé qui a enregistré la commande et le montant total de la commande.

Maintenant, écrivons quelques requêtes SQL ! Vous pouvez également vous entraîner aux CTE SQL dans ce cours interactif qui couvre tous les types de CTE. Requêtes récursives cours interactif qui couvre tous les types d'ETC.

Exemple 1

Dans notre premier exemple, nous voulons comparer le montant total de chaque commande avec le montant moyen de la commande dans le magasin correspondant.

Nous pouvons commencer par calculer le montant moyen de la commande pour chaque magasin à l'aide d'un CTE et ajouter cette colonne à la sortie de la requête principale :

WITH avg_per_store AS
  (SELECT store, AVG(amount) AS average_order
   FROM orders
   GROUP BY store)
SELECT o.id, o.store, o.amount, avg.average_order AS avg_for_store
FROM orders o
JOIN avg_per_store avg
ON o.store = avg.store;

Comme vous le voyez, notre requête commence par un CTE appelé avg_per_store. À l'aide de cet ETC, nous créons une table qui répertorie tous les magasins et le montant moyen des commandes par magasin. Ensuite, dans la requête principale, nous sélectionnons pour afficher l'ID de la commande, le nom du magasin, le montant de la commande dans le tableau d'origine orders et le montant moyen de la commande pour chaque magasin (avg_for_store) à partir de l'ETC défini précédemment.

Voici le résultat :

Ce tableau nous permet de comparer chaque commande au montant moyen de la commande dans le magasin correspondant.

Passons maintenant à un exemple plus complexe.

Exemple 2

Nous allons comparer différents magasins. Plus précisément, nous voulons voir comment le montant moyen des commandes pour chaque magasin se compare au minimum et au maximum du montant moyen des commandes pour tous les magasins.

Comme dans notre premier exemple, nous commencerons par calculer le montant moyen des commandes pour chaque magasin à l'aide d'un CTE. Ensuite, nous définirons deux autres CTE :

• Pour calculer le minimum du montant moyen de la commande pour tous les magasins.
• Pour calculer le maximum du montant moyen de la commande pour tous les magasins.

Notez que ces deux CTE utiliseront le résultat du premier CTE.

Enfin, dans la requête principale, nous joindrons les trois CTE pour obtenir les informations dont nous avons besoin :

WITH avg_per_store AS (
    SELECT store, AVG(amount) AS average_order
    FROM orders
    GROUP BY store),
    min_order_store AS (
    SELECT MIN (average_order) AS min_avg_order_store
    FROM avg_per_store),
    max_order_store AS (
    SELECT MAX (average_order) AS max_avg_order_store
    FROM avg_per_store)
SELECT avg.store, avg.average_order, min.min_avg_order_store,
max.max_avg_order_store
FROM avg_per_store avg
CROSS JOIN min_order_store min
CROSS JOIN max_order_store max;

Comme vous le voyez, même avec plusieurs ETC imbriqués, la requête SQL reste propre et facile à suivre. Si vous deviez utiliser des sous-requêtes, vous devriez imbriquer une sous-requête dans les deux autres et la répéter plusieurs fois dans la même requête. Ici, avec les CTE, nous définissons simplement les trois CTE au début, puis nous y faisons référence lorsque c'est nécessaire.

Voici le résultat de cette requête :

Vous pouvez facilement voir comment chaque magasin se compare aux autres en termes de montant moyen des commandes. Bien sûr, lorsque vous n'avez que trois magasins, nous pourrions les comparer sans ajouter les colonnes min_avg_order_store et max_avg_order_store. Cependant, lorsque vous devez analyser les performances de plusieurs magasins en fonction de différentes mesures, cette approche peut s'avérer très utile.

Lisez ce guide pour connaître les meilleures pratiques en matière d'ETC SQL.

Exemple 3

Dans notre prochain exemple, nous continuerons à comparer les performances de nos magasins, mais avec quelques métriques différentes. Supposons que notre entreprise considère que les commandes inférieures à 200 $ sont petites et que les commandes égales ou supérieures à 200 $ sont importantes. Nous voulons maintenant calculer le nombre de grosses commandes et de petites commandes de chaque magasin.

Pour réaliser cette tâche à l'aide des clauses WITH, nous avons besoin de deux expressions de table courantes :

• Pour obtenir le nombre de grosses commandes pour chaque magasin.
• Pour obtenir le nombre de petites commandes pour chaque magasin.

Certains magasins peuvent ne pas avoir de grosses ou de petites commandes, ce qui conduit à des valeurs de NULL. Nous devons nous assurer que nous ne perdons aucun magasin lors des JOINs. Pour cette raison, je préfère avoir un autre CTE qui produit simplement une liste de tous les magasins. Ensuite, dans la requête principale, nous joindrons cet ETC aux deux ETC contenant les mesures sur les grosses et les petites commandes :

WITH stores AS
   (SELECT store
    FROM orders
    GROUP BY store),
  big AS
  (SELECT store, COUNT(*) AS big_orders
   FROM orders
   WHERE amount >= 200.00
   GROUP BY store),
  small AS
  (SELECT store, COUNT(*) AS small_orders
   FROM orders
   WHERE amount < 200.00
   GROUP BY store)
SELECT s.store, b.big_orders, sm.small_orders
FROM stores s
FULL JOIN big b
ON s.store = b.store
FULL JOIN small sm
ON s.store = sm.store;

Ainsi, dans cette requête, nous

• Définissons l'ETC stores pour obtenir une liste complète des magasins.
• Définir l'ETC big pour calculer, pour chaque magasin, le nombre de commandes dont le montant total est égal ou supérieur à 200 $.
• Définir l'ETC small pour calculer, pour chaque magasin, le nombre de commandes dont le montant est inférieur à 200 $.
• Joignez les trois CTE.

Voici le résultat :

Nous pouvons maintenant constater que le magasin de l'Ouest obtient de très bons résultats ; toutes ses commandes sont supérieures à 200 $. Le magasin du centre est également performant, avec deux commandes supérieures à 200 $ et une commande inférieure à 200 $. Seule la moitié des commandes du magasin de l'Est sont importantes, avec deux commandes supérieures à 200 $ et deux commandes inférieures à 200 $.

Exemple 4

Pour les deux exemples suivants, nous utiliserons le tableau ci-dessous contenant des informations de base sur les employés de notre entreprise. Plus précisément, nous disposons de l'ID de l'employé, de son prénom, de son nom, de l'ID de son supérieur, de son service et du montant de sa dernière prime.

Calculons maintenant la prime moyenne par département, puis comptons combien d'employés ont eu des primes supérieures à la moyenne de leur département respectif et combien ont eu des primes inférieures.

Les expressions de tableau communes peuvent s'avérer très pratiques pour des calculs aussi complexes. Nous aurons trois CTE dans cette requête SQL :

• Calculer le montant moyen des primes pour chaque département.
• Calculer, par département, le nombre d'employés dont les primes sont supérieures à la moyenne de leur département respectif.
• Calculer, par département, le nombre d'employés dont les primes sont inférieures à la moyenne de leur département respectif.

Dans la requête principale, nous joindrons les trois CTE.

WITH avg_bonus_department AS
    (SELECT department, AVG(bonus) AS average_bonus
    FROM employees
    GROUP BY department),
    above_average AS
    (SELECT e.department, count(*) AS employees_above_average
     FROM employees e
     JOIN avg_bonus_department avg
     ON e.department = avg.department
     WHERE bonus > average_bonus
     GROUP BY e.department),
     below_average AS
     (SELECT e.department, count(*) AS employees_below_average
     FROM employees e
     JOIN avg_bonus_department avg
     ON e.department = avg.department
     WHERE bonus < average_bonus
     GROUP BY e.department)
SELECT avg.department, avg.average_bonus, aa.employees_above_average, ba.employees_below_average
FROM avg_bonus_department avg
LEFT JOIN above_average aa
ON avg.department = aa.department
LEFT JOIN below_average ba
ON avg.department = ba.department;

Voici le résultat de la requête :

Puisqu'il n'y a qu'une seule personne en finance, la prime moyenne du département est exactement égale à la prime de cette personne. Par conséquent, nous n'avons personne dans le département des finances dont le bonus était supérieur ou inférieur à la moyenne (reflété par des valeurs NULL dans le résultat). Il en va de même pour le PDG.

Pour le département Ventes, on constate que le bonus moyen est de 828,57 $, et que seules deux personnes sur sept ont des bonus supérieurs à la moyenne du département.

Nous vous laissons interpréter de la même manière les résultats des départements Marketing et Opérations, et nous passons à un exemple encore plus complexe avec une requête récursive.

Exemple 5

Lesexpressions de tableau commun peuvent se référencer elles-mêmes, ce qui en fait un outil parfait pour analyser les structures hiérarchiques. Voyons cela à l'aide d'un exemple.

En utilisant les informations du tableau employees et du tableau orders nous pouvons dessiner l'organigramme suivant de notre entreprise. Le personnel du magasin est considéré comme faisant partie de l'équipe des ventes. En outre, dans le orders nous pouvons voir quels employés ont des commandes dans quels magasins, ce qui nous permet de déterminer le magasin auquel chaque vendeur appartient.

Supposons maintenant que nous ayons besoin de connaître le niveau de chaque employé dans l'organigramme (c'est-à-dire que le niveau 1 correspond au PDG, le niveau 2 à ses subordonnés directs, etc.) ). Nous pouvons ajouter une colonne qui indique ce niveau à l'aide d'une requête récursive :

WITH RECURSIVE levels AS (
  SELECT
    id,
    first_name,
    last_name,
    superior_id,
    1 AS level
  FROM employees
  WHERE superior_id IS NULL
  UNION ALL
  SELECT
    employees.id,
    employees.first_name,
    employees.last_name,
    employees.superior_id,
    levels.level + 1
  FROM employees, levels
  WHERE employees.superior_id = levels.id
)

SELECT *
FROM levels;

Comme vous le voyez, l'ETC levels dans cette requête se réfère à elle-même. Il commence par sélectionner l'enregistrement correspondant au grand patron, celui qui n'a pas de supérieur (c'est-à-dire superior_id IS NULL). Nous attribuons 1 au niveau de cette personne, puis nous utilisons UNION ALL pour ajouter d'autres enregistrements, en en ajoutant un pour chaque niveau de gestion dans la structure organisationnelle.

Voici le résultat :

Le sujet des requêtes récursives est assez complexe, c'est pourquoi je n'entrerai pas dans les détails ici. Mais n'oubliez pas de consulter cet article qui explique les ETC récursifs avec des exemples, en particulier si vous travaillez avec des données hiérarchiques.

Et consultez cet article pour d'autres exemples d'ETC SQL.

Pratiquons les ETC SQL !

J'espère que ces exemples vous ont montré à quel point les ETC peuvent être pratiques pour différentes tâches analytiques. Ils permettent d'améliorer la lisibilité et la structure de vos requêtes SQL, d'effectuer des calculs imbriqués et complexes, et sont utiles pour le traitement efficace de données hiérarchiques. Pour en savoir plus sur l'utilisation des CTE, consultez cet article.

Si vous souhaitez maîtriser les expressions de table communes, je vous recommande de commencer par ce cours unique de Requêtes récursives unique en son genre. Il comprend 114 exercices interactifs couvrant tous les types d'ETC, y compris les ETC simples, les ETC imbriquées et les ETC récursives. À la fin du cours, vous saurez comment gérer les requêtes SQL avec les CTE, comment et quand imbriquer les CTE, et comment utiliser les CTE récursifs pour se déplacer dans les modèles de données hiérarchiques.

Si vous souhaitez maîtriser d'autres outils avancés pour l'analyse de données avec SQL, vous devriez envisager de suivre le cours SQL avancé ! Il va au-delà des CTE et couvre également les fonctions de fenêtre et les extensions GROUP BY en SQL. De plus, LearnSQL.fr propose de nombreuses façons de mettre en pratique ces concepts SQL avancés en ligne.

Merci de votre lecture et bon apprentissage !