Appliquer des fonctions de fenêtre pour l’analytique

14 minutes

Conseil / Astuce

Pour plus d’informations, consultez l’onglet Texte et images !

Les requêtes analytiques nécessitent souvent des calculs qui s’étendent sur plusieurs lignes tout en retournant des détails de ligne individuels. Les fonctions d’agrégation traditionnelles réduisent les lignes en groupes, ce qui perd des informations au niveau des lignes. Les fonctions de fenêtre résolvent ce défi en effectuant des calculs sur un ensemble de lignes liées à la ligne actuelle, sans réduire le jeu de résultats.

Comprendre la syntaxe de la fonction de fenêtre

Les fonctions de fenêtre calculent les valeurs sur une « fenêtre » de lignes définies par la OVER clause. Contrairement aux fonctions d’agrégation régulières, les fonctions de fenêtre ne regroupent pas de lignes dans une seule ligne de sortie. Au lieu de cela, ils calculent les valeurs entre les lignes associées tout en conservant toutes les lignes d’origine dans le résultat.

La syntaxe générale d’une fonction de fenêtre est la suivante :

function_name(arguments) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression [ASC | DESC]]
    [ROWS | RANGE frame_specification]
)

Les OVER composants de clause contrôlent la façon dont la fenêtre est définie :

PARTITION BY : divise les lignes en groupes (partitions) pour le calcul
ORDER BY : détermine l’ordre logique des lignes dans chaque partition
ROWS/RANGE : définit les limites du cadre par rapport à la ligne actuelle

La requête suivante illustre une fonction de fenêtre simple qui calcule un total de commandes en cours d’exécution par client :

SELECT 
    CustomerID,
    SalesOrderID,
    OrderDate,
    TotalDue,
    SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RunningTotal
FROM SalesLT.SalesOrderHeader
ORDER BY CustomerID, OrderDate;

Note

Lorsque vous spécifiez ORDER BY dans la OVER clause sans spécification d’image, le frame par défaut est RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW destiné aux fonctions d’agrégation. Cela crée des calculs cumulatifs.

Utiliser des fonctions de classement

Les fonctions de classement attribuent des nombres séquentiels aux lignes en fonction de leur position au sein d’une partition. SQL Server fournit quatre fonctions de classement. Chaque fonction gère les liens différemment :

ROW_NUMBER() affecte un nombre séquentiel unique à chaque ligne, sans doublons même pour les valeurs liées :

SELECT 
    ProductID,
    Name,
    ListPrice,
    ROW_NUMBER() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;

Le jeu de résultats ressemble à ceci :

ProductID   Name                        ListPrice   PriceRank
---------   -------------------------   ---------   ---------
749         Road-150 Red, 62            3578.27     1
750         Road-150 Red, 44            3578.27     2
751         Road-150 Red, 48            3578.27     3
771         Mountain-100 Silver, 38     3399.99     4

Cette requête classe tous les produits par prix le plus élevé au plus bas. Chaque produit reçoit un numéro unique, même si plusieurs produits partagent le même prix.

RANK() attribue le même rang aux valeurs liées, puis ignore les nombres pour tenir compte des liens :

SELECT 
    ProductID,
    Name,
    ListPrice,
    RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;

Le jeu de résultats ressemble à ceci :

ProductID   Name                        ListPrice   PriceRank
---------   -------------------------   ---------   ---------
749         Road-150 Red, 62            3578.27     1
750         Road-150 Red, 44            3578.27     1
751         Road-150 Red, 48            3578.27     1
771         Mountain-100 Silver, 38     3399.99     4

Lorsque deux produits ont des prix identiques, les deux reçoivent le même rang. Le classement du produit suivant reflète le nombre total de produits classés plus haut, ce qui crée des lacunes dans la séquence.

DENSE_RANK() affecte le même rang aux valeurs liées, mais n’ignore pas les nombres :

SELECT 
    ProductID,
    Name,
    ListPrice,
    DENSE_RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;

Le jeu de résultats ressemble à ceci :

