14. CTE vs Sous-Requête

Introduction

Supposons que nous avons la table fictive Personnel et que nous voulons afficher les salariés ayant un salaire au-dessus du salaire moyen de leur service. Il faudrait procéder en 2 étapes. La première consisterait à obtenir les salaires moyens par service. Puis lors de la seconde étape, il faudrait joindre les salaires moyens des services aux salaires du personnel et les comparer.

La table Personnel

La première étape, c'est-à-dire obtenir les salaires moyens par service, donnerait le résultat suivant :

Question 14.1

Écrire une requête pour afficher les salaires moyens par service. Il faut utiliser la table personnel pour générer les salaires moyens par service. Pour accéder à la console SQL.

Table SalaireMoyen

La réponse de la question 14.1 doit être similaire à la table ci-dessus

Question 14.2

Jointure les tables Personnel et SalaireMoyen afin d'afficher les salariés ayyant un salaire supérieur au salaire moyen de leur service.
Le résultat de la requête doit être similaire au résultat ci-dessous. Pour accéder à la console SQL.

Dans la pratique, pour avoir le résultat attendu dans la question 14.2, il ne sera pas nécessaire de créer une table SalaireMoyen. L'usage de Common Table Expression (expression de table commune), aussi appelée CTE, basée sur Personnel suffit.

CTE

Une CTE (Common Table Expression) est une expression qui permet de définir une table temporaire qui pourra être utilisée dans la suite d'une requête. Cette écriture simplifie la syntaxe et améliore la lisibilité du code SQL.

Une CTE commence par la clause WITH, suivie d'un nom attribué à la table temporaire. Ensuite, la clause AS est utilisée pour spécifier la requête qui définit la table temporaire. L'exemple ci-dessous reprend le cas d'usage vu dans l'introduction de ce chapitre.

-- Étape 1 : Écriture de CTE
WITH salaireMoyen AS (
    -- Définition du premier CTE
    SELECT service, AVG(salaire)
    FROM personnel
    GROUP BY service
)

-- Étape 2 : Écriture de la requête principale
SELECT p.*, s.salaireMoyen
FROM personnel as p
INNER JOIN salaireMoyen as s
  ON p.service = s.service
WHERE p.salaire > s.salaireMoyen;

On distingue 2 étapes, la première avec une CTE qui commence par la clause WITH, puis la seconde avec la requête principale qui débute avec la clause SELECT.

Étape 1 : La CTE est définie avec le nom SalaireMoyen à l'aide de la clause WITH. La requête à l'intérieur du CTE transforme les données de manière à calculer les salaires moyens par service.

Étape 2 : La requête principale joint SalaireMoyen et Personnel afin d'afficher les membres du personnel qui ont un salaire supérieur au salaire moyen de leur service.

Lorsque la requête principale est exécutée, elle utilise les résultats de la CTE pour récupérer les détails des employés avec le nom de leur département.

Question 14.3

Que fait la requête ci-dessous ?

Attention : Une ligne ne représente pas une commande mais un type d'article commandé.

WITH avgSales2011 AS (
    SELECT SUM(unitPrice*orderQuantity*(1-unitPriceDiscountPct))/COUNT(DISTINCT salesOrderNumber) as avgSalesAmount2011
    FROM  factInternetSale
    WHERE YEAR(orderDate) = 2011
)
SELECT f.salesOrderNumber, SUM(f.unitPrice*f.orderQuantity*(1-f.unitPriceDiscountPct)) as amount
FROM factInternetSale as f
CROSS JOIN avgSales2011
WHERE YEAR(f.orderDate) = 2012
GROUP BY f.salesOrderNumber, avgSalesAmount2011
HAVING SUM(f.unitPrice*f.orderQuantity*(1-f.unitPriceDiscountPct)) > avgSalesAmount2011;

Pour excécuter la requête, copier le code et coller le dans la console SQL.

Pour utiliser plusieurs CTE dans une requête, la clause WITH sera précisée uniquement pour la première CTE, les suivantes seront séparées par des virgules comme dans l'exemple ci-dessous.

