SQL Window Functions Avanzadas: Análisis de Tendencias y Ranking de Datos 📊

Introducción a las Funciones Ventana Avanzadas en SQL ✨

Las funciones ventana son una de las características más poderosas y subutilizadas de SQL. Permiten realizar cálculos sobre un conjunto de filas relacionadas con la fila actual, sin agrupar el resultado, manteniendo las filas individuales. Mientras que las funciones ventana básicas como SUM() OVER() o AVG() OVER() son un buen punto de partida, el verdadero poder reside en sus aplicaciones avanzadas para análisis de tendencias, ranking, comparaciones temporales y mucho más.

En este tutorial, daremos un paso más allá para explorar funciones ventana analíticas y de ranking que te permitirán desentrañar patrones complejos y extraer información valiosa de tus conjuntos de datos.

¿Por qué son tan importantes las funciones ventana avanzadas? 🤔

Las funciones ventana avanzadas resuelven problemas que son difíciles o imposibles de abordar con funciones de agregación tradicionales o subconsultas correlacionadas. Permiten:

• Ranking: Asignar una posición a cada fila dentro de un grupo.
• Análisis de Tendencias: Comparar una fila con filas anteriores o siguientes (ej. ventas del mes actual vs. mes anterior).
• Cálculos de Percentiles y Cuartiles: Dividir un conjunto de datos en segmentos.
• Balanceo de Cargas o Agrupación Personalizada: Dividir filas en un número específico de grupos.

Preparando el Terreno: Nuestro Conjunto de Datos de Ejemplo 🛠️

Para ilustrar los conceptos, utilizaremos un conjunto de datos simple que simula las ventas de productos a lo largo del tiempo en diferentes regiones. Creamos y poblaremos una tabla Ventas.

CREATE TABLE Ventas (
    id_venta INT PRIMARY KEY IDENTITY(1,1),
    fecha_venta DATE,
    region VARCHAR(50),
    producto VARCHAR(50),
    cantidad INT,
    precio_unitario DECIMAL(10, 2)
);

INSERT INTO Ventas (fecha_venta, region, producto, cantidad, precio_unitario) VALUES
('2023-01-05', 'Norte', 'Laptop', 2, 1200.00),
('2023-01-10', 'Sur', 'Smartphone', 5, 800.00),
('2023-01-15', 'Norte', 'Teclado', 10, 75.00),
('2023-01-20', 'Este', 'Monitor', 3, 300.00),
('2023-02-01', 'Norte', 'Laptop', 1, 1200.00),
('2023-02-05', 'Oeste', 'Tablet', 4, 450.00),
('2023-02-10', 'Sur', 'Smartphone', 6, 800.00),
('2023-02-15', 'Este', 'Auriculares', 8, 150.00),
('2023-03-01', 'Norte', 'Monitor', 2, 300.00),
('2023-03-05', 'Sur', 'Laptop', 3, 1200.00),
('2023-03-10', 'Oeste', 'Smartphone', 7, 800.00),
('2023-03-15', 'Este', 'Teclado', 12, 75.00),
('2023-04-01', 'Norte', 'Teclado', 5, 75.00),
('2023-04-05', 'Sur', 'Monitor', 2, 300.00),
('2023-04-10', 'Oeste', 'Auriculares', 6, 150.00),
('2023-04-15', 'Este', 'Laptop', 1, 1200.00),
('2023-05-01', 'Norte', 'Smartphone', 4, 800.00),
('2023-05-05', 'Sur', 'Teclado', 8, 75.00),
('2023-05-10', 'Oeste', 'Monitor', 3, 300.00),
('2023-05-15', 'Este', 'Tablet', 2, 450.00);

Calcularemos el monto total de cada venta como cantidad * precio_unitario.

Funciones de Ranking: Asignando Posiciones 🥇🥈🥉

Las funciones de ranking son fundamentales para identificar los elementos top N, clasificar datos dentro de grupos y manejar empates.