ProductID   Name                        ListPrice   PriceRank
---------   -------------------------   ---------   ---------
749         Road-150 Red, 62            3578.27     1
750         Road-150 Red, 44            3578.27     1
751         Road-150 Red, 48            3578.27     1
771         Mountain-100 Silver, 38     3399.99     2

Comme RANK(), les valeurs liées partagent le même rang. Toutefois, DENSE_RANK() continue avec le nombre consécutif suivant, vous pouvez l’utiliser pour compter des niveaux de prix distincts.

NTILE(n) distribue les lignes dans un nombre spécifié de groupes à peu près égaux :

SELECT 
    ProductID,
    Name,
    ListPrice,
    NTILE(4) OVER (ORDER BY ListPrice DESC) AS PriceQuartile
FROM SalesLT.Product
WHERE ListPrice > 0;

Le jeu de résultats ressemble à ceci :

ProductID   Name                          ListPrice   PriceQuartile
---------   ---------------------------   ---------   -------------
749         Road-150 Red, 62              3578.27     1
771         Mountain-100 Silver, 38       3399.99     1
722         LL Road Frame - Black, 58     337.22      2
859         Half-Finger Gloves, S         24.49       4

Cette requête divise les produits en quatre groupes en fonction du prix. Les produits les plus chers sont en quartile 1, et les prix les plus bas sont en quartile 4. Utiliser NTILE() pour l’analyse de percentile ou la distribution du travail de manière uniforme.

La combinaison PARTITION BY avec les fonctions de classement active les classements par groupe :

SELECT 
    pc.Name AS Category,
    p.Name AS Product,
    p.ListPrice,
    ROW_NUMBER() OVER (
        PARTITION BY p.ProductCategoryID 
        ORDER BY p.ListPrice DESC
    ) AS CategoryPriceRank
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > 0;

Le jeu de résultats ressemble à ceci :

Category          Product                     ListPrice   CategoryPriceRank
---------------   -------------------------   ---------   -----------------
Road Bikes        Road-150 Red, 62            3578.27     1
Road Bikes        Road-150 Red, 44            3578.27     2
Mountain Bikes    Mountain-100 Silver, 38     3399.99     1
Mountain Bikes    Mountain-100 Black, 38      3374.99     2

Cette requête classe les produits dans chaque catégorie séparément. Le classement redémarre à 1 pour chaque catégorie, ce qui vous permet d'identifier le produit le plus coûteux dans chaque catégorie en appliquant un filtre sur CategoryPriceRank = 1.

Conseil / Astuce

Utilisez ROW_NUMBER() quand vous avez besoin d’une ligne exactement par rang (par exemple, recherche du N le plus élevé par groupe). Utilisez RANK() ou DENSE_RANK() lorsque vous avez besoin de conserver les informations de liaison à des fins de création de rapports.

Appliquer des fonctions de fenêtre d’agrégation

Les fonctions d’agrégation standard telles que SUM, , AVGCOUNT, MINet MAX peuvent être utilisées comme fonctions de fenêtre en ajoutant la OVER clause. Cela vous permet de calculer des agrégats tout en conservant des détails de ligne individuels.

La requête suivante montre comment calculer les totaux en cours d’exécution et les agrégats cumulés :

SELECT 
    SalesOrderID,
    OrderDate,
    TotalDue,
    SUM(TotalDue) OVER (ORDER BY OrderDate, SalesOrderID) AS RunningTotal,
    AVG(TotalDue) OVER (ORDER BY OrderDate, SalesOrderID) AS RunningAverage,
    COUNT(*) OVER (ORDER BY OrderDate, SalesOrderID) AS OrderNumber
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate, SalesOrderID;

Le jeu de résultats ressemble à ceci :

SalesOrderID   OrderDate    TotalDue    RunningTotal   RunningAverage   OrderNumber
------------   ----------   ---------   ------------   --------------   -----------
71774          2008-06-01   972.785     972.785        972.785          1
71776          2008-06-01   87.083      1059.868       529.934          2
71780          2008-06-01   42452.65    43512.518      14504.172        3
71782          2008-06-01   43962.79    87475.308      21868.827        4

Important

Lorsque vous utilisez des fonctions de fenêtre d’agrégation sans ORDER BY dans la OVER clause, la fonction calcule sur l’ensemble de la partition. L’ajout de ORDER BY crée un calcul cumulatif à partir du début de la partition jusqu'à la ligne active.

Définir des cadres de fenêtre avec `ROWS` et `RANGE`

Les cadres de fenêtre vous permettent de spécifier exactement les lignes relatives à la ligne actuelle qui doivent être incluses dans le calcul. La ROWS clause compte les lignes physiques, tandis que RANGE regroupe les lignes avec des valeurs égales.

Les limites de trame peuvent être spécifiées à l’aide de :

UNBOUNDED PRECEDING: à partir du début de la partition
n PRECEDING: n lignes avant la ligne actuelle
CURRENT ROW : la ligne actuelle
n FOLLOWING: n lignes après la ligne actuelle
UNBOUNDED FOLLOWING: à la fin de la partition

La requête suivante calcule une moyenne mobile sur les trois dernières commandes :

SELECT 
    SalesOrderID,
    OrderDate,
    TotalDue,
    AVG(TotalDue) OVER (
        ORDER BY OrderDate
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS MovingAvg3Orders
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate;

Le jeu de résultats ressemble à ceci :

SalesOrderID   OrderDate    TotalDue    MovingAvg3Orders
------------   ----------   ---------   ----------------
71774          2008-06-01   972.785     972.785
71776          2008-06-01   87.083      529.934
71780          2008-06-01   42452.65    14504.172
71782          2008-06-01   43962.79    28834.174

Cette requête calcule une moyenne mobile de 3 ordres en incluant la ligne actuelle et les deux lignes avant celle-ci. Pour la première ligne, une seule valeur est disponible, de sorte que la moyenne est TotalDueégale. À la troisième rangée, la fenêtre inclut toutes les trois rangées.

Utiliser des fonctions analytiques

Les fonctions analytiques vous permettent d’accéder aux données à partir d’autres lignes sans utiliser d’auto-jointures ou de sous-requêtes. Ces fonctions sont utiles pour l’analyse de série chronologique, la détection des tendances et la comparaison des valeurs actuelles par rapport aux valeurs historiques ou futures. Contrairement aux fonctions de fenêtre d’agrégation qui calculent des résumés, les fonctions analytiques récupèrent des valeurs spécifiques à partir de lignes spécifiques dans la fenêtre.

LAG() et LEAD() accèdent aux valeurs des lignes précédentes ou suivantes, comme suit :

SELECT 
    SalesOrderID,
    OrderDate,
    TotalDue,
    LAG(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS PreviousOrderTotal,
    LEAD(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS NextOrderTotal,
    TotalDue - LAG(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS ChangeFromPrevious
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate;

Le jeu de résultats ressemble à ceci :

SalesOrderID   OrderDate    TotalDue    PreviousOrderTotal   NextOrderTotal   ChangeFromPrevious
------------   ----------   ---------   ------------------   --------------   ------------------
71774          2008-06-01   972.785     0                    87.083           972.785
71776          2008-06-01   87.083      972.785              42452.65         -885.702
71780          2008-06-01   42452.65    87.083               43962.79         42365.567
71782          2008-06-01   43962.79    42452.65             0                1510.14

LAG() récupère une valeur d’une ligne précédente, tandis qu’elle LEAD() est récupérée à partir d’une ligne suivante. Le deuxième paramètre spécifie le nombre de lignes à regarder vers l’arrière ou à l’avance (la valeur par défaut est 1), et le troisième paramètre fournit une valeur par défaut lorsqu’aucune ligne n’existe (par exemple, pour la première ligne avec LAG()). Utilisez ces fonctions pour calculer les modifications de période sur période, identifier les tendances ou détecter les anomalies dans les données séquentielles.

FIRST_VALUE() et LAST_VALUE() retourne des valeurs à partir de la première ou de la dernière ligne du cadre :

SELECT 
    ProductID,
    Name,
    ListPrice,
    ProductCategoryID,
    FIRST_VALUE(Name) OVER (
        PARTITION BY ProductCategoryID 
        ORDER BY ListPrice DESC
    ) AS MostExpensiveInCategory,
    LAST_VALUE(Name) OVER (
        PARTITION BY ProductCategoryID 
        ORDER BY ListPrice DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS LeastExpensiveInCategory
FROM SalesLT.Product
WHERE ListPrice > 0;

Le jeu de résultats ressemble à ceci :

ProductID   Name                        ListPrice   ProductCategoryID   MostExpensiveInCategory    LeastExpensiveInCategory
---------   -------------------------   ---------   -----------------   ------------------------   ------------------------
749         Road-150 Red, 62            3578.27     5                   Road-150 Red, 62           LL Road Frame - Red, 58
750         Road-150 Red, 44            3578.27     5                   Road-150 Red, 62           LL Road Frame - Red, 58
722         LL Road Frame - Red, 58     337.22      5                   Road-150 Red, 62           LL Road Frame - Red, 58
771         Mountain-100 Silver, 38     3399.99     6                   Mountain-100 Silver, 38    Mountain-500 Black, 52

FIRST_VALUE() retourne la valeur de la première ligne de la fenêtre ordonnée, qui, dans ce cas, est le produit le plus coûteux par catégorie. LAST_VALUE() retourne le moins coûteux, mais nécessite une trame explicite pour inclure toutes les lignes. Ces fonctions vous aident à comparer chaque ligne à des valeurs de référence comme la valeur de référence la plus élevée, la plus basse ou la valeur de référence dans un groupe.

Note

LAST_VALUE() nécessite une spécification explicite de trame pour inclure des lignes après la ligne actuelle. Sans cela, la fenêtre par défaut inclut uniquement les lignes jusqu'à la ligne actuelle, ce qui fait que LAST_VALUE() renvoie la valeur de la ligne actuelle.

PERCENT_RANK() et CUME_DIST() calculent la position relative dans une partition :

SELECT 
    Name,
    ListPrice,
    PERCENT_RANK() OVER (ORDER BY ListPrice) AS PercentRank,
    CUME_DIST() OVER (ORDER BY ListPrice) AS CumulativeDistribution
FROM SalesLT.Product
WHERE ListPrice > 0
ORDER BY ListPrice;

Le jeu de résultats ressemble à ceci :

Name                        ListPrice   PercentRank   CumulativeDistribution
-------------------------   ---------   -----------   ----------------------
Patch Kit/8 Patches         2.29        0.0           0.0081
Road Tire Tube              3.99        0.0081        0.0162
Touring Tire Tube           4.99        0.0162        0.0243
Road-150 Red, 62            3578.27     0.9919        1.0

PERCENT_RANK() retourne une valeur comprise entre 0 et 1 indiquant le pourcentage de lignes ayant des valeurs inférieures (0 signifie le plus bas, un signifie le plus élevé). CUME_DIST() affiche la distribution cumulative, indiquant le pourcentage de lignes dont les valeurs sont inférieures ou égales à la ligne actuelle. Utilisez ces fonctions pour l’analyse centile, l’identification des valeurs hors norme ou la création de rapports de distribution.

Pour plus d’informations sur les fonctions de fenêtre, consultez Fonctions de fenêtre (Transact-SQL) et Fonctions de classement.

Commentaires

Cette page a-t-elle été utile ?

Appliquer des fonctions de fenêtre pour l’analytique

Comprendre la syntaxe de la fonction de fenêtre

Utiliser des fonctions de classement

Appliquer des fonctions de fenêtre d’agrégation

Définir des cadres de fenêtre avec ROWS et RANGE

Utiliser des fonctions analytiques

Commentaires

Définir des cadres de fenêtre avec `ROWS` et `RANGE`