WITH 
    CTE1 AS (
        -- Définition du premier CTE
        SELECT * FROM table1
    ), -- Une virgule car cette CTE est suivie d'une autre CTE.
    CTE2 AS (
        -- Définition du deuxième CTE
        SELECT * FROM table2
    ) -- Pas de virgule car cette CTE est suivie de la requête principale.
SELECT *
FROM CTE1
JOIN CTE2 ON CTE1.column = CTE2.column;

Attention : Entre la dernière CTE (CTE2 dans l'exemple ci-dessus) et la dernière requête commençant par SELECT (aussi appelée la requête principale), il n'y aura pas de virgule.

Dans cet exemple, il y a 2 CTE: CTE1 et CTE2. Elles sont définies puis utilisées dans la requête principale qui effectue une jointure entre les 2 CTE. Les CTE sont séparées par des virgules après le mot-clé WITH. Cela simplifie la syntaxe et permet d'ajouter autant de CTE que nécessaire dans une requête sans avoir à répéter la clause WITH pour chacune d'entre elles.

Prenons un second exemple, cette fois, il s'agit de répondre à une question business :
Quels sont les clients qui ont le plus contribués au chiffre d'affaires d'adventureWork en 2011 ?

Pour répondre à cette question, il faut procéder en 2 étapes :

Etape 1 : Créer 2 CTE (Common Table Expressions) :
• La première CTE est utilisée pour calculer le chiffre d'affaires total en 2011.
• La deuxième CTE calcule la valeur totale des achats réalisés pour chaque client.

Etape 2 : Les résultats obtenus dans la première partie sont joints et analysés pour calculer le pourcentage du chiffre d'affaires total que représente chaque client.

--- Première étape
WITH allSales AS (
    SELECT SUM(unitPrice*orderQuantity*(1-unitPriceDiscountPct)) as totalSalesAmount
    FROM factInternetSale
    WHERE YEAR(orderDate) = 2011
),
customerSpend AS (
    SELECT customerKey, SUM(unitPrice*orderQuantity*(1-unitPriceDiscountPct)) as totalSpend
    FROM factInternetSale
    WHERE YEAR(orderDate) = 2011
    GROUP BY customerKey
    ORDER BY totalSpend DESC
)
--- Seconde étape
SELECT customerKey, totalSpend,
       ROUND(100*totalSpend/totalSalesAmount, 2) AS pctTurnover
FROM customerSpend
CROSS JOIN allSales;

Affiche le montant total dépensé en 2011 par client ainsi que le pourcentage du chiffre d'affaires que ce montant représente pour 2011. Pour excécuter la requête, copier le code et coller le dans la console SQL.

Question 14.4

Afficher la contribution de chaque client au chiffre d'affaires en pourcentage (considérez-le comme un pourcentage même si le signe % n'est pas représenté). Montrez 3 colonnes : customerKey, le total dépensé par client en 2011 (appeler ce champ totalSpend11) et le pourcentage du chiffre d'affaire total que ça représente (appeler ce champ pctTurnover11). Triez les clients du plus gros acheteur au plus petit acheteur. Pour accéder à la console SQL.

Question 14.5

Parmi les produits commandés en 2011 et 2012, afficher les produits qui ont été plus commandés en 2012 qu'en 2011. Pour accéder à la console SQL.

Question 14.6

Combien de clients sont nécessaires en 2011 pour atteindre au moins 80% du chiffre d'affaires en 2011 ? Pour accéder à la console SQL.

Les CTE sont beaucoup utilisées dans le monde professionnel car elles sont lisibles, réutilisables, décomposables et optimisables.

Lisibilité : Les CTE améliorent la lisibilité du code en permettant la définition de sous-ensembles logiques de données à l'intérieur de la requête.
Réutilisation : Une CTE peut être référencée plusieurs fois dans la même requête, facilitant ainsi la réutilisation du même ensemble de données temporaire.
Décomposition : Les CTE permettent de décomposer une requête complexe en parties logiques et plus gérables.
Optimisation : Certains systèmes de gestion de base de données peuvent optimiser l'exécution des requêtes en utilisant les informations fournies par les CTE.

