SELECT — WINDOW — (Transact-SQL)
Область применения: 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 |
См. также
Обратная связь
https://aka.ms/ContentUserFeedback.
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделеОтправить и просмотреть отзыв по