Aller au contenu principal

Techniques avancées

Introduction​

"OK… j’ai peut-être pas l’air d’un expert en SQL… mais attends de voir ce que je fais avec un GROUPING SET et une pizza."

Maîtriser les bases du SQL, c’est cool. Mais là, on passe au niveau supérieur : celui où chaque requête devient un enchaînement millimétré, chaque ligne de code une attaque précise. Ce laboratoire plonge dans les techniques avancées de SQL Server, là où seuls les vrais data-guerriers s’aventurent. Tu découvriras l’agilité du ROLLUP et du CUBE pour explorer des dimensions cachées, la puissance du PIVOT pour retourner la réalité comme une crêpe, et l’élégance des fonctions de fenêtrage pour voir chaque ligne dans le contexte de l’ensemble. Tu feras aussi face à la légendaire requête récursive, maîtresse de l’arborescence infinie, et tu apprendras à rafraîchir automatiquement les vues, comme un souffle de kung-fu frais sur tes données. Bref, c’est plus qu’un laboratoire : c’est ton entraînement. Et qui sait ? À la fin… tu pourrais bien devenir le guerrier dragon du SQL.

Prérequis​

Dans ce laboratoire, nous allons utiliser une base de données préfaite par Microsoft: AdventureWorks Pour vous faciliter le travail, nous allons construire une image custom basée sur celle de Microsoft. Téléchargez ce fichier zip, décompressez le et ouvrez un terminal dans le dossier. Exécutez la commande suivante:

docker build -t adventureworks .

Le build prendra un peu de temps, car Docker va charger le fichier de sauvegarde mis à disposition par Microsoft.

Ensuite, exécutez la commande suivante:

docker run -p 1433:1433 -m 2G adventureworks

La base de données reste accessible avec les mêmes informations qu'auparavant:

  • Adresse : localhost
  • Port : 1433
  • Utilisateur : sa
  • Mot de passe : Password1

CTE et requête récursive​

Une CTE (Common Table Expression) permet de créer une table temporaire en mémoire. Cette technique est parfois utilisée pour simplifier des requêtes utilisant des sous-requêtes. En effet, on pourrait avoir plusieurs fois la même sous-requête utilisée dans un WHERE ou FROM par exemple. Comme elle n'a pas de nom, nous sommes obligés de répéter le code. En utilisant une CTE, il devient possible d'utiliser le nom de la CTE directement.

Une CTE commence avec WITH, puis le nom de la CTE, puis AS avec le code de la requête entre (). Par exemple:

WITH ExempleCTE AS (
    ...
) -- PAS DE ; !!!

SELECT * FROM ExempleCTE WHERE ExempleCTE.ID IN (...);

Par contre, les CTE sont indispensables pour les requêtes récursives. En effet, vous avez besoin de la définition de la requête pour pouvoir l'utiliser de manière récursive. Dans une approche récursive, la CTE est divisée en 2 parties: l'ancre et la récursion. L'ancre est la partie d'initialisation de votre requête et consiste en une simple requête. Dans la partie récursion, on appelle l'ancre sur laquelle on va pouvoir faire des opérations (par exemple des join). Ces deux parties sont séparées généralement par un UNION ALL pour indiquer comment combiner les deux parties. Cependant, il existe d'autres possibilités comme INTERSECT ou EXCEPT.

WITH ExempleCTE AS (
    --- Ancre
    SELECT ...

    UNION ALL -- Ou autre chose pour gérer la fusion

    SELECT ... FROM ExempleCTE ...
    -- Vous pouvez utiliser ExempleCTE dans le FROM, JOIN, etc. comme une table classique

)

SELECT * FROM ExempleCTE WHERE ExempleCTE.ID IN (...);
attention

Les colonnes dans l'ancre et la récursion doivent être dans le même ordre et du même type pour permettre la fusion.

