SELECT - WINDOW - (Transact-SQL)
S’applique à : SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance
La définition de fenêtre nommée dans la clause WINDOW Détermine le partitionnement et l’ordre d’un ensemble de lignes avant que la fonction de fenêtre qui utilise la fenêtre dans la clause OVER ne soit appliquée.
Notes
La clause WINDOW nécessite le niveau de compatibilité de base de données 160 ou supérieur. Si le niveau de compatibilité de votre base de données est inférieur à 160, SQL Server ne peut pas exécuter de requêtes avec la clause WINDOW.
Vous pouvez vérifier le niveau de compatibilité dans la vue sys.databases ou dans les propriétés de la 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 (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 sera utilisé par les fonctions de fenêtre dans la clause OVER 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 clause WINDOW.
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
Remarques générales
Plusieurs fenêtres nommées peuvent être définies dans la clause WINDOW.
Des composants supplémentaires peuvent être ajoutés à une fenêtre nommée dans la clause OVER en utilisant nom_fenêtre suivi des spécifications supplémentaires. Cependant, les propriétés spécifiées dans la clause WINDOW ne peuvent pas être redéfinies dans la clause OVER.
Quand une requête utilise plusieurs fenêtres, une fenêtre nommée peut faire référence à une autre fenêtre nommée en utilisant nom_fenêtre. Dans ce cas, le nom_fenêtre référencé doit être spécifié dans la définition de fenêtre de la fenêtre qui fait la référence. Un composant de fenêtre défini dans une fenêtre ne peut pas être redéfini par une autre fenêtre qui la 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 <window_expression>
, en tant que reference_window_name
, indépendamment de 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 même fenêtre ne sont pas autorisées.
L’étendue du nouveau nom_fenêtre d’une fenêtre définie contenue dans une <window_expression>
est constituée des définitions de fenêtre qui font partie de <window_expression>
ainsi que de la clause SELECT de <query_specification>
ou de <SELECT statement>
qui contient la clause WINDOW. Si la <window_expression>
est contenue dans une <query_specification>
qui fait partie de <query_expression>
qui est une requête table simple, l’étendue du nouveau nom_fenêtre inclut également <order_by_expression>
(le cas échéant) de cette <query_expression>
.
Les restrictions d’utilisation des spécifications de fenêtre dans la clause OVER avec les fonctions d’agrégation et d’analytique basées sur leur sémantique sont applicables à la clause WINDOW.
Exemples
R. Spécification d’une fenêtre définie dans la clause WINDOW
L’exemple de requête suivant montre des utilisations d’une fenêtre nommée dans la clause OVER.
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 ci-dessus sans utilisation de la clause WINDOW.
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 obtenu.
Row Number | LastName | SalesYTD | PostalCode |
---|---|---|---|
1 | Mitchell | 4251368,5497 | 98027 |
2 | Blythe | 3763178,1787 | 98027 |
3 | Carson | 3189418,3662 | 98027 |
4 | Reiter | 2315185,611 | 98027 |
5 | Vargas | 1453719,4653 | 98027 |
6 | Ansman-Wolfe | 1352577,1325 | 98027 |
1 | Pak | 4116871,2277 | 98055 |
2 | Varkey Chudukatil | 3121616,3202 | 98055 |
3 | Saraiva | 2604540,7172 | 98055 |
4 | Ito | 2458535,6169 | 98055 |
5 | Valdez | 1827066,7118 | 98055 |
6 | Mensa-Annan | 1576562,1966 | 98055 |
7 | Campbell | 1573012,9383 | 98055 |
8 | Tsoflias | 1421810,9242 | 98055 |
B. Spécification d’une fenêtre unique dans plusieurs clauses OVER
L’exemple suivant montre comment définir une spécification de fenêtre et l’utiliser plusieurs fois dans une clause OVER.
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 ci-dessus sans utilisation de la clause WINDOW.
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 obtenu.
SalesOrderID | IDProduit | OrderQty | Total | Avg | Count | Min | Max |
---|---|---|---|---|---|---|---|
43659 | 776 | 1 | 26 | 2 | 12 | 1 | 6 |
43659 | 777 | 3 | 26 | 2 | 12 | 1 | 6 |
43659 | 778 | 1 | 26 | 2 | 12 | 1 | 6 |
43659 | 771 | 1 | 26 | 2 | 12 | 1 | 6 |
43659 | 772 | 1 | 26 | 2 | 12 | 1 | 6 |
43659 | 773 | 2 | 26 | 2 | 12 | 1 | 6 |
43659 | 774 | 1 | 26 | 2 | 12 | 1 | 6 |
43659 | 714 | 3 | 26 | 2 | 12 | 1 | 6 |
43659 | 716 | 1 | 26 | 2 | 12 | 1 | 6 |
43659 | 709 | 6 | 26 | 2 | 12 | 1 | 6 |
43659 | 712 | 2 | 26 | 2 | 12 | 1 | 6 |
43659 | 711 | 4 | 26 | 2 | 12 | 1 | 6 |
43664 | 772 | 1 | 14 | 1 | 8 | 1 | 4 |
43664 | 775 | 4 | 14 | 1 | 8 | 1 | 4 |
43664 | 714 | 1 | 14 | 1 | 8 | 1 | 4 |
43664 | 716 | 1 | 14 | 1 | 8 | 1 | 4 |
43664 | 777 | 2 | 14 | 1 | 8 | 1 | 4 |
43664 | 771 | 3 | 14 | 1 | 8 | 1 | 4 |
43664 | 773 | 1 | 14 | 1 | 8 | 1 | 4 |
43664 | 778 | 1 | 14 | 1 | 8 | 1 | 4 |
C. Définition d’une spécification commune dans la clause WINDOW
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 clause OVER.
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 ci-dessus sans utilisation de la clause WINDOW.
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 obtenu.
OrderNumber | IDProduit | OrderQty | Total | Avg | Count |
---|---|---|---|---|---|
43659 | 711 | 4 | 4 | 4 | 2 |
43659 | 712 | 2 | 6 | 3 | 3 |
43659 | 714 | 3 | 9 | 3 | 4 |
43659 | 716 | 1 | 10 | 2 | 5 |
43664 | 714 | 1 | 11 | 1 | 6 |
43664 | 716 | 1 | 12 | 1 | 6 |
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 clause WINDOW.
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 ci-dessus sans utilisation de la clause WINDOW.
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 obtenu.
OrderNumber | IDProduit | OrderQty | Total | Avg |
---|---|---|---|---|
43659 | 711 | 4 | 4 | 4 |
43659 | 712 | 2 | 6 | 3 |
43659 | 714 | 3 | 9 | 3 |
43659 | 716 | 1 | 10 | 2 |
43664 | 714 | 1 | 11 | 1 |
43664 | 716 | 1 | 12 | 1 |
Voir aussi
Commentaires
https://aka.ms/ContentUserFeedback.
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultezEnvoyer et afficher des commentaires pour