SELECT - WINDOW 子句 （Transact-SQL）
適用於：
SQL Server 2022 (16.x) Azure SQL 資料庫 Azure SQL 受控執行個體
子句中的 WINDOW 具名視窗定義會決定數據列集在 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 必須在參考視窗的視窗定義中指定。 一個視窗中定義的視窗元件無法由另一個參考它的視窗重新定義。
根據時間範圍定義於時間範圍子句中的順序，會允許前向和後向時間範圍參考。 換句話說，視窗可能會使用在視窗表達式中定義的任何其他視窗，做為 reference_window_name，而不論其定義順序為何。 不允許在單一視窗中循環參考和使用多個窗口參考。
包含在視窗表達式中之已定義視窗的新 window_name 範圍，包含任何屬於窗口表示式一部分的窗口定義，以及 SELECT 包含 window 子句的查詢規格或 SELECT 語句的 子句。 如果窗口表達式包含在屬於查詢表達式的查詢規格中，這是基本數據表查詢，則新 window_name 的範圍也會包含 ORDER BY 該查詢表達式的表達式。如果有任何的話，該查詢表達式的範圍。
子句中 OVER 具有匯總和分析函式之窗口規格使用限制，其語意適用於 WINDOW 子句。
範例
本文 Transact-SQL 程式碼範例使用 AdventureWorks2022 或 AdventureWorksDW2022 範例資料庫，從 Microsoft SQL Server Samples 和 Community Projects （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
結果集如下所示。
| 資料列號碼 | 姓氏 | 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
結果集如下所示。
| SalesOrderID | ProductID | OrderQty | 總計 | 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. 在 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 | 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. 前向和後向時間範圍參考
這個範例示範在 子句中 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 |