Cette approche est typique des requêtes de type "hiérarchie". On pourrait imaginer résoudre le problème avec des joins. Le souci vient du fait qu'on n'a aucune idée du nombre de jointures à faire.

Imaginons la table suivante

IDNomIDEnfant
1Alice3
2Bob3
3CharlieNULL

Si je vous demande de trouver le nom de l'enfant d'Alice et Bob, c'est très facile de le faire avec un join:

SELECT
    P2.Nom as Nom
FROM Person AS P1
WHERE P1.ID = 1
JOIN Person AS P2 ON P1.IDEnfant = P2.ID;

Et si on modifiait l'exercice légèrement ?

IDNomIDEnfant
1Alice3
2Bob3
3Charlie5
.........

Si je vous demande de donner toute la descendance d'Alice ? Impossible de connaître le nombre de join nécessaires à l'avance (et qui voudrait écrire une requête avec 25 jointures ?!). Dans ce cas, on utilisera une requête récursive.

WITH Descendance AS (
    -- Ancre

    UNION ALL

    -- Récursion
)

SELECT Nom
FROM Descendance;

L'ancre concerne le cas de base: on voudrait partir d'Alice.

SELECT
    ID,
    Nom,
    IDEnfant
FROM Person
WHERE ID = 1;

Ce qui nous donnerait ceci:

IDNomIDEnfant
1Alice3

Pour la partie récursion, on souhaite prendre ce résultat, faire une jointure pour avoir l'enfant direct d'Alice. On sait que le résultat de l'ancre est appelable depuis le nom du CTE. On aurait donc quelque chose comme ceci:

SELECT
    P.ID,
    P.Nom,
    P.IDParent
FROM Descendance AS D
JOIN Person AS P ON D.IDParent = P.ID;

Ce qui nous donnera ceci à la première itération

IDNomIDEnfant
3Charlie5

Qui est ensuite fusionné avec l'ancre pour donner ceci:

IDNomIDEnfant
1Alice3
3Charlie5

Le résultat de l'itération devient l'ancre de la prochaine itération et le cycle continue jusqu'au moment où l'itération ne peut plus continuer. C'est-à-dire qu'on est arrivé à quelqu'un qui n'a pas d'enfant.

astuce

Quand vous réalisez une requête récursive, vous devriez vérifier si votre ancre est bonne. Ensuite, tentez de faire la première itération de la partie récursion pour savoir si vous avez les colonnes nécessaires et comment le faire.

Exercices - CTE et requête récursive​

Malheureusement, la base de données AdventureWorks ne propose pas d'exercice simple pour les requêtes récursives. Nous allons donc créer une table avec un petit ensemble de données:

CREATE TABLE dbo.Employes (
    ID INT PRIMARY KEY,
    Nom NVARCHAR(50),
    ManagerID INT NULL
);

INSERT INTO dbo.Employes (ID, Nom, ManagerID) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 2),
(6, 'Frank', 3),
(7, 'Grace', 4);

Je vous demande de:

  1. Affichez tous les employés sous la responsabilité directe ou indirecte de Bob (ID = 2)
Indice
IDNomManagerID
4David2
5Eve2
  1. Retrouvez tous les managers de Grace (ID = 7), jusqu’à la patronne.
Indice
Nom
David
Bob
Alice
  1. Affichez l’organigramme complet à partir d’Alice (ID = 1) avec un niveau (0 = Alice, 1 = Bob/Charlie, etc.)
Indice
SELECT
    E.ID AS ID,
    E.Nom AS Nom,
    0 AS Niveau,
    E2.ID AS GereID,
    E2.Nom AS GereNom
FROM dbo.Employes AS E
JOIN dbo.Employes E2 ON E.ID = E2.ManagerID
WHERE E.ID = 1

Window functions​