En résumé, les CTE sont des outils puissants pour améliorer la clarté et la modularité du code, elles offrent la possibilité de référencer des ensembles de données temporaires.

Sous-Requête

Une sous-requête (ou subquery en anglais) est une requête imbriquée à l'intérieur d'une requête principale. Elle peut être utilisée dans différentes parties d'une requête SQL, y compris dans les clauses SELECT, FROM, WHERE, HAVING et JOIN. La sous-requête fournit un ensemble de résultats temporaires qui est utilisé par la requête principale pour accomplir une tâche spécifique.

Comment utiliser les sous-requêtes ?

Dans la clause SELECT

Une sous-requête permet de récupérer une valeur unique à afficher dans une colonne de la requête principale.

SELECT column1, (SELECT MAX(column2) FROM table2) AS max_value
FROM table1;

Dans l'exemple ci-dessus, la sous-requête est utilisée dans la liste des colonnes sélectionnées. Elle calcule la valeur maximale de column2 dans table2 et renvoie ce résultat comme une colonne nommée max_value qui sera ajoutée au résultat de la requête principale.

Question 14.7

Utiliser une sous-requête pour calculer la part de chaque commande en pourcentage du chiffre d'affaires total de 2011. Intégrer la quantité d'articles commandés (orderQuantity) et les remises (UnitPriceDiscountPct) dans le calcul du prix final. Classer les résultats par contribution, de la plus élevée à la plus faible. La sous-requête doit permettre de déterminer le chiffre d'affaires total. Pour accéder à la console SQL.

Dans la clause FROM

Une sous-requête peut être utilisée pour traiter une table temporaire, dont les résultats sont exploités dans la requête principale.

SELECT column1
FROM (SELECT * FROM table1 WHERE condition) AS temp_table;

Dans l'exemple ci-dessus, la sous-requête est placée dans la clause FROM pour créer une table temporaire, ici nommée temp_table. Cette table temporaire est ensuite utilisée comme source de données dans la requête principale.

Un second exemple

/* Requête qui associe à chaque commande, le coût d'achat au fournisseur 
   et le coût de vente au client. */
SELECT *
FROM (
    SELECT customerKey, OrderDate, dueDate, salesOrderNumber,
           SUM(orderQuantity*ProductStandardCost) as costAmount,
           SUM(unitPrice*orderQuantity*(1-UnitPriceDiscountPct)) as SalesAmount
    FROM factInternetSale
    GROUP BY customerKey, OrderDate, dueDate, salesOrderNumber
) as o
WHERE costAmount > 150;

La sous-requête est utilisée pour agréger les articles par commande et ensuite filtrer les coûts associés aux commandes. Pour accéder à la console SQL.

Dans la clause WHERE

Une sous-requête peut être utilisée pour filtrer les résultats de la requête principale selon une condition spécifique.

SELECT column1
FROM table1
WHERE column2 = (SELECT MAX(column2) FROM table1);

Dans cet exemple, la sous-requête sert à déterminer une condition dans la clause WHERE de la requête principale.

• Sous-requête : (SELECT MAX(column2) FROM table1) calcule la valeur maximale de la colonne column2 dans table1.
• Requête principale : La clause WHERE column2 = utilise le résultat de la sous-requête pour filtrer les lignes de table1 où column2 correspond à cette valeur maximale.
• Résultat final : Cette requête retourne les valeurs de column1 uniquement pour les lignes de table1 ayant la valeur maximale de column2.

A présent l'objectif est d'afficher les commandes dont le montant dépasse la moyenne des commandes de 2011.

WITH orders AS (
    SELECT customerKey, OrderDate, dueDate,salesOrderNumber, 
                 SUM(orderQuantity*ProductStandardCost) as costAmount,
                 SUM(unitPrice*OrderQuantity*(1-UnitPriceDiscountPct)) as SalesAmount
          FROM factInternetSale
          GROUP BY customerKey, OrderDate, dueDate, salesOrderNumber)

SELECT *
FROM orders
WHERE SalesAmount > (SELECT AVG(SalesAmount) FROM orders WHERE YEAR(OrderDate) = 2011) 

