SELECT - WINDOW - (Transact-SQL)
Aplica-se a: SQL Server 2022 (16.x) Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure
Definição da janela nomeada na cláusula WINDOW que determina o particionamento e a ordenação de um conjunto de linhas antes que a função de janela que usa a janela na cláusula OVER seja aplicada.
Observação
A cláusula WINDOW requer o nível de compatibilidade do banco de dados 160 ou superior. Se o nível de compatibilidade do seu banco de dados for inferior a 160, o SQL Server não poderá executar consultas com a cláusula WINDOW.
Você pode verificar o nível de compatibilidade na exibição sys.databases ou nas propriedades do banco de dados. É possível alterar o nível de compatibilidade de um banco de dados usando o seguinte comando:
ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 160
Convenções da sintaxe Transact-SQL (Transact-SQL)
Sintaxe
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>
Argumentos
window_name
Nome da especificação da janela definida. Esse nome será usado pelas funções de janela na cláusula OVER para consultar a especificação da janela. Os nomes de janela devem seguir as regras para identificadores.
reference_window_name
Nome da janela que está sendo referenciada pela janela atual. A janela referenciada deve estar entre as janelas definidas na cláusula WINDOW.
Os outros argumentos são:
PARTITION BY divide o conjunto de resultados da consulta em partições.
ORDER BY define a ordem lógica das linhas dentro de cada partição do conjunto de resultados.
ROWS/RANGE limita as linhas dentro da partição com a especificação de pontos iniciais e finais na partição.
Para obter detalhes mais específicos sobre os argumentos, confira a Cláusula OVER
Comentários gerais
Mais de uma janela nomeada pode ser definida na cláusula WINDOW.
Componentes adicionais podem ser adicionados a uma janela nomeada na cláusula OVER usando o window_name seguido pelas especificações adicionais. No entanto, as propriedades especificadas na cláusula WINDOW não podem ser redefinidas na cláusula OVER.
Quando uma consulta usa várias janelas, uma janela nomeada pode referenciar outra janela nomeada usando o window_name. Nesse caso, o window_name referenciado deve ser especificado na definição de janela da janela de referência. Um componente de janela definido em uma janela não pode ser redefinido por outra janela referenciando-o.
Com base na ordem na qual as janelas são definidas na cláusula de janela, as referências de avanço e regressão de janela são permitidas. Em outras palavras, uma janela pode usar qualquer outra janela definida na <window_expression>
da qual ela faz parte, como reference_window_name
, independentemente da ordem na qual elas são definidas. As referências cíclicas e o uso de várias referências de janela em uma única janela não são permitidas.
O escopo do novo window_name de uma janela definida contida em um <window_expression>
consiste em quaisquer definições de janela que fazem parte do <window_expression>
, juntamente com a cláusula SELECT do <query_specification>
ou <SELECT statement>
que contém a cláusula window. Se o <window_expression>
estiver contido em uma <query_specification>
que faz parte da <query_expression>
, que é uma consulta de tabela simples, o escopo do novo window_name também incluirá o <order_by_expression>
, se houver, desse <query_expression>
.
As restrições para uso de especificações de janela na cláusula OVER com as funções analíticas e de agregação baseadas na semântica são aplicáveis à cláusula WINDOW.
Exemplos
a. Especificar uma janela definida na cláusula WINDOW
A consulta de exemplo a seguir usa uma janela nomeada na cláusula 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
A consulta a seguir é equivalente à consulta acima sem usar a cláusula 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
Este é o conjunto de resultados.
Row Number | Sobrenome | 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. Especificar uma única janela em várias cláusulas OVER
O exemplo a seguir mostra como definir uma especificação de janela e usá-la várias vezes em uma cláusula 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
A consulta a seguir é equivalente à consulta acima sem usar a cláusula 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
Este é o conjunto de resultados.
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. Definir uma especificação comum na cláusula WINDOW
Este exemplo mostra como definir uma especificação comum em uma janela e usá-la para definir especificações adicionais na cláusula 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
A consulta a seguir é equivalente à consulta acima sem usar a cláusula 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
Este é o conjunto de resultados.
OrderNumber | ProductID | OrderQty | 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. Referências de avanço e regressão de janela
Este exemplo mostra o uso de janelas nomeadas como referências de avanço e regressão ao definir uma nova janela na cláusula 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
A consulta a seguir é equivalente à consulta acima sem usar a cláusula 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 |
Este é o conjunto de resultados.
OrderNumber | ProductID | OrderQty | 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 |
Confira também
Comentários
https://aka.ms/ContentUserFeedback.
Brevemente: Ao longo de 2024, vamos descontinuar progressivamente o GitHub Issues como mecanismo de feedback para conteúdos e substituí-lo por um novo sistema de feedback. Para obter mais informações, veja:Submeter e ver comentários