Les Window functions permettent de prendre une partie des lignes d'une table et d'appliquer un traitement sur ces lignes. Ce traitement peut être: une somme, une moyenne, une numérotation des lignes, etc. Mais on souhaite le faire de manière indépendante pour chaque ligne. Prenons un exemple: vous souhaitez faire une moyenne des ventes par pays. Pas de problème, vous faites un GROUP BY selon les pays et vous utilisez AVG sur le total de chaque commande.

Maintenant, si je vous demande de me donner chaque vente et d'ajouter à côté la moyenne par pays ? C'est déjà un peu plus compliqué. Il faudrait reprendre la requête précédente et faire un JOIN sur les pays pour arriver au résultat. Avec une Window function, on peut très facilement réaliser ce travail.

Un autre exemple possible: pour chaque classe, on vous demande de sortir la liste des étudiants avec leur moyenne et celle de la classe à côté. À nouveau, ce sera assez compliqué à réaliser avec des jointures, mais avec une Window function, ce sera assez simple.

Alors comment fait-on ? Tout d'abord, vous allez utiliser la clause OVER et vous allez indiquer comment générer les sous-ensembles.

OVER (
    --- règle de répartition
    --- ordre
    --- comptage des lignes
)

Ces 3 parties sont optionnelles, mais nous allons dans un premier temps voir comment les utiliser.

  1. Règle de répartition : Ici, nous indiquons via PARTITION BY comment nous allons créer nos sous-ensembles. Dans le cas de la moyenne par classe, nous ferions quelque chose comme ceci: PARTITION BY classeID
  2. Ordre: nous pouvons trier les lignes selon un ordre: ASC (défaut) ou DESC. Intéressant si on souhaite prendre les X premières lignes ou dernières lignes. Par exemple, on pourrait faire une moyenne sur les 5 étudiants ayant les moyennes les plus élevées
  3. Comptage des lignes: permet de limiter la taille de la fenêtre. Intéressant pour le cas discuté juste avant où on voudrait prendre uniquement les 5 étudiants ayant la moyenne la plus élevée.

Concrètement, comment on réalisera notre moyenne par classe ? Comme ceci:

OVER (
    PARTITION BY classeID --- règle de répartition, donc notre partition se fera par classe
    -- pas besoin d'ordre
    -- pas besoin de limiter le nombre de lignes
)

Ok, parfait ! Maintenant, il ne reste plus qu'à construire notre requête:

SELECT
    ID,
    Nom,
    Prénom,
    ClasseID,
    Moyenne,
    AVG(Moyenne) OVER (PARTITION BY ClasseID) MoyenneDeLaClasse
FROM etudians

Comme vous le savez, AVG permet d'obtenir une moyenne. Mais ici, on fait la moyenne de la partition qui dépend de la ClasseID. Chaque ligne produite a donc:

  • L'ID de l'étudiant
  • Le nom de l'étudiant
  • Le prénom de l'étudiant
  • L'ID de la classe de l'étudiant
  • La moyenne de l'étudiant
  • La moyenne de la classe de l'étudiant

Un autre exemple ? Supposons que nous disposons d'une table avec une liste de mots. Chaque mot est composé de:

  • Un ID aléatoire
  • Le mot
  • La définition du mot

Je vous demande de construire une requête qui permet d'avoir pour chaque mot:

  • Toutes les informations du mot
  • L'ID du mot précédent dans l'ordre alphabétique
  • L'ID du mot suivant dans l'ordre alphabétique

Il suffirait de faire:

SELECT
    ID,
    Mot,
    Definition,
    LAG(ID) OVER (
        -- Pas de partition
        ORDER BY Mot -- on a besoin d'un ordre
        -- pas de comptage
    ) AS IDPrecedent,
    LEAD(ID) OVER (ORDER BY Mot) AS IDSuivant
FROM Dictionnaire

En ne précisant aucune règle de partition, la partition affecte l'ensemble de la table et permet de récupérer L'ID du précédent et suivant. Peut-on faire mieux ? On voit qu'on a deux fois la même définition pour les partitions. Comme pour les requêtes récursives, nous avons un équivalent des CTE pour avoir une définition réutilisable dans notre requête

