Las Window Functions (Funciones de Ventana) representan una evolución en el estándar SQL (introducidas en SQL:2003) que permite realizar cálculos sobre un conjunto de filas que están relacionadas con la fila actual. A diferencia de las funciones de agregación tradicionales con GROUP BY, las Window Functions no colapsan las filas; cada registro conserva su identidad individual mientras se le adjunta el resultado del cálculo.

Desde una perspectiva profesional, su uso optimiza el rendimiento del motor de base de datos, ya que a menudo evitan múltiples pasadas por las tablas (escaneos) que las subconsultas sí requieren.

1. La Cláusula OVER: El Corazón de la Ventana

La sintaxis fundamental es FUNCTION() OVER(...). Los componentes clave son:

• PARTITION BY: Divide el conjunto de datos en grupos (ventanas) lógicas.
• ORDER BY: Define el orden de las filas dentro de cada ventana (vital para ránkings o acumulados).
• FRAME: Define el subconjunto de filas a considerar (ej. “desde el inicio hasta la fila actual”).

2. Reemplazando Subconsultas con Window Functions

En estos ejemplos utilizaremos el esquema iris para observar la elegancia y eficiencia de esta técnica.

A. Reemplazo de Subconsulta en SELECT (Métrica Global)

En lugar de ejecutar una subconsulta escalar para cada fila para obtener el promedio global, usamos OVER().

SELECT 
    id,
    petal_length,
    -- Calcula el promedio sobre toda la tabla sin agrupar filas
    ROUND(AVG(petal_length) OVER(), 2) AS promedio_global,
    ROUND(petal_length - AVG(petal_length) OVER(), 2) AS desviacion
FROM iris_muestras;

B. Reemplazo de Subconsulta en FROM (Métrica por Grupo)

Para obtener estadísticas por especie sin necesidad de crear tablas derivadas o joins adicionales, usamos PARTITION BY.

SELECT 
    m.id,
    m.especie_id,
    ed.nombre_especie,
    m.sepal_length,
    -- Promedio calculado específicamente para la especie de la fila actual
    ROUND(AVG(m.sepal_length) OVER(PARTITION BY m.especie_id), 2) AS promedio_especie
FROM iris.iris_muestras m
JOIN especies_detalles ed ON m.especie_id = ed.id;

C. Reemplazo de Subconsulta Correlacionada (Filtrado Avanzado)

Las Window Functions no pueden usarse directamente en la cláusula WHERE. Esto se debe a que se ejecutan después de que las filas han sido filtradas. Para filtrar por ellas, se envuelven en una CTE.

Caso: Encontrar flores más largas que el promedio de su especie (equivalente al ejercicio previo de WHERE).

WITH metricas_ventana AS (
    SELECT 
        id, 
        especie_id, 
        petal_length,
        AVG(petal_length) OVER(PARTITION BY especie_id) AS avg_especie
    FROM iris_muestras
)
SELECT * FROM metricas_ventana
WHERE petal_length > avg_especie;

3. Funciones de Ranking (Exclusivas de Ventana)

Existen funciones que solo pueden existir en el contexto de una ventana, fundamentales para analítica avanzada:

• RANK() / DENSE_RANK(): Asignan una posición basada en un orden.
• ROW_NUMBER(): Asigna un número único secuencial.

Ejemplo: Listar las 3 flores con pétalos más largos por cada especie.

SELECT * FROM (
    SELECT 
        m.id,
        ed.nombre_especie,
        m.petal_length,
        RANK() OVER(PARTITION BY m.especie_id ORDER BY m.petal_length DESC) AS posicion_especie
    FROM iris_muestras m
    JOIN especies_detalles ed ON m.especie_id = ed.id
) AS ranking
WHERE posicion_especie <= 3;

Comparativa Técnica: Subconsultas vs. Window Functions