SELECT - WINDOW clause (Transact-SQL)

Άρθρο
07/26/2024

Applies to: SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance

The named window definition in the WINDOW clause determines the partitioning and ordering of a rowset before the window function, which uses the window in an OVER clause.

The WINDOW clause requires database compatibility level 160 or higher. If your database compatibility level is lower than 160, the Database Engine can't execute queries with the WINDOW clause.

You can check the compatibility level in the sys.databases view or in database properties. You can change the compatibility level of a database with the following command:

ALTER DATABASE DatabaseName
SET COMPATIBILITY_LEVEL = 160;

Transact-SQL syntax conventions

Syntax

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

Name of the defined window specification. This name is used by the window functions in the OVER clause to refer to the window specification. Window names must follow the rules for identifiers.

reference_window_name

Name of the window being referenced by the current window. The referenced window must be among the windows defined in the WINDOW clause.

The other arguments are:

PARTITION BY that divides the query result set into partitions.
ORDER BY that defines the logical order of the rows within each partition of the result set.
ROWS/RANGE that limits the rows within the partition by specifying start and end points within the partition.

For more specific details about the arguments, see the OVER clause

Remarks

More than one named window can be defined in the WINDOW clause.

More components can be added to a named window in the OVER clause by using the window_name followed by the extra specifications. However, the properties specified in WINDOW clause can't be redefined in the OVER clause.

When a query uses multiple windows, one named window can reference another named window using the window_name. In this case, the referenced window_name must be specified in the window definition of the referencing window. A window component defined in one window can't be redefined by another window referencing it.

Based on the order in which the windows are defined in the window clause, forward and backward window references are permitted. In other words, a window might use any other window defined in the window expression that it's part of, as reference_window_name, irrespective of the order in which they're defined. Cyclic references and using multiple window references in a single window aren't allowed.

The scope of the new window_name of a defined window contained in a window expression, consists of any window definitions that are part of the window expression, together with the SELECT clause of the query specification or SELECT statement that contains the window clause. If the window expression is contained in a query specification that is part of query expression, which is a basic table query, then the scope of the new window_name also includes the ORDER BY expression, if any, of that query expression.

The restrictions for usage of window specifications in the OVER clause with the aggregate and analytic functions based on their semantics are applicable to WINDOW clause.

Examples

The Transact-SQL code samples in this article use the AdventureWorks2022 or AdventureWorksDW2022 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.

A. Specify a window defined in the window clause

The following example query shows uses a named window in the OVER clause.

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

The following query is the equivalent of the previous query without using the WINDOW clause.

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

Here's the result set.

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. Specify a single window in multiple OVER clauses

The following example shows defining a window specification and using it multiple times in an OVER clause.

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

The following query is the equivalent of the previous query without using the WINDOW clause.

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

Here's the result set.

SalesOrderID	ProductID	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. Define common specification in window clause

This example shows defining a common specification in a window and using it to define additional specifications in the OVER clause.

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

The following query is the equivalent of the previous query without using the WINDOW clause.

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

Here's the result set.

OrderNumber	ProductID	Qty	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. Forward and backward window references

This example shows using named windows as forward and backward references when defining a new window in the WINDOW clause.

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

The following query is the equivalent of the previous query without using the WINDOW clause.

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

Here's the result set.

OrderNumber	ProductID	Qty	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

Κοινή χρήση μέσω

SELECT - WINDOW clause (Transact-SQL)

Syntax

Arguments

window_name

reference_window_name

Remarks

Examples

A. Specify a window defined in the window clause

B. Specify a single window in multiple OVER clauses

C. Define common specification in window clause

D. Forward and backward window references

Σχόλια

Πρόσθετοι πόροι

Κοινή χρήση μέσω

SELECT - WINDOW clause (Transact-SQL)

Syntax

Arguments

window_name

reference_window_name

Remarks

Examples

A. Specify a window defined in the window clause

B. Specify a single window in multiple OVER clauses

C. Define common specification in window clause

D. Forward and backward window references

Related content

Σχόλια

Πρόσθετοι πόροι