SELECT - WINDOW - (Transact-SQL)
Se aplica a:
SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed Instance
La definición de la ventana con nombre en la cláusula WINDOW determina las particiones y el orden de un conjunto de filas antes de que se aplique la función de ventana que usa la ventana en la cláusula OVER.
Nota:
La cláusula WINDOW requiere un nivel de compatibilidad de base de datos 160 o superior. Si el nivel de compatibilidad de base de datos es inferior a 160, SQL Server no puede ejecutar consultas con la cláusula WINDOW.
Puede consultar el nivel de compatibilidad en la vista sys.databases o en las propiedades de la 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 (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 de la cláusula OVER usarán este nombre para hacer referencia a la especificación de 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 cláusula WINDOW.
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 información más detallada sobre los argumentos, consulte la cláusula OVER.
Observaciones generales
En la cláusula WINDOW se puede definir más de una ventana con nombre.
En la cláusula OVER se pueden agregar componentes adicionales a una ventana con nombre usando el valor de window_name correspondiente seguido de las especificaciones adicionales. Sin embargo, las propiedades especificadas en la cláusula WINDOW no se pueden volver a definir en la cláusula OVER.
Cuando en una consulta se usan varias ventanas, una ventana con nombre puede hacer referencia a otra usando el valor de window_name correspondiente. En este caso, el valor de 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 puede volver a definirse en otra ventana que haga referencia a él.
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 podrá usar cualquier otra ventana definida en el elemento <window_expression> del que forme parte, como reference_window_name, independientemente del orden del que se definan. No se permiten las referencias cíclicas y el uso de varias referencias de ventana en una misma ventana.
El ámbito del nuevo elemento window_name de una ventana definida contenida en un elemento <window_expression> consta de cualquier definición de ventana que forme parte de <window_expression>, junto con la cláusula SELECT de <query_specification> o de <SELECT statement> que contenga la cláusula WINDOW. Si el elemento <window_expression> está contenido en un elemento <query_specification> que forma parte de <query_expression>, que es una consulta de tabla simple, el ámbito del nuevo elemento window_name también incluirá el elemento <order_by_expression>, si existe, de ese elemento <query_expression>.
Las restricciones de uso de las especificaciones de ventana de la cláusula OVER con las funciones de agregado y análisis en función de la semántica son aplicables también a la cláusula WINDOW.
Ejemplos
A. Especificación de una ventana definida en la cláusula WINDOW
En la consulta de ejemplo siguiente se muestran cómo usar una ventana con nombre en la 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
La consulta siguiente equivale a la consulta anterior sin usar la 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
El conjunto de resultados es el siguiente:
| Row Number | Apellidos | 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. Especificación de 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 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
La consulta siguiente equivale a la consulta anterior sin usar la 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
El conjunto de resultados es el siguiente:
| Id.OrdenVentas | ProductID | OrderQty | Total | Avg | Recuento | Mín. | Máx. |
|---|---|---|---|---|---|---|---|
| 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. Definición de una 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 más especificaciones en la 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
La consulta siguiente equivale a la consulta anterior sin usar la 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
El conjunto de resultados es el siguiente:
| OrderNumber | ProductID | OrderQty | Total | Avg | Recuento |
|---|---|---|---|---|---|
| 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. Referencias a ventanas anteriores y posteriores
En este ejemplo se muestra el uso de ventanas con nombre como referencias anteriores o posteriores al definir una nueva ventana en la 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
La consulta siguiente equivale a la consulta anterior sin usar la 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 |
El conjunto de resultados es el siguiente:
| 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 |
Consulte también
Comentarios
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea: https://aka.ms/ContentUserFeedback.
Enviar y ver comentarios de