SELECT - WINDOW - (Transact-SQL)

  • Artigo

Aplica-se a: SQL Server 2022 (16.x) Banco de Dados SQL do AzureInstâ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