ROW_NUMBER(): El ranking más básico

Asigna un número secuencial único a cada fila dentro de una partición, comenzando desde 1. No maneja empates; si hay valores idénticos en la columna de ordenación, les asigna números de fila diferentes pero consecutivos.

Sintaxis: ROW_NUMBER() OVER (PARTITION BY columna1 ORDER BY columna2 [ASC|DESC])

Ejemplo: Queremos saber el orden de cada venta por fecha_venta dentro de cada region.

SELECT
    id_venta,
    fecha_venta,
    region,
    producto,
    (cantidad * precio_unitario) AS monto_venta,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY fecha_venta ASC) AS numero_fila_region
FROM Ventas;

Uso práctico: Para obtener la primera venta de cada región.

RANK(): Ranking con empates

Asigna el mismo rango a las filas que tienen valores idénticos en la columna de ordenación. El siguiente rango disponible salta el número de posiciones que ocupan los empates.

Sintaxis: RANK() OVER (PARTITION BY columna1 ORDER BY columna2 [ASC|DESC])

Ejemplo: Clasificar productos por monto de venta dentro de cada mes. Si dos productos tienen el mismo monto, tendrán el mismo rango, y el siguiente producto tendrá un rango que salta la posición.

SELECT
    id_venta,
    CAST(fecha_venta AS VARCHAR(7)) AS mes,
    region,
    producto,
    (cantidad * precio_unitario) AS monto_venta,
    RANK() OVER (PARTITION BY CAST(fecha_venta AS VARCHAR(7)) ORDER BY (cantidad * precio_unitario) DESC) AS rank_venta_mes
FROM Ventas;

DENSE_RANK(): Ranking denso con empates

Similar a RANK(), asigna el mismo rango a filas con valores idénticos. Sin embargo, no salta números en la secuencia de rango; el siguiente rango disponible es siempre el siguiente entero consecutivo.

Sintaxis: DENSE_RANK() OVER (PARTITION BY columna1 ORDER BY columna2 [ASC|DESC])

Ejemplo: Clasificar productos por monto de venta dentro de cada mes, pero con un ranking denso.

SELECT
    id_venta,
    CAST(fecha_venta AS VARCHAR(7)) AS mes,
    region,
    producto,
    (cantidad * precio_unitario) AS monto_venta,
    DENSE_RANK() OVER (PARTITION BY CAST(fecha_venta AS VARCHAR(7)) ORDER BY (cantidad * precio_unitario) DESC) AS dense_rank_venta_mes
FROM Ventas;

NTILE(N): Dividiendo en grupos iguales 📦

Divide las filas de una partición en un número N de grupos aproximadamente iguales y asigna un número de grupo a cada fila. Útil para análisis de percentiles o para dividir una población en segmentos (ej. cuartiles, deciles).

Sintaxis: NTILE(N) OVER (PARTITION BY columna1 ORDER BY columna2 [ASC|DESC])

Ejemplo: Dividir todas las ventas en 4 cuartiles (grupos) según su monto de venta.

SELECT
    id_venta,
    fecha_venta,
    region,
    producto,
    (cantidad * precio_unitario) AS monto_venta,
    NTILE(4) OVER (ORDER BY (cantidad * precio_unitario) DESC) AS cuartil_venta
FROM Ventas;

Ejemplo con partición: Dividir las ventas de cada región en 3 tercios.

SELECT
    id_venta,
    fecha_venta,
    region,
    producto,
    (cantidad * precio_unitario) AS monto_venta,
    NTILE(3) OVER (PARTITION BY region ORDER BY (cantidad * precio_unitario) DESC) AS tercio_venta_region
FROM Ventas;

Funciones de Desplazamiento: Analizando Tendencias Temporales 📈

Estas funciones permiten acceder a datos de filas anteriores o posteriores dentro de la misma partición, lo que es invaluable para cálculos de diferencias, porcentajes de cambio y análisis de series temporales.

