SELECT : cláusula WINDOW (Transact-SQL)

Se aplica a: SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance

La definición de ventana con nombre de la WINDOW cláusula determina la creación de particiones y el orden de un conjunto de filas antes de la función de ventana, que usa la ventana en una OVER cláusula .

La WINDOW cláusula requiere un nivel 160 de compatibilidad de base de datos o superior. Si el nivel de compatibilidad de la base de datos es inferior a 160, el Motor de base de datos no puede ejecutar consultas con la WINDOW cláusula .

Puede comprobar el nivel de compatibilidad en la vista o en las propiedades de la sys.databases base de datos. Se puede cambiar el nivel de compatibilidad de una base de datos mediante el comando siguiente:

ALTER DATABASE DatabaseName
SET COMPATIBILITY_LEVEL = 160;

Convenciones de sintaxis de Transact-SQL

Sintaxis

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

Nombre de la especificación de ventana definida. Las funciones de ventana usan este nombre en la OVER cláusula para hacer referencia a la especificación de la ventana. Los nombres de ventana deben cumplir las reglas de los identificadores.

reference_window_name

Nombre de la ventana a la que hace referencia la ventana actual. La ventana a la que se hace referencia debe estar entre las ventanas definidas en la WINDOW cláusula .

Los otros argumentos son:

• PARTITION BY, que divide el conjunto de resultados de la consulta en particiones.

• ORDER BY, que define el orden lógico de las filas dentro de cada partición del conjunto de resultados.

• ROWS/RANGE, que limita aún más las filas de la partición especificando los puntos inicial y final.

Para obtener detalles más específicos sobre los argumentos, consulte la cláusula OVER.

Comentarios

Se puede definir más de una ventana con nombre en la WINDOW cláusula .

Se pueden agregar más componentes a una ventana con nombre en la OVER cláusula mediante el window_name seguido de las especificaciones adicionales. Sin embargo, las propiedades especificadas en WINDOW la cláusula no se pueden volver a definir en la OVER cláusula .

Cuando una consulta usa varias ventanas, una ventana con nombre puede hacer referencia a otra ventana con nombre mediante el window_name. En este caso, el window_name al que se hace referencia debe especificarse en la definición de ventana de la ventana de referencia. Un componente de ventana definido en una ventana no se puede volver a definir mediante otra ventana que haga referencia a ella.

Las referencias a una ventana anterior o posterior dependerán del orden en que las ventanas estén definidas en la cláusula WINDOW. En otras palabras, una ventana puede usar cualquier otra ventana definida en la expresión de ventana de la que forma parte, como reference_window_name, independientemente del orden en que se definan. No se permiten las referencias cíclicas ni el uso de varias referencias de ventana en una sola ventana.

El ámbito de la nueva window_name de una ventana definida contenida en una expresión de ventana, consta de cualquier definición de ventana que forme parte de la expresión de ventana, junto con la SELECT cláusula de la especificación o SELECT instrucción de consulta que contiene la cláusula window. Si la expresión de ventana está contenida en una especificación de consulta que forma parte de la expresión de consulta, que es una consulta de tabla básica, el ámbito del nuevo window_name también incluye la ORDER BY expresión, si existe, de esa expresión de consulta.

Las restricciones para el uso de especificaciones de ventana en la OVER cláusula con las funciones de agregado y análisis en función de su semántica son aplicables a la WINDOW cláusula .

Ejemplos

Los ejemplos de código de Transact-SQL de este artículo utilizan la base de datos de ejemplo AdventureWorks2022 o AdventureWorksDW2022, que se pueden descargar desde la página principal de Ejemplos y proyectos de la comunidad de Microsoft SQL Server.

A Especificar una ventana definida en la cláusula window

En la consulta de ejemplo siguiente se muestra cómo se usa una ventana con nombre en la 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

La consulta siguiente es el equivalente de la consulta anterior sin usar la 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 es el conjunto de resultados.

B. Especificar una sola ventana en varias cláusulas OVER

En el ejemplo siguiente se muestra cómo definir una especificación de ventana y usarla varias veces en una 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

La consulta siguiente es el equivalente de la consulta anterior sin usar la 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 es el conjunto de resultados.

C. Definición de la especificación común en la cláusula window

En este ejemplo se muestra cómo definir una especificación común en una ventana y usarla para definir especificaciones adicionales en la 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

La consulta siguiente es el equivalente de la consulta anterior sin usar la 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 es el conjunto de resultados.

D. Referencias a ventanas anteriores y posteriores

En este ejemplo se muestra el uso de ventanas con nombre como referencias hacia delante y hacia atrás al definir una nueva ventana en la WINDOW 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

La consulta siguiente es el equivalente de la consulta anterior sin usar la 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 es el conjunto de resultados.

Contenido relacionado

• Funciones de agregado (Transact-SQL)
• Funciones analíticas (Transact-SQL)
• SELECT : cláusula OVER (Transact-SQL)
• SELECT (Transact-SQL)
• Cláusula SELECT (Transact-SQL)