BI

Cuando analizamos datos con SQL Server, a menudo nos encontramos con la necesidad de realizar cálculos complejos que involucren conjuntos de filas relacionados entre sí. Tradicionalmente, recurríamos a subconsultas o a la ya conocida cláusula «GROUP BY». Sin embargo, existe un conjunto de herramientas mucho más potente y elegante para abordar estas situaciones: las funciones de ventana. A lo largo de este artículo, exploraremos en profundidad qué son, cómo funcionan y cómo podemos sacarles el máximo partido en nuestras consultas en SQL Server.

¿Qué son realmente las Funciones de Ventana en SQL Server? 

Las funciones de ventana son un tipo especial de funciones que nos permiten realizar cálculos sobre un conjunto de filas que están relacionadas con la fila actual que estamos procesando. No es nada nuevo, las vimos por primera vez en 1998 en Oracle8i y fueron introducidas como parte del estándar SQL en su versión 3 en el año 2003. En SQL Server las tenemos desde la versión 2005 y posteriormente llegaron a otros sistemas de bases de datos como PostgreSQL (2009), MariaDB (2016) y MySQL (2018).

La clave de su potencia reside en que, a diferencia de las funciones agregadas tradicionales que colapsan múltiples filas en una única fila de salida (como sucede con «GROUP BY»), las funciones de ventana operan dentro de una «ventana» o «marco de ventana» definido por nosotros, devolviendo un valor para cada fila individual.

Imaginemos una tabla de ventas donde queremos calcular el total de ventas acumulado por vendedor a lo largo del tiempo. Sin una función de ventana, tendríamos que recurrir a subconsultas complejas o a cursores, lo que puede resultar ineficiente y difícil de mantener. Con una función de ventana, podemos definir una ventana que incluya todas las ventas del vendedor hasta la fecha actual, calculando el acumulado para cada venta sin perder la información de cada transacción individual.

La sintaxis fundamental para utilizar una función de ventana involucra la cláusula «OVER()». Esta cláusula es la que define la «ventana» sobre la cual la función operará. Dentro de «OVER()», podemos especificar cómo se particionarán los datos y cómo se ordenarán dentro de cada partición.

Sintaxis de las Funciones de Ventana en SQL Server

La estructura básica para emplear una función de ventana es la siguiente:

SELECT
    columna1,
    columna2,
    funcion_ventana (argumento) OVER (
        [PARTITION BY lista_de_columnas]
        [ORDER BY lista_de_columnas [ASC | DESC]]
        [ROWS o RANGE especificación_de_marco]
    ) AS alias_de_columna
FROM
    tabla;

Analicemos cada uno de los componentes esenciales:

• funcion_ventana: Aquí especificamos la función de ventana que queremos aplicar. Puede ser una función de agregación (como «SUM()», «AVG()», «MIN()», «MAX()», «COUNT()»), una función de ranking («ROW_NUMBER()», «RANK()», «DENSE_RANK()», «NTILE()»), o una función de valor («LAG()», «LEAD()», «FIRST_VALUE()», «LAST_VALUE()»). El argumento dependerá de la función específica.
• OVER(): Esta cláusula es obligatoria para indicar que estamos utilizando una función de ventana. Es dentro de sus paréntesis donde definimos el contexto de la ventana.
• PARTITION BY lista_de_columnas (Opcional): La cláusula «PARTITION BY» divide el conjunto de resultados en particiones basadas en los valores de las columnas especificadas. La función de ventana se aplicará de forma independiente a cada una de estas particiones. Si omitimos «PARTITION BY», la función se aplicará a toda la tabla como una única partición.
• ORDER BY lista_de_columnas [ASC | DESC] (Opcional): Dentro de cada partición (o en toda la tabla si no hay «PARTITION BY»), la cláusula «ORDER BY» define el orden lógico de las filas. Este orden es crucial para muchas funciones de ventana, especialmente las de ranking y las que trabajan con valores de filas precedentes o siguientes. Si se omite, el orden de las filas dentro de la partición será arbitrario.
• ROWS o RANGE especificación_de_marco (Opcional): Esta cláusula nos permite definir aún más el marco de la ventana dentro de cada partición. Podemos especificar un conjunto de filas contiguas que se incluirán en el cálculo de la función para la fila actual. Las opciones más comunes incluyen:
  •  «ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW»: Incluye todas las filas desde el inicio de la partición hasta la fila actual.
  • «ROWS BETWEEN n PRECEDING AND CURRENT ROW»: Incluye las «n» filas anteriores a la fila actual y la fila actual.
  • “ROWS BETWEEN CURRENT ROW AND n FOLLOWING»: Incluye la fila actual y las «n» filas siguientes.
  • “ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING»: Incluye todas las filas de la partición.
  • «RANGE» funciona de manera similar a «ROWS», pero en lugar de un número fijo de filas, define el marco basándose en los valores de las columnas especificadas en «ORDER BY».