SELECT
    ID,
    Mot,
    Definition,
    LAG(ID) OVER win AS IDPrecedent,
    LEAD(ID) OVER win AS IDSuivant
FROM Dictionnaire
WINDOW win AS (ORDER BY Mot)

Exercices - Window functions​

  1. Numérotez les commandes par client
  • Affichez les commandes de la table Sales.SalesOrderHeader avec :
  • SalesOrderID
  • CustomerID
  • OrderDate
  • Ajoutez une colonne qui numérote les commandes par client, de la plus ancienne à la plus récente (ROW_NUMBER()).
  • Classez les résultats par CustomerID, puis par OrderDate.
Indice

Uniquement les 10 premières lignes:

SalesOrderIDCustomerIDOrderDateNumCommande
43793110002011-06-21 00:00:00.0001
51522110002013-06-20 00:00:00.0002
57418110002013-10-03 00:00:00.0003
43767110012011-06-17 00:00:00.0001
51493110012013-06-18 00:00:00.0002
72773110012014-05-12 00:00:00.0003
43736110022011-06-09 00:00:00.0001
51238110022013-06-02 00:00:00.0002
53237110022013-07-26 00:00:00.0003
43701110032011-05-31 00:00:00.0001
  1. Moyenne des ventes par client
  • Affichez les commandes avec : SalesOrderID, CustomerID, TotalDue
  • Ajoutez une colonne indiquant la moyenne des commandes du même client.
  • Triez par CustomerID.
Indice

Uniquement pour les 10 premières lignes:

SalesOrderIDCustomerIDTotalDueMoyenne
43793110003756.98903038.3780
51522110002587.87693038.3780
57418110002770.26823038.3780
43767110013729.36402351.3958
51493110012674.02272351.3958
7277311001650.80082351.3958
43736110023756.98902988.6714
51238110022535.96402988.6714
53237110022673.06132988.6714
43701110033756.98902997.9718
  1. Utilisez LAG() pour comparer avec la commande précédente d’un même client
  • Affichez les commandes avec : SalesOrderID, CustomerID, OrderDate, TotalDue
  • Ajoutez le montant de la commande précédente du même client (LAG()).
  • Affichez aussi la différence de montant entre la commande et la précédente.
  • Utilisez une fenêtre nommée
Indice

Uniquement pour les 10 premières lignes:

SalesOrderIDCustomerIDOrderDateTotalDueTotPrecedentDiffPrecedent
43793110002011-06-21 00:00:00.0003756.9890NULLNULL
51522110002013-06-20 00:00:00.0002587.87693756.9890-1169.1121
57418110002013-10-03 00:00:00.0002770.26822587.8769182.3913
43767110012011-06-17 00:00:00.0003729.3640NULLNULL
51493110012013-06-18 00:00:00.0002674.02273729.3640-1055.3413
72773110012014-05-12 00:00:00.000650.80082674.0227-2023.2219
43736110022011-06-09 00:00:00.0003756.9890NULLNULL
51238110022013-06-02 00:00:00.0002535.96403756.9890-1221.0250
53237110022013-07-26 00:00:00.0002673.06132535.9640137.0973
43701110032011-05-31 00:00:00.0003756.9890NULLNULL

ROLLUP, CUBE et GROUPING SETS​

Ce que nous allons voir maintenant est une extension du GROUP BY. Vous allez voir, ce n'est pas très compliqué (en tout cas, moins que les requêtes récursives). Les 3 instructions que nous allons voir sont: ROLLUP, CUBE et GROUPING SETS

La première est connue pour faire un "total final". Si on reprend le contexte de l'école: on souhaite connaitre le nombre d'interrogations que chaque étudiant a réalisées. Mais on souhaite avoir le total d'interrogations réalisées (pour toute la classe). On pourrait faire quelque chose comme ceci:

