SELECT - WINDOW 子句 （Transact-SQL）
适用于：
SQL Server 2022 (16.x) Azure SQL 数据库 Azure SQL 托管实例
子句中的 WINDOW 命名窗口定义确定在窗口函数之前行集的分区和排序，该函数使用子句中的 OVER 窗口。
子 WINDOW 句需要数据库兼容性级别 160 或更高版本。 如果数据库兼容性级别低于160，则数据库引擎无法使用子句执行查询WINDOW。
可以在视图或数据库属性中 sys.databases 检查兼容性级别。 可以使用以下命令更改数据库的兼容级别：
ALTER DATABASE DatabaseName
SET COMPATIBILITY_LEVEL = 160;
语法
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>
参数
window_name
定义的窗口规范的名称。 子句中的 OVER 窗口函数使用此名称来引用窗口规范。 窗口名称必须符合标识符的规则。
reference_window_name
当前窗口引用的窗口的名称。 引用的窗口必须是子句中定义的窗口之 WINDOW 一。
其他参数包括：
PARTITION BY：将查询结果集分为多个分区。
ORDER BY：定义结果集的每个分区中行的逻辑顺序。
ROWS/RANGE：通过指定分区中的起点和终点来限制分区中的行数。
有关参数的更具体的详细信息，请参阅 OVER 子句
注解
可以在子句中 WINDOW 定义多个命名窗口。
可以使用window_name后跟额外的规范，将更多组件添加到子句中的OVER命名窗口。 但是，子句中指定的 WINDOW 属性不能在 OVER 子句中重新定义。
当查询使用多个窗口时，一个命名窗口可以使用window_name引用另一个命名窗口。 在这种情况下，必须在引用窗口的窗口定义中指定引用 window_name 。 一个窗口中定义的窗口组件不能由引用它的另一个窗口重新定义。
根据在 window 子句中定义窗口的顺序，允许向前和向后的窗口引用。 换句话说，窗口可能会使用窗口表达式中定义的任何其他窗口（作为 reference_window_name的一部分），而不管它们的定义顺序如何。 不允许在单个窗口中使用循环引用和使用多个窗口引用。
窗口表达式中包含的已定义窗口的新 window_name 的范围包括属于窗口表达式的任何窗口定义，以及 SELECT 包含 window 子句的查询规范或 SELECT 语句的子句。 如果窗口表达式包含在查询表达式的查询规范中，这是一个基本表查询，则新 window_name 的范围还包括 ORDER BY 该查询表达式的表达式（如果有）。
子句中 OVER 具有聚合和分析函数的窗口规范的使用限制适用于 WINDOW 子句。
示例
本文中的 Transact-SQL 代码示例使用 AdventureWorks2022 或 AdventureWorksDW2022 示例数据库，可从 Microsoft SQL Server 示例和社区项目主页下载它。
A. 指定在 window 子句中定义的窗口
以下示例查询显示使用子句中的 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
以下查询与上一个查询等效，而不使用 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
结果集如下。
| Row Number | 姓氏 | 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. 在多个 OVER 子句中指定单个窗口
以下示例演示如何定义窗口规范并在子句中 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
以下查询与上一个查询等效，而不使用 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
结果集如下。
| 销售订单 ID | ProductID | OrderQty | 总计 | Avg | 计数 | 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 在 window 子句中定义通用规范
此示例演示如何在窗口中定义通用规范，并使用它在子句中 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
以下查询与上一个查询等效，而不使用 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
结果集如下。
| OrderNumber | ProductID | 数量 | 总计 | Avg | 计数 |
|---|---|---|---|---|---|
| 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. 向前和向后的窗口引用
此示例演示如何在子句中 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
以下查询与上一个查询等效，而不使用 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
结果集如下。
| OrderNumber | ProductID | 数量 | 总计 | 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 |