Las funciones analíticas en bases de datos relacionales permiten 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 que colapsan múltiples filas en un solo resultado, las funciones analíticas devuelven un valor para cada fila procesada.

La estructura fundamental de una función analítica se compone de cuatro elementos:

1. Función: La operación que se ejecuta sobre los datos (ej. ROW_NUMBER, RANK, SUM).
2. Cláusula de partición (PARTITION BY): Divide el conjunto de resultados en grupos independientes.
3. Cláusula de ordenamiento (ORDER BY): Define el orden lógico de las filas dentro de cada partición.
4. Cláusula de ventana (Windowing): Especifica el subconjunto físico de filas dentro de la partición sobre el cual opera la función.

Generación de datos de prueba

Para demostrar el comportamiento de estas funciones, se utilizará una tabla de transacciones comerciales. A diferencia del uso de secuencias simples, aquí se emplea un identificador único global.

CREATE TABLE product_sales (
       sale_id VARCHAR2(32) PRIMARY KEY,
       category VARCHAR2(50),
       revenue NUMBER(10, 2),
       region VARCHAR2(50)
);

INSERT INTO product_sales VALUES(SYS_GUID(), 'Electronics', 1200.50, 'North');
INSERT INTO product_sales VALUES(SYS_GUID(), 'Electronics', 950.00, 'South');
INSERT INTO product_sales VALUES(SYS_GUID(), 'Electronics', 1500.00, 'North');
INSERT INTO product_sales VALUES(SYS_GUID(), 'Clothing', 300.00, 'East');
INSERT INTO product_sales VALUES(SYS_GUID(), 'Clothing', 450.75, 'West');
INSERT INTO product_sales VALUES(SYS_GUID(), 'Clothing', 300.00, 'East');
INSERT INTO product_sales VALUES(SYS_GUID(), 'Home', 800.00, 'South');
INSERT INTO product_sales VALUES(SYS_GUID(), 'Home', 1100.25, 'North');

Enumeración de filas y deduplicación con ROW_NUMBER()

La función ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2) asigna un identificador numérico secuencial y único a cada fila dentro de un grupo definido por col1, ordenado por col2.

Es crucial distinguir entre ROW_NUMBER() y la pseudocolumna ROWNUM. Mientras que ROWNUM asigna un número a medida que las filas son recuperadas (antes de cualquier ordenamiento explícito), ROW_NUMBER() evalúa primero la cláusula ORDER BY y luego asigna la numeración, lo que garantiza resultados consistentes y predecibles.

Este comportamiento es ideal para operaciones de deduplicación basadas en criterios específicos, como obtener la transacción con mayores ingresos por categoría:

SELECT category, revenue, region, 
       ROW_NUMBER() OVER(PARTITION BY category ORDER BY revenue DESC) as row_num 
FROM product_sales;

Para filtrar y conservar únicamente el registro principal de cada grupo, se envuelve la consulta en una subconsulta:

SELECT * FROM (
    SELECT category, revenue, region, 
           ROW_NUMBER() OVER(PARTITION BY category ORDER BY revenue DESC) as rn 
    FROM product_sales
) WHERE rn = 1;

Comparación de clasificaciones: RANK() vs DENSE_RANK()

Ambas funciones calculan la posición relativa de una fila dentro de una partición, pero manejan los empates de manera diferente.

• RANK(): Asigna el mismo rango a valores idénticos, pero deja un espacio en la secuencia numérica posterior. Si hay dos elementos en la posición 1, el siguiente será 3.
• DENSE_RANK(): Asigna el mismo rango a valores idénticos sin interrumpir la secuencia. Después de dos elementos en la posición 1, el siguiente será 2.

-- Clasificación con saltos
SELECT category, revenue, region, 
       RANK() OVER(PARTITION BY category ORDER BY revenue DESC) as rank_val 
FROM product_sales;

-- Clasificación continua
SELECT category, revenue, region, 
       DENSE_RANK() OVER(PARTITION BY category ORDER BY revenue DESC) as dense_rank_val 
FROM product_sales;

Desplazamiento de datos con LAG() y LEAD()

Las funciones LAG() y LEAD() permiten acceder a datos de filas anteriores o posteriores dentro de la misma partición sin necesidad de realizar auto-join (self-joins), lo que optimiza significativamente el rendimiento.

La sintaxis acepta tres argumentos: la columna a evaluar, el desplazamiento (por defecto 1) y un valor predeterminado si el desplazamiento excede los límites de la partición.

-- Obtener el ingreso de la transacción inmediatamente anterior dentro de la misma categoría
SELECT category, revenue, region, 
       LAG(revenue, 1, 0) OVER(PARTITION BY category ORDER BY revenue DESC) as prev_revenue 
FROM product_sales;

-- Obtener el ingreso de la transacción dos posiciones adelante
SELECT category, revenue, region, 
       LEAD(revenue, 2, 0) OVER(PARTITION BY category ORDER BY revenue DESC) as next_revenue 
FROM product_sales;

-- Desplazamiento sin particionamiento (evaluación global)
SELECT category, revenue, region, 
       LAG(revenue, 1, 0) OVER(ORDER BY revenue DESC) as global_prev_revenue 
FROM product_sales;

Agregaciones acumulativas con SUM() OVER()

Al combinar una función de agregación como SUM() con la cláusula OVER(), es posible calcular totales móviles o acumulativos sin perder el nivel de detalle de las filas originales.

-- Suma acumulativa ordenada dentro de cada categoría
SELECT category, revenue, region, 
       ROW_NUMBER() OVER(PARTITION BY category ORDER BY revenue DESC) as row_num,
       SUM(revenue) OVER(PARTITION BY category ORDER BY revenue DESC) as cumulative_sum 
FROM product_sales;

-- Suma total agrupada por categoría (sin ordenamiento interno, el total se repite en cada fila del grupo)
SELECT category, revenue, region, 
       SUM(revenue) OVER(PARTITION BY category) as total_category_sum 
FROM product_sales;

-- Suma acumulativa global con manejo explícito de valores nulos
SELECT category, revenue, region, 
       SUM(revenue) OVER(ORDER BY revenue DESC NULLS LAST) as global_cumulative_sum 
FROM product_sales;

-- Gran total de todos los ingresos en la tabla
SELECT category, revenue, region, 
       SUM(revenue) OVER() as grand_total 
FROM product_sales;