Une CTE et une sous-requête sont utilisées pour afficher les commandes qui ont un montant supérieur au montant moyen. Pour accéder à la console SQL.

Question 14.8

Identifier les commandes de mars 2012 à mars 2013 qui excèdent la quantité moyenne d'articles par commande sur la même période? Pour accéder à la console SQL.

Dans la clause HAVING

Une sous-requête dans la clause HAVING permet de filtrer les résultats agrégés.

SELECT column1, COUNT(*)
FROM table1
GROUP BY column1
HAVING COUNT(*) > (SELECT AVG(count_column) FROM stats_table);

Dans cet exemple, la sous-requête est utilisée pour définir une condition dans la clause HAVING de la requête principale.

• Sous-requête : (SELECT AVG(count_column) FROM stats_table) calcule la moyenne des valeurs de la colonne count_column dans la table stats_table.
  

• Requête principale : La table table1 est groupée par la colonne column1 à l'aide de GROUP BY. La fonction d'agrégation COUNT(*) calcule le nombre de lignes dans chaque groupe. La clause HAVING filtre les groupes en conservant uniquement ceux pour lesquels le nombre total de lignes est supérieur à la moyenne calculée par la sous-requête.
  

• Résultat final : La requête retourne les valeurs uniques de column1 et leur nombre total de lignes, mais uniquement pour les groupes où ce nombre est supérieur à la moyenne des valeurs de count_column dans stats_table.

  
  

Un second exemple

Dans l'exemple précédent, uniquement les commandes ayant un montant supérieur au montant moyen était affiché. Ce même résultat peut être affiché avec une requête différente.

WITH orders AS (
    SELECT salesOrderNumber, 
            SUM(unitPrice*OrderQuantity*(1-UnitPriceDiscountPct)) as SalesAmount
          FROM factInternetSale
          WHERE YEAR(OrderDate) = 2011
          GROUP BY salesOrderNumber
          )

SELECT customerKey, OrderDate, dueDate,salesOrderNumber, 
                 SUM(orderQuantity*ProductStandardCost) as costAmount,
                 SUM(unitPrice*OrderQuantity*(1-UnitPriceDiscountPct)) as SalesAmount
          FROM factInternetSale
          GROUP BY customerKey, OrderDate, dueDate, salesOrderNumber
          HAVING SUM(unitPrice*OrderQuantity*(1-UnitPriceDiscountPct)) > 
                (SELECT AVG(SalesAmount) 
                 FROM orders)

Cette requête extrait les commandes pour lesquelles le chiffre d'affaires dépasse la moyenne des ventes associées aux commandes de l'année 2011. Tester la requête dans la console SQL.

Question 14.9

Répondre à la même question que la précédente en utilisant une sous-requête dans la clause HAVING. Pour accéder à la console SQL.

Dans la clause JOIN

Une sous-requête au niveau de la jointure permet de joindre une table à une seconde table filtrée.

SELECT column1, column2
FROM table1
JOIN (SELECT id, value FROM table2 WHERE condition) AS temp_table
ON table1.id = temp_table.id;

Question 14.10

Reformuler la requête ci-dessous de manière à obtenir le même résultat en utilisant des sous-requêtes dans les clauses FROM et JOIN.

--- Première étape
WITH allSales AS (
    SELECT SUM(unitPrice*orderQuantity*(1-unitPriceDiscountPct)) as totalSalesAmount
    FROM factInternetSale
    WHERE YEAR(orderDate) = 2011
    ), --virgule ici

customerSpend AS ( 
    SELECT customerKey, SUM(unitPrice*orderQuantity*(1-unitPriceDiscountPct)) as totalSpend
    FROM factInternetSale
    WHERE YEAR(orderDate) = 2011
    GROUP BY customerKey
    ORDER BY totalSpend DESC
    ) -- pas de virgule car c'est la dernière CTE avant la requête principale

SELECT customerKey, totalSpend, 
       ROUND(100*totalSpend/totalSalesAmount,2) AS pctTurnover
FROM customerSpend
  CROSS JOIN allSales