LAG(): Mirando hacia atrás ⏪

Accede al valor de una columna en una fila anterior dentro de la ventana, a un desplazamiento especificado. Ideal para calcular la diferencia con el período anterior.

Sintaxis: LAG(expresion, offset, default_value) OVER (PARTITION BY columna1 ORDER BY columna2 [ASC|DESC])

• expresion: La columna cuyo valor quieres recuperar.
• offset: Cuántas filas hacia atrás mirar (por defecto es 1).
• default_value: Valor a retornar si no hay una fila anterior (por defecto es NULL).

Ejemplo: Comparar el monto de venta actual con el monto de la venta anterior del mismo producto.

SELECT
    id_venta,
    fecha_venta,
    producto,
    (cantidad * precio_unitario) AS monto_venta,
    LAG((cantidad * precio_unitario), 1, 0) OVER (PARTITION BY producto ORDER BY fecha_venta ASC) AS monto_venta_anterior,
    (cantidad * precio_unitario) - LAG((cantidad * precio_unitario), 1, 0) OVER (PARTITION BY producto ORDER BY fecha_venta ASC) AS diferencia_monto
FROM Ventas
ORDER BY producto, fecha_venta;

LEAD(): Mirando hacia adelante ⏩

Accede al valor de una columna en una fila posterior dentro de la ventana, a un desplazamiento especificado. Útil para planificar o prever.

Sintaxis: LEAD(expresion, offset, default_value) OVER (PARTITION BY columna1 ORDER BY columna2 [ASC|DESC])

• expresion: La columna cuyo valor quieres recuperar.
• offset: Cuántas filas hacia adelante mirar (por defecto es 1).
• default_value: Valor a retornar si no hay una fila posterior (por defecto es NULL).

Ejemplo: Mostrar el monto de la siguiente venta del mismo producto.

SELECT
    id_venta,
    fecha_venta,
    producto,
    (cantidad * precio_unitario) AS monto_venta,
    LEAD((cantidad * precio_unitario), 1, 0) OVER (PARTITION BY producto ORDER BY fecha_venta ASC) AS monto_siguiente_venta
FROM Ventas
ORDER BY producto, fecha_venta;

Funciones de Distribución y Valor: Explorando los Extremos 📉⬆️

Estas funciones te permiten entender la distribución de tus datos o extraer valores específicos dentro de una ventana.

FIRST_VALUE() y LAST_VALUE(): El primero y el último de la ventana 🎯

FIRST_VALUE() retorna el valor de la expresión especificada de la primera fila en la ventana. LAST_VALUE() retorna el valor de la expresión de la última fila en la ventana.

Sintaxis:

• FIRST_VALUE(expresion) OVER (PARTITION BY columna1 ORDER BY columna2 [ASC|DESC] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
• LAST_VALUE(expresion) OVER (PARTITION BY columna1 ORDER BY columna2 [ASC|DESC] ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

Ejemplo FIRST_VALUE(): Encontrar el monto de la primera venta de cada producto.

SELECT
    id_venta,
    fecha_venta,
    producto,
    (cantidad * precio_unitario) AS monto_venta,
    FIRST_VALUE((cantidad * precio_unitario)) OVER (
        PARTITION BY producto
        ORDER BY fecha_venta ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS primera_venta_producto
FROM Ventas
ORDER BY producto, fecha_venta;

Ejemplo LAST_VALUE(): Encontrar el monto de la última venta de cada producto hasta la fecha de la fila actual.

SELECT
    id_venta,
    fecha_venta,
    producto,
    (cantidad * precio_unitario) AS monto_venta,
    LAST_VALUE((cantidad * precio_unitario)) OVER (
        PARTITION BY producto
        ORDER BY fecha_venta ASC
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) AS ultima_venta_producto
FROM Ventas
ORDER BY producto, fecha_venta;

NTH_VALUE(): El enésimo valor de la ventana 🕵️‍♀️

Retorna el valor de la expresión especificada de la enésima fila en la ventana. Útil para obtener un valor específico de una secuencia ordenada.

Sintaxis: NTH_VALUE(expresion, N) OVER (PARTITION BY columna1 ORDER BY columna2 [ASC|DESC] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

Ejemplo: Encontrar la segunda venta más cara para cada región.

SELECT
    id_venta,
    fecha_venta,
    region,
    producto,
    (cantidad * precio_unitario) AS monto_venta,
    NTH_VALUE((cantidad * precio_unitario), 2) OVER (
        PARTITION BY region
        ORDER BY (cantidad * precio_unitario) DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS segunda_venta_mas_cara_region
FROM Ventas
ORDER BY region, monto_venta DESC;

La Cláusula OVER() y los Frames de Ventana: Controlando la Vista 🖼️

La cláusula OVER() define la ventana (el conjunto de filas) sobre la cual opera la función. Puede contener:

• PARTITION BY: Divide el conjunto de resultados en particiones (grupos) a las que se aplica la función de forma independiente. Si se omite, toda la tabla es una sola partición.
• ORDER BY: Define el orden lógico de las filas dentro de cada partición. Es crucial para funciones de ranking y desplazamiento.
• ROWS o RANGE: Define el marco de la ventana, es decir, las filas físicas específicas dentro de la partición actual que se incluyen en el cálculo. Esto es donde el control fino realmente entra en juego.

Frames de Ventana (ROWS vs. RANGE) 🧐

Los frames te permiten especificar un subconjunto de filas dentro de tu PARTITION para el cálculo. Por defecto, si no se especifica un ORDER BY dentro de OVER(), la ventana es toda la partición (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING). Si se especifica un ORDER BY, el valor predeterminado es RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW para funciones de agregación (como SUM, AVG), o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW para algunas funciones analíticas.

Opciones de Frame:

• ROWS UNBOUNDED PRECEDING: Desde el inicio de la partición hasta la fila actual.
• ROWS N PRECEDING: N filas antes de la fila actual.
• ROWS CURRENT ROW: Solo la fila actual.
• ROWS N FOLLOWING: N filas después de la fila actual.
• ROWS UNBOUNDED FOLLOWING: Desde la fila actual hasta el final de la partición.

Se pueden combinar: ROWS BETWEEN N PRECEDING AND M FOLLOWING o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Ejemplo de suma acumulada con frame: Calcular las ventas acumuladas por producto a lo largo del tiempo.

SELECT
    id_venta,
    fecha_venta,
    producto,
    (cantidad * precio_unitario) AS monto_venta,
    SUM((cantidad * precio_unitario)) OVER (
        PARTITION BY producto
        ORDER BY fecha_venta ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS ventas_acumuladas_producto
FROM Ventas
ORDER BY producto, fecha_venta;

Casos de Uso Avanzados y Combinaciones 🚀

Las funciones ventana realmente brillan cuando se combinan para resolver problemas de negocio complejos.

Porcentaje de Contribución al Total del Mes por Producto

Calculamos el total de ventas mensual y luego el porcentaje de cada venta individual respecto a ese total.

SELECT
    id_venta,
    fecha_venta,
    region,
    producto,
    (cantidad * precio_unitario) AS monto_venta,
    SUM((cantidad * precio_unitario)) OVER (PARTITION BY CAST(fecha_venta AS VARCHAR(7))) AS total_ventas_mes,
    (cantidad * precio_unitario) * 100.0 /
    SUM((cantidad * precio_unitario)) OVER (PARTITION BY CAST(fecha_venta AS VARCHAR(7)))
    AS porcentaje_venta_mes
FROM Ventas
ORDER BY fecha_venta, producto;

Top N por Grupo Dinámico

Obtener los 2 productos más vendidos por región cada mes.

WITH VentasRanked AS (
    SELECT
        id_venta,
        fecha_venta,
        region,
        producto,
        (cantidad * precio_unitario) AS monto_venta,
        ROW_NUMBER() OVER (
            PARTITION BY region, CAST(fecha_venta AS VARCHAR(7))
            ORDER BY (cantidad * precio_unitario) DESC
        ) AS rn
    FROM Ventas
)
SELECT
    id_venta,
    fecha_venta,
    region,
    producto,
    monto_venta
FROM VentasRanked
WHERE rn <= 2
ORDER BY region, CAST(fecha_venta AS VARCHAR(7)), monto_venta DESC;

Detección de Cambios de Estado o Eventos Consecutivos

Imagina que tenemos un log de estado de un sistema y queremos saber cuándo cambió de 'ACTIVO' a 'INACTIVO'. Aunque no tenemos ese tipo de datos en nuestra tabla Ventas, el concepto se aplica. Con LAG(), podrías comparar el estado actual con el estado anterior.

-- Este es un ejemplo conceptual, no se aplica directamente a la tabla Ventas
-- CREATE TABLE SystemLog (
--     event_time DATETIME,
--     system_id INT,
--     status VARCHAR(50)
-- );

-- SELECT
--     event_time,
--     system_id,
--     status AS current_status,
--     LAG(status) OVER (PARTITION BY system_id ORDER BY event_time) AS previous_status
-- FROM SystemLog
-- WHERE status <> LAG(status) OVER (PARTITION BY system_id ORDER BY event_time);

Cálculo de Medias Móviles (Running Averages)

Las medias móviles suavizan las fluctuaciones de datos y revelan tendencias. Son muy comunes en finanzas y análisis de datos de series temporales.

Ejemplo: Calcular la media móvil de 3 ventas anteriores para cada producto.

SELECT
    id_venta,
    fecha_venta,
    producto,
    (cantidad * precio_unitario) AS monto_venta,
    AVG((cantidad * precio_unitario)) OVER (
        PARTITION BY producto
        ORDER BY fecha_venta ASC
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS media_movil_3_ventas
FROM Ventas
ORDER BY producto, fecha_venta;

Consideraciones de Rendimiento y Buenas Prácticas ⚙️

Aunque potentes, las funciones ventana pueden ser costosas en términos de rendimiento si no se usan correctamente.

• PARTITION BY y ORDER BY son clave: Asegúrate de que las columnas utilizadas en estas cláusulas estén bien indexadas. Un índice compuesto sobre (columna_particion, columna_ordenacion) puede ser extremadamente beneficioso.
• Tamaño de la Ventana: Cuanto más grande sea la ventana (ej. UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING en una tabla muy grande), más recursos puede consumir. Sé específico con tus frames (ROWS BETWEEN...) cuando sea posible.
• Evita Subconsultas Innecesarias: Las funciones ventana a menudo pueden reemplazar subconsultas correlacionadas complejas, lo que generalmente resulta en un mejor rendimiento y código más legible.
• Entiende el Contexto: Asegúrate de que el ORDER BY y el PARTITION BY realmente representen la lógica de tu negocio. Un orden incorrecto puede llevar a resultados erróneos.

Conclusión ✅

Las funciones ventana avanzadas son una herramienta indispensable en el arsenal de cualquier analista de datos o desarrollador SQL. Dominar ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), y NTH_VALUE(), junto con la comprensión de los frames de ventana, te permitirá realizar análisis complejos con elegancia y eficiencia.

Desde el ranking de los mejores productos hasta el análisis de tendencias de ventas o la detección de cambios, estas funciones transformarán tu capacidad para extraer insights significativos de tus bases de datos. Practica con los ejemplos, experimenta con tus propios datos y desbloquea el verdadero potencial analítico de SQL.

¡Felicidades! Ahora eres un experto en funciones ventana avanzadas.