SELECT — предложение WINDOW (Transact-SQL)

Статья
07/27/2024

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

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

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

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

В зависимости от порядка, в котором окна определены в предложении WINDOW, разрешаются прямые и обратные ссылки на окно. Другими словами, окно может использовать любое другое окно, определенное в выражении окна, в котором он является частью, как reference_window_name, независимо от порядка, в котором они определены. Циклические ссылки и использование нескольких ссылок на окна в одном окне не допускаются.

Область нового window_name определенного окна, содержащегося в выражении окна, состоит из всех определений окон, которые являются частью выражения окна, вместе с SELECT предложением спецификации запроса или SELECT инструкции, содержащей предложение окна. Если выражение окна содержится в спецификации запроса, являющейся частью выражения запроса, который является базовым запросом таблицы, область нового window_name также включает ORDER BY выражение, если таковое имеется, из этого выражения запроса.

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

Примеры

Примеры кода Transact-SQL в этой статье используют AdventureWorks2022 базу данных или AdventureWorksDW2022 пример базы данных, которую можно скачать с домашней страницы примеров и проектов сообщества Microsoft SQL Server.

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

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

В следующем примере показано определение спецификации окна и его использование несколько раз в предложении 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	КоличествоЗаказа	Итог	Ср.	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	Кол-во	Итог	Ср.	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	Кол-во	Итог	Ср.
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. Указание одного окна в нескольких предложениях OVER

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

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

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

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

Поделиться через

SELECT — предложение WINDOW (Transact-SQL)

Синтаксис

Аргументы

window_name

reference_window_name

Замечания

Примеры

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

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

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

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

Связанный контент

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

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