SELECT - WINDOW 子句（Transact-SQL）

發行項
07/27/2024

適用於：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;

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具名視窗。不過，子句中指定的 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

共用方式為

SELECT - WINDOW 子句（Transact-SQL）

語法

引數

window_name

reference_window_name

備註

範例

A. 指定 window 子句中定義的視窗

B. 在多個 OVER 子句中指定單一視窗

C. 在 window 子句中定義通用規格

D. 前向和後向時間範圍參考

意見反應

意見反應

其他資源

共用方式為

SELECT - WINDOW 子句 （Transact-SQL）

語法

引數

window_name

reference_window_name

備註

範例

A. 指定 window 子句中定義的視窗

B. 在多個 OVER 子句中指定單一視窗

C. 在 window 子句中定義通用規格

D. 前向和後向時間範圍參考

相關內容

意見反應

意見反應

其他資源

SELECT - WINDOW 子句（Transact-SQL）