Funciones de ventana

Las funciones de ventana en SQL Server se utilizan para realizar cálculos en un conjunto de filas relacionadas con una fila específica en un resultado de consulta. Las funciones de ventana se utilizan comúnmente para calcular valores acumulativos, promedios móviles, clasificaciones y otras operaciones analíticas en un conjunto de datos.

En Microsoft SQL Server, las funciones de ventana se pueden utilizar con la cláusula OVER para especificar la partición y el orden de las filas en las que se realizarán los cálculos. Las funciones de ventana más comunes son ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, SUM, AVG, MIN y MAX.

Las siguientes tablas se utilizarán para facilitar la comprensión de las funciones de ventana:

Tabla Orders

OrderID	OrderDate	EmployeeID
10248	1996-07-04	1
10249	1996-07-05	2
10250	1996-07-08	3
10251	1996-07-08	3

Funciones de ventana

ROW_NUMBER

La función de ventana ROW_NUMBER se utiliza para asignar un número secuencial único a cada fila en un conjunto de resultados de consulta. La función ROW_NUMBER se puede utilizar para generar un número de fila único para cada fila en un conjunto de datos.

La sintaxis básica de la función ROW_NUMBER es la siguiente:

SELECT ROW_NUMBER() OVER (ORDER BY column_name)
FROM table_name;

Por ejemplo, para asignar un número de fila único a cada pedido en la tabla Orders, se puede utilizar la siguiente consulta:

SELECT OrderID, ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber
FROM Orders;

En esta consulta, estamos asignando un número de fila único a cada pedido en la tabla Orders utilizando la columna OrderID.

Donde tenemos como resultado lo siguiente:

OrderID	RowNumber
10248	1
10249	2
10250	3
10251	4

RANK

La función de ventana RANK se utiliza para asignar un rango a cada fila en un conjunto de resultados de consulta. La función RANK se puede utilizar para asignar un rango a cada fila en un conjunto de datos, donde las filas con valores iguales obtienen el mismo rango y las filas con valores diferentes obtienen rangos consecutivos.

La sintaxis básica de la función RANK es la siguiente:

SELECT RANK() OVER (ORDER BY column_name)
FROM table_name;

Por ejemplo, para asignar un rango a cada pedido en la tabla Orders basado en la fecha del pedido, se puede utilizar la siguiente consulta:

SELECT OrderID, RANK() OVER (ORDER BY OrderDate) AS OrderRank
FROM Orders;

En esta consulta, estamos asignando un rango a cada pedido en la tabla Orders basado en la fecha del pedido.

Donde tenemos como resultado lo siguiente:

OrderID	OrderRank
10248	1
10249	2
10250	3
10251	3

DENSE_RANK

La función de ventana DENSE_RANK se utiliza para asignar un rango denso a cada fila en un conjunto de resultados de consulta. La función DENSE_RANK se puede utilizar para asignar un rango denso a cada fila en un conjunto de datos, donde las filas con valores iguales obtienen el mismo rango y las filas con valores diferentes obtienen rangos consecutivos sin huecos.

La sintaxis básica de la función DENSE_RANK es la siguiente:

SELECT DENSE_RANK() OVER (ORDER BY column_name)
FROM table_name;

Por ejemplo, para asignar un rango denso a cada pedido en la tabla Orders basado en la fecha del pedido, se puede utilizar la siguiente consulta:

SELECT OrderID, DENSE_RANK() OVER (ORDER BY OrderDate) AS DenseOrderRank
FROM Orders;

En esta consulta, estamos asignando un rango denso a cada pedido en la tabla Orders basado en la fecha del pedido.

Donde tenemos como resultado lo siguiente:

OrderID	DenseOrderRank
10248	1
10249	2
10250	3
10251	3

NTILE

La función de ventana NTILE se utiliza para dividir un conjunto de datos en un número específico de grupos o particiones. La función NTILE se puede utilizar para dividir un conjunto de datos en un número específico de grupos, donde cada grupo contiene un número aproximadamente igual de filas.

La sintaxis básica de la función NTILE es la siguiente:

SELECT NTILE(n) OVER (ORDER BY column_name)
FROM table_name;

Donde n es el número de grupos en los que se dividirá el conjunto de datos.

Por ejemplo, para dividir los pedidos en la tabla Orders en 2 grupos basados en la fecha del pedido, se puede utilizar la siguiente consulta:

SELECT OrderID, NTILE(2) OVER (ORDER BY OrderDate) AS OrderGroup
FROM Orders;

En esta consulta, estamos dividiendo los pedidos en la tabla Orders en 2 grupos basados en la fecha del pedido.

Donde tenemos como resultado lo siguiente:

OrderID	OrderGroup
10248	1
10249	1
10250	2
10251	2

LAG

La función de ventana LAG se utiliza para acceder a una fila anterior en un conjunto de resultados de consulta. La función LAG se puede utilizar para acceder a una fila anterior en un conjunto de datos, donde se puede especificar el número de filas anteriores que se deben acceder.

La sintaxis básica de la función LAG es la siguiente:

SELECT LAG(column_name, offset, default_value) OVER (ORDER BY column_name)
FROM table_name;

Donde offset es el número de filas anteriores que se deben acceder y default_value es el valor que se devolverá si no hay una fila anterior disponible.

Por ejemplo, para acceder al OrderID anterior en la tabla Orders, se puede utilizar la siguiente consulta:

SELECT OrderID, LAG(OrderID, 1, 0) OVER (ORDER BY OrderID) AS PreviousOrderID
FROM Orders;

En esta consulta, estamos accediendo al OrderID anterior en la tabla Orders.

Donde tenemos como resultado lo siguiente:

OrderID	PreviousOrderID
10248	0
10249	10248
10250	10249

LEAD

La función de ventana LEAD se utiliza para acceder a una fila siguiente en un conjunto de resultados de consulta. La función LEAD se puede utilizar para acceder a una fila siguiente en un conjunto de datos, donde se puede especificar el número de filas siguientes que se deben acceder.

La sintaxis básica de la función LEAD es la siguiente:

SELECT LEAD(column_name, offset, default_value) OVER (ORDER BY column_name)
FROM table_name;

Por ejemplo, para acceder al OrderID siguiente en la tabla Orders, se puede utilizar la siguiente consulta:

SELECT OrderID, LEAD(OrderID, 1, 0) OVER (ORDER BY OrderID) AS NextOrderID
FROM Orders;

En esta consulta, estamos accediendo al OrderID siguiente en la tabla Orders.

Donde tenemos como resultado lo siguiente:

OrderID	NextOrderID
10248	10249
10249	10250
10250	10251
10251	0

SUM

La función de ventana SUM se utiliza para calcular la suma acumulativa de los valores en una columna en un conjunto de resultados de consulta. La función SUM se puede utilizar para calcular la suma acumulativa de los valores en una columna, donde se suman los valores

La sintaxis básica de la función SUM es la siguiente:

SELECT SUM(column_name) OVER (ORDER BY column_name)
FROM table_name;

Por ejemplo, para calcular la suma acumulativa de los valores de la columna EmployeeID en la tabla Orders, se puede utilizar la siguiente consulta:

SELECT OrderID, EmployeeID, SUM(EmployeeID) OVER (ORDER BY OrderID) AS CumulativeSum
FROM Orders;

En esta consulta, estamos calculando la suma acumulativa de los valores de la columna EmployeeID en la tabla Orders.

Donde tenemos como resultado lo siguiente:

OrderID	EmployeeID	CumulativeSum
10248	1	1
10249	2	3
10250	3	6
10251	3	9

AVG

La función de ventana AVG se utiliza para calcular el promedio acumulativo de los valores en una columna en un conjunto de resultados de consulta. La función AVG se puede utilizar para calcular el promedio acumulativo de los valores en una columna, donde se calcula el promedio de los valores acumulados.

La sintaxis básica de la función AVG es la siguiente:

SELECT AVG(column_name) OVER (ORDER BY column_name)
FROM table_name;

Por ejemplo, para calcular el promedio acumulativo de los valores de la columna EmployeeID en la tabla Orders, se puede utilizar la siguiente consulta:

SELECT OrderID, EmployeeID, AVG(EmployeeID) OVER (ORDER BY OrderID) AS CumulativeAverage
FROM Orders;

En esta consulta, estamos calculando el promedio acumulativo de los valores de la columna EmployeeID en la tabla Orders.

Donde tenemos como resultado lo siguiente:

OrderID	EmployeeID	CumulativeAverage
10248	1	1
10249	2	1.5
10250	3	2
10251	3	2.25

MIN

La función de ventana MIN se utiliza para calcular el valor mínimo acumulativo en una columna en un conjunto de resultados de consulta. La función MIN se puede utilizar para calcular el valor mínimo acumulativo en una columna, donde se obtiene el valor mínimo acumulado.

La sintaxis básica de la función MIN es la siguiente:

SELECT MIN(column_name) OVER (ORDER BY column_name)
FROM table_name;

Por ejemplo, para calcular el valor mínimo acumulativo de los valores de la columna EmployeeID en la tabla Orders, se puede utilizar la siguiente consulta:

SELECT OrderID, EmployeeID, MIN(EmployeeID) OVER (ORDER BY OrderID) AS CumulativeMin
FROM Orders;

En esta consulta, estamos calculando el valor mínimo acumulativo de los valores de la columna EmployeeID en la tabla Orders.

Donde tenemos como resultado lo siguiente:

OrderID	EmployeeID	CumulativeMin
10248	1	1
10249	2	1
10250	3	1
10251	3	1

MAX

La función de ventana MAX se utiliza para calcular el valor máximo acumulativo en una columna en un conjunto de resultados de consulta. La función MAX se puede utilizar para calcular el valor máximo acumulativo en una columna, donde se obtiene el valor máximo acumulado.

La sintaxis básica de la función MAX es la siguiente:

SELECT MAX(column_name) OVER (ORDER BY column_name)
FROM table_name;

Por ejemplo, para calcular el valor máximo acumulativo de los valores de la columna EmployeeID en la tabla Orders, se puede utilizar la siguiente consulta:

SELECT OrderID, EmployeeID, MAX(EmployeeID) OVER (ORDER BY OrderID) AS CumulativeMax
FROM Orders;

En esta consulta, estamos calculando el valor máximo acumulativo de los valores de la columna EmployeeID en la tabla Orders.

Donde tenemos como resultado lo siguiente:

OrderID	EmployeeID	CumulativeMax
10248	1	1
10249	2	2
10250	3	3
10251	3	3

Referencias

• [ROW_NUMBER (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact