Cláusula SELECT - WINDOW (Transact-SQL)

Artigo
07/27/2024

Aplica-se a: SQL Server 2022 (16.x) Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

A definição de janela nomeada na WINDOW cláusula determina o particionamento e a ordenação de um conjunto de linhas antes da função de janela, que usa a janela em uma OVER cláusula.

A WINDOW cláusula requer o nível 160 de compatibilidade do banco de dados ou superior. Se o nível de compatibilidade do banco de dados for inferior a 160, o Mecanismo de Banco de Dados não poderá executar consultas com a WINDOW cláusula.

Você pode verificar o nível de compatibilidade na sys.databases exibição 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 de sintaxe de 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 é usado pelas funções de janela na OVER cláusula para se referir à 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 WINDOW cláusula.

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, consulte a cláusula OVER

Comentários

Mais de uma janela nomeada pode ser definida na WINDOW cláusula.

Mais componentes podem ser adicionados a uma janela nomeada na OVER cláusula usando o window_name seguido pelas especificações extras. No entanto, as propriedades especificadas na WINDOW cláusula não podem ser redefinidas na OVER cláusula.

Quando uma consulta usa várias janelas, uma janela nomeada pode fazer referência a outra janela nomeada usando o window_name. Nesse caso, o window_name referenciado deve ser especificado na definição da janela de referência. Um componente de janela definido em uma janela não pode ser redefinido por outra janela que faça referência a ele.

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 expressão de janela da qual ela faz parte, como reference_window_name, independentemente da ordem em que são definidas. Referências cíclicas e o uso de várias referências de janela em uma única janela não são permitidos.

O escopo da nova window_name de uma janela definida contida em uma expressão de janela consiste em todas as definições de janela que fazem parte da expressão de janela, juntamente com a SELECT cláusula da especificação de consulta ou SELECT instrução que contém a cláusula de janela. Se a expressão de janela estiver contida em uma especificação de consulta que faz parte da expressão de consulta, que é uma consulta de tabela básica, o escopo do novo window_name também incluirá a ORDER BY expressão, se houver, dessa expressão de consulta.

As restrições para o OVER uso de especificações de janela na cláusula com as funções agregadas e analíticas com base em sua semântica são aplicáveis à WINDOW cláusula.

Exemplos

Os exemplos de código do Transact-SQL deste artigo usa o banco de dados de exemplo AdventureWorks2022 ou AdventureWorksDW2022, que pode ser baixado da home page Microsoft SQL Server Samples and Community Projects.

R. Especificar uma janela definida na cláusula window

A consulta de exemplo a seguir mostra que usa uma janela nomeada na OVER cláusula.

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 é o equivalente à consulta anterior sem usar a WINDOW cláusula.

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 a definição de uma especificação de janela e o uso dela várias vezes em uma OVER cláusula.

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 é o equivalente à consulta anterior sem usar a WINDOW cláusula.

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 especificação comum na cláusula de janela

Este exemplo mostra a definição de uma especificação comum em uma janela e o uso dela para definir especificações adicionais na OVER cláusula.

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 é o equivalente à consulta anterior sem usar a WINDOW cláusula.

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	Qtd	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 WINDOW uso de janelas nomeadas como referências para frente e para trás ao definir uma nova janela na cláusula.

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 é o equivalente à consulta anterior sem usar a WINDOW cláusula.

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	Qtd	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

Partilhar via

Cláusula SELECT - WINDOW (Transact-SQL)

Sintaxe

Argumentos

window_name

reference_window_name

Comentários

Exemplos

R. Especificar uma janela definida na cláusula window

B. Especificar uma única janela em várias cláusulas OVER

C. Definir especificação comum na cláusula de janela

D. Referências de avanço e regressão de janela

Comentários

Comentários

Recursos adicionais

Partilhar via

Cláusula SELECT - WINDOW (Transact-SQL)

Sintaxe

Argumentos

window_name

reference_window_name

Comentários

Exemplos

R. Especificar uma janela definida na cláusula window

B. Especificar uma única janela em várias cláusulas OVER

C. Definir especificação comum na cláusula de janela

D. Referências de avanço e regressão de janela

Conteúdo relacionado

Comentários

Comentários

Recursos adicionais