SELECT - WINDOW - (Transact-SQL)

项目
05/23/2023

适用于： SQL Server 2022 (16.x) Azure SQL 数据库 Azure SQL 托管实例

在应用 OVER 子句中使用窗口的窗口函数之前，WINDOW 子句中的命名窗口定义确定行集的分区和排序。

注意

WINDOW 子句要求数据库兼容性级别为 160 或更高。如果数据库兼容性级别低于 160，则 SQL Server 无法使用 WINDOW 子句执行查询。

可在 sys.databases 视图或数据库属性中查看兼容性级别。可以使用以下命令更改数据库的兼容级别：

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 160

Transact-SQL 语法约定 (Transact-SQL)

语法

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 子句中的命名窗口。但是，不能在 OVER 子句中重新定义 WINDOW 子句中指定的属性。

当查询使用多个窗口时，一个命名窗口可以使用 window_name 引用另一个命名窗口。在这种情况下，必须在引用窗口的窗口定义中指定引用 window_name。一个窗口中定义的窗口组件不能由另一个引用它的窗口重新定义。

根据在 window 子句中定义窗口的顺序，允许向前和向后的窗口引用。换句话说，一个窗口可使用在它所属的 <window_expression> 中定义的任何其他窗口作为 reference_window_name，而不管它们的定义顺序如何。不允许循环引用和在单个窗口中使用多个窗口引用。

<window_expression> 中包含的已定义窗口的新 window_name 的范围由属于 <window_expression> 的任何窗口定义，以及包含该 window 子句的 <query_specification> 或 <SELECT statement> 的 SELECT 子句组成。如果 <window_expression> 包含在属于简单表查询 <query_expression> 的 <query_specification> 中，则新 window_name 的范围还包括该 <query_expression> 的 <order_by_expression>（如果有）。

OVER 子句中窗口规范与基于语义的聚合函数和分析函数的使用限制适用于 WINDOW 子句。

示例

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	OrderQty	总计	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	OrderQty	总计	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

SELECT - WINDOW - (Transact-SQL)

语法

参数

window_name

reference_window_name

一般备注

示例

A. 指定在 window 子句中定义的窗口

B. 在多个 over 子句中指定单个窗口

C. 在 window 子句中定义通用规范

D. 向前和向后的窗口引用

另请参阅

反馈

反馈

其他资源