SELECT - CLAUSE WINDOW (Transact-SQL)

S’applique à : SQL Server 2022 (16.x)base de données Azure SQL Azure SQL Managed Instance

La définition de fenêtre nommée dans la WINDOW clause détermine le partitionnement et l’ordre d’un ensemble de lignes avant la fonction de fenêtre, qui utilise la fenêtre dans une OVER clause.

La WINDOW clause nécessite un niveau 160 de compatibilité de base de données ou supérieur. Si votre niveau de compatibilité de base de données est inférieur 160à , le Moteur de base de données ne peut pas exécuter de requêtes avec la WINDOW clause.

Vous pouvez vérifier le niveau de compatibilité dans la vue ou dans les sys.databases propriétés de base de données. Vous pouvez changer le niveau de compatibilité d’une base de données à l’aide de la commande suivante :

ALTER DATABASE DatabaseName
SET COMPATIBILITY_LEVEL = 160;

Conventions de la syntaxe Transact-SQL

Syntaxe

WINDOW window_name AS (
       [ reference_window_name ]
       [ <PARTITION BY clause> ]
       [ <ORDER BY clause> ]
       [ <ROW or RANGE clause> ]
      )

<PARTITION BY clause> ::=
PARTITION BY value_expression , ... [ n ]

<ORDER BY clause> ::=
ORDER BY order_by_expression
    [ COLLATE collation_name ]
    [ ASC | DESC ]
    [ , ...n ]

<ROW or RANGE clause> ::=
{ ROWS | RANGE } <window frame extent>

Arguments

window_name

Nom de la spécification de fenêtre définie. Ce nom est utilisé par les fonctions de fenêtre dans la OVER clause pour faire référence à la spécification de la fenêtre. Les noms de fenêtre doivent suivre les règles applicables aux identificateurs.

reference_window_name

Nom de la fenêtre référencée par la fenêtre active. La fenêtre référencée doit figurer parmi les fenêtres définies dans la WINDOW clause.

Les autres arguments sont :

• PARTITION BY qui divise le jeu de résultats de la requête en partitions.

• ORDER BY qui définit l’ordre logique des lignes dans chaque partition du jeu de résultats.

• ROWS/RANGE qui limite davantage les lignes dans la partition en spécifiant les points de départ et de terminaison dans la partition.

Pour plus d’informations sur les arguments, consultez la clause OVER

Notes

Plusieurs fenêtres nommées peuvent être définies dans la WINDOW clause.

D’autres composants peuvent être ajoutés à une fenêtre nommée dans la OVER clause à l’aide de la window_name suivie des spécifications supplémentaires. Toutefois, les propriétés spécifiées dans WINDOW la clause ne peuvent pas être redéfinies dans la OVER clause.

Lorsqu’une requête utilise plusieurs fenêtres, une fenêtre nommée peut référencer une autre fenêtre nommée à l’aide de la window_name. Dans ce cas, la window_name référencée doit être spécifiée dans la définition de fenêtre de la fenêtre de référencement. Un composant de fenêtre défini dans une fenêtre ne peut pas être redéfini par une autre fenêtre qui le référence.

En fonction de l’ordre dans lequel les fenêtres sont définies dans la clause WINDOW, les références de fenêtre avant et arrière sont autorisées. En d’autres termes, une fenêtre peut utiliser n’importe quelle autre fenêtre définie dans l’expression de fenêtre dont elle fait partie, comme reference_window_name, quel que soit l’ordre dans lequel elles sont définies. Les références cycliques et l’utilisation de plusieurs références de fenêtre dans une seule fenêtre ne sont pas autorisées.

L’étendue de la nouvelle window_name d’une fenêtre définie contenue dans une expression de fenêtre se compose de toutes les définitions de fenêtre qui font partie de l’expression de fenêtre, ainsi que la SELECT clause de la spécification ou SELECT de l’instruction de requête qui contient la clause de fenêtre. Si l’expression de fenêtre est contenue dans une spécification de requête qui fait partie de l’expression de requête, qui est une requête de table de base, l’étendue du nouveau window_name inclut également l’expression ORDER BY , le cas échéant, de cette expression de requête.

Les restrictions relatives à l’utilisation des spécifications de fenêtre dans la OVER clause avec les fonctions d’agrégation et d’analyse en fonction de leur sémantique s’appliquent à WINDOW la clause.

Exemples

Les exemples de code Transact-SQL de cet article sont fondés sur l’échantillon de base de données AdventureWorks2022 ou AdventureWorksDW2022 fourni, que vous pouvez télécharger à partir de la page d’accueil Échantillons et projets communautaires Microsoft SQL Server.

R : Spécifier une fenêtre définie dans la clause de fenêtre

L’exemple de requête suivant montre comment utiliser une fenêtre nommée dans la OVER clause.

ALTER DATABASE AdventureWorks2022
SET COMPATIBILITY_LEVEL = 160;
GO

USE AdventureWorks2022;
GO

SELECT ROW_NUMBER() OVER win AS [Row Number],
    p.LastName,
    s.SalesYTD,
    a.PostalCode
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
    ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
    ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0
WINDOW win AS
    (
        PARTITION BY PostalCode ORDER BY SalesYTD DESC
    )
ORDER BY PostalCode;
GO