Les sous-requêtes ajoutent de la flexibilité aux requêtes SQL en permettant d'effectuer des opérations plus complexes et de manipuler des données de manière plus précise. Cependant, elles doivent être utilisées judicieusement pour éviter des performances médiocres sur de grandes bases de données.

En résumé

Les sous-requêtes et les CTE (Common Table Expressions) sont deux concepts puissants en SQL, chacun ayant ses avantages et ses cas d'utilisation spécifiques. Voici quelques points à considérer pour choisir entre les deux dans différents contextes.

CTE (Common Table Expressions)

• Lisibilité : Les CTE améliorent la lisibilité du code en permettant de définir une expression de table temporaire au début de la requête. Cela rend le code plus modulaire et plus facile à comprendre.
• Réutilisation : Les CTE peuvent être réutilisées dans la même requête, ce qui favorise la réutilisation du code et permet de simplifier les requêtes complexes.
• Organisation : Les CTE sont particulièrement utiles lorsque vous avez besoin de traiter plusieurs étapes logiques dans une requête et que chaque étape est mieux exprimée séparément.

Sous-requêtes

• Utilisation : Les sous-requêtes peuvent être utilisées dans différentes parties d'une requête, y compris dans les clauses SELECT, FROM, WHERE, HAVING et JOIN.
• Flexibilité : Les sous-requêtes sont plus flexibles car elles peuvent être directement incorporées dans la requête principale, ce qui les rend adaptées à des scénarios variés.
• Réutilisation : Les sous-requêtes peuvent être réutilisées à plusieurs endroits dans une requête. Cependant, si vous avez besoin d'utiliser la même logique dans plusieurs requêtes, vous devrez répéter la sous-requête.

Contexte d'utilisation

• Complexité : Si la logique de votre requête implique des opérations complexes, des filtres multiples ou des étapes de calcul pouvant être décomposées, l'utilisation de CTE peut améliorer la lisibilité et l'organisation du code.

• Performance : En général, les performances entre sous-requêtes et CTE sont similaires. Cependant, dans certains cas, le moteur de requête peut optimiser l'exécution d'une CTE de manière plus efficace qu'une sous-requête.

En résumé, le choix entre sous-requêtes et CTE dépend du contexte spécifique de votre requête et de vos préférences en termes de lisibilité et de modularité du code. Si la flexibilité est primordiale, les sous-requêtes peuvent être adaptées. En revanche, optez pour les CTE si la lisibilité, la réutilisation et l'organisation du code sont vos priorités.

Exercices supplémentaires

Question 14.11

Quel pourcentage chaque produit représente-t-il dans le chiffre d'affaires total du mois d'avril 2011 ?

Question 14.12

Quels sont les 5 produits ayant généré le plus de revenus par mois, pour chaque mois de l'année 2012 ?

Question 14.13

Très vite les requêtes peuvent devenir compliquées à lire lorsqu'on utilise des sous-requêtes. Que fait la requête ci-dessous?

SELECT S.SalesTerritoryKey, SalesTerritoryRegion, amount, 
       ROUND(100*amount/(SELECT SUM(SalesAmount) 
                         FROM factInternetSale)
            ,2) as pctTotalAmount
FROM dimSalesTerritory as T
  LEFT JOIN (SELECT SalesTerritoryKey, 
                    ROUND(SUM(SalesAmount),0) as amount
             FROM factInternetSale
             GROUP BY SalesTerritoryKey) as S
  ON T.SalesTerritoryKey = S.SalesTerritoryKey

Question 14.14

Affichez les noms des produits les plus achetés par zone géographique en 2011. Les zones géographiques retenues doivent représenter au moins 15 % du chiffre d'affaires total de l'année.

Question 14.15

Listez les jours où le chiffre d'affaires est supérieur à celui de la journée précédente.

SQL Console

Question 14.1
Question 14.2
Question 14.3
Question 14.4
Question 14.5
Question 14.6
Question 14.7
Question 14.8
Question 14.9
Question 14.10
Question 14.11
Question 14.12
Question 14.13
Question 14.14
Question 14.15