SELECT
    E.ID,
    COUNT(IE.interroID) AS NBInterro
FROM etudiants AS E
JOIN interrogations_etudiants AS IE ON IE.etudiant = E.ID
GROUP BY(E.ID)

UNION ALL

SELECT
    NULL,
    COUNT(*) AS NBInterro
FROM etudiants AS E
JOIN interrogations_etudiants AS IE ON IE.etudiant = E.ID

On peut éviter cette requête lourde à la lecture par un ROLLUP. Le ROLLUP, contrairement à ce que pense la plupart des gens, ne sert pas qu'à faire des totaux. En réalité, ROLLUP va utiliser plusieurs combinaisons pour ses groupements. ROLLUP(c1, c2, c3) va provoquer les combinaisons suivantes:

  • Combinaisons de c1 - c2 - c3
  • Combinaisons de c1 - c2 (on ne tient plus compte de c3)
  • Combinaisons de c1 (on ne tient plus compte de c2 et de c3)
  • Tout (on ne tient plus compte de c1, de c2 et de c3, le fameux "total")

Si on remplace c1 par année, c2 par mois, c3 par semaine et qu'on faisait la somme des ventes, nous aurions:

  • Combinaisons des années, mois, semaines: donc une ligne sera la somme des ventes à une combinaison année, mois et semaine précises --> Ces lignes sont reconnaissables, car aucune colonne n'a la valeur NULL
  • Combinaisons des années, mois: donc une ligne sera la somme des ventes à une combinaison année et mois précis --> Ces lignes sont reconnaissables, car la colonne "semaine" vaudra NULL
  • Combinaisons des années: donc une ligne sera la somme des ventes d'une année --> Ces lignes sont reconnaissables, car les colonnes "semaine" et "mois" sont NULL
  • Tout: une seule ligne représentant le tout (sans tenir compte d'une année, mois ou semaine) --> Cette ligne est reconnaissable, car les colonnes "semaine", "mois" et "année" sont NULL

L'utilisation du ROLLUP se fait avec un GROUP BY:

SELECT
    IDProduit,
    SUM(vente) AS TotalVente
FROM produit
JOIN vente ON vente.IDProduit = produit.IDProduit
GROUP BY ROLLUP (produit.IDProduit, vente.annee, vente.mois, vente.semainee)

L'instruction CUBE n'est pas très différente. La différence avec ROLLUP est qu'elle fait de la combinatoire pour les combinaisons. CUBE(c1, c2) fera:

  • Combinaisons c1 et c2
  • Combinaisons c1
  • Combinaisons c2
  • c1 et c2 à NULL

Le ROLLUP(c1, c2) aurait fait

  • Combinaisons c1 et c2
  • Combinaisons c1
  • c1 et c2 à NULL

Enfin, GROUPING SETS est plus flexible dans son approche. En effet, c'est vous qui allez déterminer les combinaisons.

GROUPING SETS(
    (c1, c2, c3, c4) -- premier set
    (c1, c3) -- deuxieme set
    () -- le fameux "total"
)

GROUPING SETS peut être un remplaçant pour ROLLUP ou CUBE. Gardez juste en tête que GROUPING SETS demande à ce que vous indiquiez toutes les combinaisons à réaliser, ce qui est potentiellement très long.

Exercices - ROLLUP, CUBE et GROUPING SETS​

  1. Récapitulatif des ventes par année avec ROLLUP
  • Utilisez Sales.SalesOrderHeader pour récupérer :
    • YEAR(OrderDate) en tant qu’année
    • SUM(TotalDue) pour le chiffre d’affaires
  • Appliquez ROLLUP pour afficher un total général.
  • Si vous désirez une version améliorée, vous pouvez essayer de remplacer le NULL par Total
Indice
AnneeBenef
201114155699.5250
201237675700.3120
201348965887.9632
201422419498.3157
NULL123216786.1159
  1. Croisement des ventes par produit et année avec CUBE
  • Utilisez :
    • YEAR(OrderDate) pour l’année
    • sod.ProductID pour le produit
    • SUM(UnitPrice * OrderQty) pour le chiffre d’affaires
  • Appliquez CUBE sur (ProductID, Annee).
  • Vous aurez besoin de faire une jointure entre:
    • SalesOrderHeader: qui contient la date de la commande
    • SalesOrderDetail: qui contient le reste des informations
Indice

Uniquement les 10 premières lignes:

ProductIDAnneeTotalVentes
70720116681.7315
707201225155.0720
707201375271.5470
707201451757.5579
707NULL158865.9084
70820116883.5965
708201227368.8573
708201377477.4893
708201450346.7611
708NULL162076.7042
  1. Contrôle personnalisé des regroupements avec GROUPING SETS
  • On souhaite un reporting des ventes selon :
    • Par produit uniquement
    • Par année uniquement
    • Par produit et année
    • Et le total général
  • Utilisez GROUPING SETS pour définir ces 4 niveaux.
  • Affichez ProductID, Annee, TotalVentes.
Indice
ProductIDAnneeTotalVentes
70720116681.7315
70820116883.5965
70920113367.4650
7102011376.2000
71120117259.7381
71220112820.2452
71420116575.6112
715201115628.6110
71620117354.3020
71720119854.9867
  1. Suite du 3:
  • Ajoutez la fonction GROUPING pour identifier les lignes de total.
  • Une colonne "TypeLigne"
  • Pour y arriver, dans le SELECT vous aurez besoin du CASE qui permet d'indiquer les différents types de cas
Indice

Uniquement les 10 premières lignes:

ProductIDAnneeTotalVentesTypeLigne
70720116681.7315Detail
70820116883.5965Detail
70920113367.4650Detail
7102011376.2000Detail
71120117259.7381Detail
71220112820.2452Detail
71420116575.6112Detail
715201115628.6110Detail
71620117354.3020Detail
71720119854.9867Detail

Pivot​

L'instruction Pivot permet de fair pivoter une table (d'où le nom). Il consiste à transformer les lignes en colonnes. Par exemple:

MoisProduitQuantite
JanvierPommes100
JanvierBananes150
FévrierPommes120
FévrierBananes130
MarsPommes90
MarsBananes140

Après un PIVOT, on aurait ce résultat:

ProduitJanvierFévrierMars
Pommes10012090
Bananes150130140

Vous noterez que beaucoup de lignes ont disparu et c'est normal (notez qu'on a gagné une colonne au passage). Comment réaliser ce pivot ? Tout d'abord, il faut d'abord créer notre table source qui sera utilisée par le PIVOT:

--- le SELECT du FROM sera présenté plus tard
FROM (
    SELECT Mois, Produit, Quantite
    FROM Ventes
) AS SourceTable

La table source est prête pour la rotation, nous pouvons analyser PIVOT:

PIVOT (
    --- les fonctions d'aggrégats
    FOR [colonne de la table source] IN ([LES VALEURS DESIREES])
) AS PivotTable

Pourquoi une fonction d'aggrégat ? C'est simplement parce que nous pourrions avoir besoin de rassembler plusieurs valeurs entre-elles. L'exemple plus haut n'a pas un cas pareil, mais il suffirait d'avoir les deux lignes suivantes dans notre tableau:

MoisProduitQuantite
JanvierPommes100
JanvierPommes50

Après rotation de la table, nous n'aurions plus qu'une ligne. Il faudrait donc combiner les deux valeurs. Ce qui semble le plus logique dans notre cas serait SUM(). Complétons notre PIVOT:

PIVOT (
    SUM(Quantite)
    FOR Mois IN ([Janvier], [Fevrier], [Mars])
) AS PivotTable

La table effectue bien sa rotation et nous n'avons plus qu'à compléter le SELECT