Tipos de Funciones de Ventana en SQL Server

Las funciones de ventana se pueden clasificar en varios tipos, cada uno diseñado para abordar necesidades específicas de análisis:

Funciones de Agregación

Podemos utilizar las funciones de agregación que ya conocemos (SUM(),AVG(), MIN(), MAX() o COUNT()) como funciones de ventana al incluirlas con la cláusula «OVER()». La diferencia fundamental con su uso tradicional con «GROUP BY» es que, al emplearlas como funciones de ventana, no perdemos la granularidad de las filas individuales.

Por ejemplo, para obtener el total de ventas por ciudad y a la vez visualizar el importe de cada pedido individual junto con el total de su ciudad, podríamos escribir algo como:

SELECT
    order_id,
    order_date,
    customer_name,
    city,
    order_amount,
    SUM(order_amount) OVER (PARTITION BY city) AS total_ventas_ciudad
FROM
    Orders;

Aquí, «SUM(order_amount) OVER (PARTITION BY city)» calcula la suma de «order_amount» para todas las filas que comparten el mismo valor en la columna «city», y este total se muestra en cada fila correspondiente a esa ciudad.

Funciones de Ranking 

Las funciones de ranking nos permiten asignar una posición o rango a cada fila dentro de una partición según un criterio de ordenación específico. SQL Server nos ofrece las siguientes funciones de ranking:

• ROW_NUMBER(): Asigna un número secuencial único a cada fila dentro de una partición, comenzando en 1. Si hay filas con los mismos valores en la columna de ordenación, se les asignarán números diferentes según el orden en que se procesen.
• RANK(): Asigna un rango a cada fila dentro de una partición basado en el orden de las columnas especificadas en «ORDER BY». Si dos o más filas tienen el mismo valor, recibirán el mismo rango, y el siguiente rango se saltará. Por ejemplo: 1, 2, 2, 4…
• DENSE_RANK(): Similar a «RANK()», asigna rangos basados en el orden, pero no se salta ningún rango en caso de empate. Por ejemplo: 1, 2, 2, 3, 4…
• NTILE(n): Divide las filas dentro de una partición en «n» grupos (aproximadamente) iguales y asigna un número de grupo (desde 1 hasta «n») a cada fila. Es útil para identificar percentiles, cuartiles, etc..

Funciones de Valor 

Las funciones de valor nos permiten acceder a valores de otras filas dentro de la misma partición (o en toda la tabla) sin necesidad de realizar joins o subconsultas. Las más utilizadas son:

• LAG(columna, n, valor_predeterminado): Accede al valor de la «columna» en la fila que está «n» filas antes de la fila actual dentro de la partición (ordenada por «ORDER BY»). Si no existe una fila anterior en la distancia especificada, devuelve el «valor_predeterminado» (si se proporciona, sino devuelve «NULL»).
• LEAD(columna, n, valor_predeterminado): Accede al valor de la «columna» en la fila que está «n» filas después de la fila actual dentro de la partición (ordenada por «ORDER BY»). Similar a «LAG()», permite especificar un «valor_predeterminado» si no existe una fila posterior.
• FIRST_VALUE(columna): Devuelve el valor de la «columna» de la primera fila dentro de la partición (ordenada por «ORDER BY»).
• LAST_VALUE(columna): Devuelve el valor de la «columna» de la última fila dentro de la partición (ordenada por «ORDER BY»). Es importante tener en cuenta que, por defecto, el marco de la ventana para «LAST_VALUE()» va desde el inicio de la partición hasta la fila actual, por lo que a menudo se utiliza con una especificación de marco como «ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING» para obtener el verdadero último valor de la partición.

