SELECT — WINDOW — (Transact-SQL)

Статья
05/23/2023

Область применения: SQL Server 2022 (16.x) База данных SQL Azure Управляемый экземпляр SQL Azure

Определение именованного окна в предложении WINDOW определяет секционирование и упорядочение набора строк перед применением функции окна, которая использует окно в предложении OVER.

Примечание.

Для предложения WINDOW требуется уровень совместимости базы данных 160 или выше. Если уровень совместимости базы данных ниже 160, SQL Server не сможет выполнять запросы с предложением WINDOW.

Уровень совместимости можно проверка в представлении sys.database или в свойствах базы данных. Изменить уровень совместимости базы данных можно с помощью следующей команды:

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 можно определить несколько именованных окон.

Дополнительные компоненты можно добавить в именованное окно в предложении OVER с помощью window_name, после которого указываются дополнительные спецификации. Однако свойства, указанные в предложении WINDOW, нельзя переопределять в предложении OVER.

Если запрос использует несколько окон, одно именованное окно может ссылаться на другое именованное окно с помощью window_name. В этом случае window_name, на который создана ссылка, должен быть указан в определении окна, в котором существует ссылка. Компонент окна, определенный в одном окне, не может быть переопределен другим окном, которое на него ссылается.

В зависимости от порядка, в котором окна определены в предложении WINDOW, разрешаются прямые и обратные ссылки на окно. Иными словами, окно может использовать любое другое окно, определенное в <window_expression>, частью которого оно является, как reference_window_name независимо от порядка, в котором они определены. Не допускаются циклические ссылки и использование нескольких ссылок на окна в одном окне.

Область нового window_name для определенного окна, которое содержится в <window_expression>, состоит из всех определений окон, входящих в <window_expression>, вместе с предложением SELECT в <query_specification> или <SELECT statement>, которое содержит предложение WINDOW. Если <window_expression> содержится в <query_specification>, который является частью <query_expression> (простой табличный запрос), область нового window_name также включает <order_by_expression> (если есть) для этого <query_expression>.

Ограничения использования спецификаций окон в предложении OVER с агрегатными и аналитическими функциями в зависимости от их семантики могут применяться и к предложению 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

Результирующий набор:

Номер строки	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. Указание одного окна в нескольких предложениях

В следующем примере показано, как определить спецификацию окна и использовать ее несколько раз в предложении 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	Численность	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

Общие замечания

Примеры

А. Указание окна, определенного в предложении окна

B. Указание одного окна в нескольких предложениях

C. Определение общей спецификации в предложении window

D. Прямые и обратные ссылки на окно

См. также

Обратная связь

Обратная связь

Дополнительные ресурсы