SELECT Produit, [Janvier], [Février], [Mars]

La requête finale ressemblera donc à ceci:

SELECT Produit, [Janvier], [Février], [Mars]
FROM (
    SELECT Mois, Produit, Quantite
    FROM Ventes
) AS SourceTable
PIVOT (
    SUM(Quantite)
    FOR Mois IN ([Janvier], [Février], [Mars])
) AS PivotTable;

Le mot clé UNPIVOT permet... de faire l'inverse !

L'instruction est plus simple, car elle n'a pas besoin de fonction d'aggrégation. En effet, vous allez juste transformer vos colonnes en lignes. Il n'est plus nécessaire de "concentrer" des valeurs en une.

UNPIVOT (
    [Nom de la colonne qui va accueillir les valeurs des anciennes colonnes]
    FOR [Nom de la colonne qui va accueillir les anciennes colonnes]
    IN ([Nom des anciennes colonnes])
)

Ce qui nous donnerait ceci:

UNPIVOT (
    Quantite
    FOR Mois
    IN ([Janvier], [Février], [Mars])
) AS UnpivotTable

Il suffit maintenant de faire notre SELECT

SELECT Produit, Mois, Quantite
-- Supposons que nous ayons une table qui contient la table pivotée décrite plus haut
FROM VentesPivot
UNPIVOT (
    Quantite FOR Mois IN ([Janvier], [Février], [Mars])
) AS UnpivotTable

Et vous aurez le même résultat qu'au point de départ !

Exercices - Pivot​

  1. On souhaite afficher le chiffre d’affaires d’un produit donné réparti par année sous forme de colonnes (comme dans un tableau Excel).
  • Prenez un seul produit (ProductID = 776, par exemple).
  • Pour chaque année (YEAR(OrderDate)), calculez le chiffre d’affaires (SUM(UnitPrice * OrderQty)).
  • Utilisez PIVOT pour afficher une colonne par année.
Indice

Uniquement les 10 premières lignes:

AnneeMontant
20112024.9940
20118099.9760
20112024.9940
20112024.9940
20114049.9880
20114049.9880
20114049.9880
20116074.9820
20113374.9900
20116074.9820
  1. Utiliser PIVOT pour analyser plusieurs clients en ligne et plusieurs années en colonnes.
  • Identifiez les 5 clients ayant passé le total des commandes le plus élevé (je vous conseille d'utiliser une CTE).
  • Pour chacun d’eux, affichez le chiffre d’affaires par année en colonnes (2011 à 2014).
  • Classez les résultats par chiffre d’affaires total décroissant.
Indice
CustomerID
29818
29715
29722
30117
29614
  1. On dispose d’un tableau avec les ventes trimestrielles en colonnes (Q1, Q2, Q3, Q4). On veut le transformer en une table avec une colonne Trimestre et une colonne Montant.
  • Créez une table temporaire ou une table dérivée avec les colonnes suivantes : ProductID, Q1, Q2, Q3, Q4 (voir requête plus bas)
  • Utilisez UNPIVOT pour avoir un enregistrement par trimestre.
  • Vous aurez besoin d'une table temporaire pour faire cet exercice:
WITH VentesTrimestrielles AS (
    SELECT 776 AS ProductID, 1200 AS Q1, 1500 AS Q2, 1800 AS Q3, 1300 AS Q4
)
Indice
ProductIDMontantTrimestre
7761200Q1
7761500Q2
7761800Q3
7761300Q4

Vue matérialisée​

Nous avons vu durant le laboratoire précédent qu'une vue ne stocke pas les données. En réalité, nous stockons la requête qui est exécutée lors de l'appel à la vue.

Cependant, nous pouvons faire en sorte que les données soient directement stockées sur le disque. L'avantage de cette approche est que les données seront déjà présentes sur le disque et il ne sera plus nécessaire d'exécuter une requête pour générer les données. Le défaut (sinon, ce serait trop simple) vient qu'il faudra mettre à jour la table et la vue (duplication des données). Heureusement, SQL Server permet d'optimiser le processus pour nous. Nous devons juste faire deux choses:

  1. Lors de la déclaration de la vue, nous devons la lier à la table (ou aux tables).
  2. Créer un clustered index sur la vue.

SCHEMABINDING​

Comme expliqué, nous devons lier notre vue aux tables. C'est assez simple, il suffit d'ajouter WITH SCHEMABINDING lors de la création de la vue:

CREATE VIEW ExempleVue (
    ...
)
WITH SCHEMABINDING

Cette instruction a pour conséquence de lier la vue aux tables dans la requête. Dorénavant, il n'est plus possible de modifier les tables (si cela impacte la définition de la vue) ou de les supprimer. Nous allons maintenant matérialiser la vue.

Clustered Index​

Il s'agit d'un type spécial d'index qui force physiquement l'ordre des données sur le disque. Par exemple, si vous faites un clustered index sur les ID d'une table, ceux-ci seront dans l'ordre indiqué sur le support physique. Il y a un effet intéressant sur les vues: toutes les lignes de la vue se retrouvent sur le support physique. Grâce à cette instruction, nous pouvons donc matérialiser le contenu de la vue:

CREATE UNIQUE CLUSTERED INDEX ExempleIndex ON ExempleVue(ID) -- on supposera qu'on aura une colonne ID

Exercices - Vue matérialisée​

  1. Créez une vue indexée qui calcule le nombre de commandes et le total des ventes par produit et observez le rafraîchissement automatique.
  • Écrivez une requête qui agrège les données de la table Sales.SalesOrderDetail :
    • ProductID
    • Nombre de lignes de commande (utilisez COUNT_BIG(*))
    • Total des ventes (SUM(LineTotal))
  • Créez une vue avec WITH SCHEMABINDING.
  • Ajoutez un index cluster unique sur ProductID.
  • Faites un SELECT sur la vue pour voir les données.
  • Insérez une ligne dans Sales.SalesOrderDetail pour un produit existant, puis revérifiez si la vue a été mise à jour.
  1. Créez une vue matérialisée pour le reporting mensuel des ventes qui sera rafraîchie automatiquement.
  • Joignez les tables Sales.SalesOrderHeader et Sales.SalesOrderDetail.
  • Calculez :
    • Année et mois (YEAR(OrderDate) et MONTH(OrderDate))
    • SUM(LineTotal) pour les ventes mensuelles
    • COUNT_BIG(*) pour le nombre de lignes
  • Créez la vue avec SCHEMABINDING et un index cluster unique sur (Année, Mois).
  • Vérifiez que la vue est bien mise à jour après une insertion.

Conclusion​

"Tu n’as pas besoin d’être parfait. Tu as juste besoin d’être prêt à apprendre. Encore et encore."

À travers ce laboratoire, tu as affronté des requêtes plus complexes, exploré des fonctions avancées, et découvert des outils puissants pour rendre ton analyse plus fluide, plus riche, plus expressive. Les techniques comme le ROLLUP, le CUBE, ou encore le PIVOT, combinées aux fenêtres analytiques, aux requêtes récursives et au rafraîchissement automatique des vues, forment un arsenal de maître SQL. Mais n’oublie jamais : ce n’est pas parce que tu connais les fonctions que tu es un expert. C’est ce que tu fais avec qui compte. Continue de t’entraîner, de t’amuser avec tes données, d’oser poser des questions et de rater parfois — car c’est là, exactement là, que se trouve la vraie progression. Un jour peut-être, tu regarderas ton écran, tu verras une requête bien optimisée… et tu te diras : "Waaah… c’est moi qui ai fait ça ?"

remarque

L'introduction et la conclusion de ce laboratoire ont été générées par IA pour correspondre au style de Po dans Kung-Fu Panda.