Cláusulas avanzadas en Funciones de Ventana

Como mencionaba anteriormente, las cláusulas «ROWS» y «RANGE» nos permiten refinar la definición del marco de la ventana. «ROWS» define el marco en términos de un número fijo de filas precedentes o siguientes a la fila actual. «RANGE», por otro lado, define el marco basado en los valores de la columna de ordenación.

Por ejemplo, para calcular una media móvil de ventas de los últimos tres meses (incluyendo el mes actual), podríamos utilizar:

SELECT
    mes,
    ventas,
    AVG(ventas) OVER (ORDER BY mes ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS media_movil_3_meses
FROM
    VentasMensuales;

Aquí, «ROWS BETWEEN 2 PRECEDING AND CURRENT ROW» define una ventana que incluye la fila actual y las dos filas anteriores según el orden de la columna «mes».

El poder de «PARTITION BY» y «ORDER BY» juntos en Funciones de Ventana 

La combinación de «PARTITION BY» y «ORDER BY» dentro de la cláusula «OVER()» es donde realmente brilla el potencial de las funciones de ventana. «PARTITION BY» nos permite dividir los datos en grupos lógicos, mientras que «ORDER BY» establece un orden significativo dentro de cada uno de estos grupos.

Consideremos el ejemplo de calcular el ranking de productos más vendidos dentro de cada categoría:

SELECT
    categoria,
    producto,
    ventas,
    RANK() OVER (PARTITION BY categoria ORDER BY ventas DESC) AS ranking_ventas
FROM
    VentasProductos;

En este caso, los productos se particionan por «categoria», y dentro de cada categoría, se ordenan por «ventas» de forma descendente. La función «RANK()» asignará un ranking a cada producto dentro de su respectiva categoría.

Usando la Cláusula «WINDOW» para simplificar consultas complejas en SQL Server

A partir de SQL Server 2022 (con un nivel de compatibilidad de base de datos 160 o superior), se introduce la cláusula «WINDOW». Esta cláusula nos permite definir especificaciones de ventana con nombre que pueden ser referenciadas por múltiples funciones de ventana dentro de una misma consulta. Esto mejora significativamente la legibilidad y el mantenimiento de consultas complejas que utilizan las mismas definiciones de ventana varias veces. La sintaxis básica de la cláusula «WINDOW» es:

WINDOW nombre_ventana AS (
    [PARTITION BY lista_de_columnas]
    [ORDER BY lista_de_columnas [ASC | DESC]]
    [ROWS o RANGE especificación_de_marco]
);

Una vez definida la ventana con nombre, podemos referenciarla en la cláusula «OVER()» de nuestras funciones de ventana:

SELECT
    ...,
    SUM(ventas) OVER nombre_ventana AS total_ventas,
    AVG(precio) OVER nombre_ventana AS precio_medio
FROM
    TablaVentas
WINDOW nombre_ventana AS (PARTITION BY categoria ORDER BY fecha_venta);

Esto resulta especialmente útil cuando tenemos varias funciones de ventana que comparten la misma lógica de partición y ordenación.

Conclusión

Las funciones de ventana representan una herramienta fundamental en el arsenal de cualquier experto en SQL Server. Nos brindan la capacidad de realizar análisis sofisticados sobre conjuntos de datos relacionados sin sacrificar la información a nivel de fila, abriendo un abanico de posibilidades para calcular totales acumulados, medias móviles, rankings dinámicos, y comparar valores entre filas.

Dominar la sintaxis de la cláusula «OVER()», comprender los diferentes tipos de funciones de ventana (agregación, ranking, valor), y saber cómo utilizar las cláusulas «PARTITION BY», «ORDER BY», «ROWS», y «RANGE» nos permitirá escribir consultas más eficientes, legibles y potentes. La introducción de la cláusula «WINDOW» en versiones recientes de SQL Server simplifica aún más la gestión de consultas complejas con múltiples definiciones de ventana.

Os animamos a explorar y practicar con estas funciones en vuestros proyectos. El potencial analítico que desbloquean las funciones de ventana en SQL Server es enorme y, sin duda, os permitirá llevar vuestras habilidades de análisis de datos al siguiente nivel.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!