La requête suivante est l’équivalent de la requête précédente sans utiliser la WINDOW clause.

USE AdventureWorks2022;
GO

SELECT ROW_NUMBER() OVER (
        PARTITION BY PostalCode ORDER BY SalesYTD DESC
        ) AS [Row Number],
    p.LastName,
    s.SalesYTD,
    a.PostalCode
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
    ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
    ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0
ORDER BY PostalCode;
GO

Voici le jeu de résultats.

B. Spécifier une seule fenêtre dans plusieurs clauses OVER

L’exemple suivant montre comment définir une spécification de fenêtre et l’utiliser plusieurs fois dans une OVER clause.

ALTER DATABASE AdventureWorks2022
SET COMPATIBILITY_LEVEL = 160;
GO

USE AdventureWorks2022;
GO

SELECT SalesOrderID,
    ProductID,
    OrderQty,
    SUM(OrderQty) OVER win AS [Total],
    AVG(OrderQty) OVER win AS [Avg],
    COUNT(OrderQty) OVER win AS [Count],
    MIN(OrderQty) OVER win AS [Min],
    MAX(OrderQty) OVER win AS [Max]
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43659, 43664)
WINDOW win AS (PARTITION BY SalesOrderID);
GO

La requête suivante est l’équivalent de la requête précédente sans utiliser la WINDOW clause.

USE AdventureWorks2022;
GO

SELECT SalesOrderID,
    ProductID,
    OrderQty,
    SUM(OrderQty) OVER (PARTITION BY SalesOrderID) AS [Total],
    AVG(OrderQty) OVER (PARTITION BY SalesOrderID) AS [Avg],
    COUNT(OrderQty) OVER (PARTITION BY SalesOrderID) AS [Count],
    MIN(OrderQty) OVER (PARTITION BY SalesOrderID) AS [Min],
    MAX(OrderQty) OVER (PARTITION BY SalesOrderID) AS [Max]
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43659, 43664);
GO

Voici le jeu de résultats.

C. Définir une spécification commune dans la clause de fenêtre

Cet exemple montre comment définir une spécification commune dans une fenêtre et l’utiliser pour définir des spécifications supplémentaires dans la OVER clause.

ALTER DATABASE AdventureWorks2022
SET COMPATIBILITY_LEVEL = 160;
GO

USE AdventureWorks2022;
GO

SELECT SalesOrderID AS OrderNumber,
    ProductID,
    OrderQty AS Qty,
    SUM(OrderQty) OVER win AS Total,
    AVG(OrderQty) OVER (win PARTITION BY SalesOrderID) AS Avg,
    COUNT(OrderQty) OVER (
        win ROWS BETWEEN UNBOUNDED PRECEDING
            AND 1 FOLLOWING
        ) AS Count
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43659, 43664)
    AND ProductID LIKE '71%'
WINDOW win AS
    (
        ORDER BY SalesOrderID, ProductID
    );
GO

La requête suivante est l’équivalent de la requête précédente sans utiliser la WINDOW clause.

USE AdventureWorks2022;
GO

SELECT SalesOrderID AS OrderNumber,
    ProductID,
    OrderQty AS Qty,
    SUM(OrderQty) OVER (ORDER BY SalesOrderID, ProductID) AS Total,
    AVG(OrderQty) OVER (
        PARTITION BY SalesOrderID ORDER BY SalesOrderID, ProductID
        ) AS Avg,
    COUNT(OrderQty) OVER (
        ORDER BY SalesOrderID,
            ProductID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING
        ) AS Count
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43659, 43664)
    AND ProductID LIKE '71%';
GO

Voici le jeu de résultats.

D. Références de fenêtre avant et arrière

Cet exemple montre l’utilisation de fenêtres nommées comme références avant et arrière lors de la définition d’une nouvelle fenêtre dans la WINDOW clause.

ALTER DATABASE AdventureWorks2022
SET COMPATIBILITY_LEVEL = 160;
GO

USE AdventureWorks2022;
GO

SELECT SalesOrderID AS OrderNumber, ProductID,
    OrderQty AS Qty,
    SUM(OrderQty) OVER win2 AS Total,
    AVG(OrderQty) OVER win1 AS Avg
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664) AND
    ProductID LIKE '71%'
WINDOW win1 AS (win3),
    win2 AS (ORDER BY SalesOrderID, ProductID),
    win3 AS (win2 PARTITION BY SalesOrderID);
GO

La requête suivante est l’équivalent de la requête précédente sans utiliser la WINDOW clause.

USE AdventureWorks2022;
GO

SELECT SalesOrderID AS OrderNumber, ProductID,
    OrderQty AS Qty,
    SUM(OrderQty) OVER (ORDER BY SalesOrderID, ProductID) AS Total,
    AVG(OrderQty) OVER (PARTITION BY SalesOrderID ORDER BY SalesOrderID, ProductID) AS Avg
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664) AND
    ProductID LIKE '71%';
GO

Voici le jeu de résultats.

Contenu connexe

• Fonctions d’agrégation (Transact-SQL)
• Fonctions analytiques (Transact-SQL)
• SELECT - CLAUSE OVER (Transact-SQL)
• SELECT (Transact-SQL)
• CLAUSE SELECT (Transact-SQL)