En el contexto de consolidar períodos de cobertura contractual superpuestos en bloques continuos efectivos, ¿cómo fusionarías estos intervalos en rangos disjuntos y no superpuestos utilizando estrictamente funciones de ventana de ANSI SQL sin bucles procedimentales?

Respuesta a la pregunta

Historia de la pregunta. La necesidad de consolidar intervalos temporales superpuestos se origina en el álgebra de intervalos de Allen (1983) y en la investigación temprana sobre bases de datos temporales. Los sistemas de seguros, plataformas de reservas de hoteles y aplicaciones de programación de recursos se enfrentan con frecuencia a este desafío cuando múltiples períodos de cobertura, reservas o ventanas de mantenimiento se superponen y requieren normalización en bloques disjuntos y contiguos para un informe de disponibilidad preciso o facturación. A diferencia de la agregación simple, esta operación exige comprender el orden y la continuidad, lo que la convierte en una prueba estándar de dominio avanzado de funciones de ventana de ANSI SQL.

El problema. Dada una tabla de rangos de fechas definida por las columnas start_date y end_date, el objetivo es fusionar todos los intervalos superpuestos o adyacentes en un conjunto mínimo de rangos no superpuestos. Un enfoque procedimental mantendría un búfer en ejecución, comparando cada fila con el rango fusionado actual, pero esto viola el paradigma basado en conjuntos de SQL. La dificultad principal radica en identificar “islas” de continuidad sin auto-uniones o CTEs recursivas, particularmente cuando existen superposiciones transitivas (el rango A se superpone a B, B se superpone a C, aunque A y C no se toquen directamente).

La solución. Utiliza funciones de ventana de ANSI SQL para detectar el comienzo de cada nueva isla comparando la start_date de la fila actual con el end_date máximo de todas las filas anteriores dentro de la misma partición. Cuando la start_date excede el anterior end_date máximo, comienza una nueva isla; de lo contrario, la fila actual extiende la isla existente. Asigna un total acumulado de estas banderas de “ruptura” como un island_id, luego agrupa por este identificador para calcular la min(start_date) y max(end_date consolidadas. Este enfoque logra una complejidad de O(n log n) a través de ordenación y agregación de pasada única.

Situación de la vida real

Descripción del problema. Un proveedor de atención médica multinacional mantenía una base de datos de procesamiento de reclamaciones donde los pacientes tenían múltiples pólizas de seguro superpuestas: cobertura primaria del 1 de enero al 31 de marzo, secundaria del 15 de febrero al 15 de abril, y terciaria comenzando el 1 de mayo. El sistema existente generaba rechazos de reclamaciones duplicadas porque trataba estos períodos como activos separados en lugar de un bloque de cobertura continua del 1 de enero al 15 de abril seguido de la extensión de mayo. La empresa requería una vista consolidada para hacer cumplir las reglas de “sin pago duplicado” mientras se preservaban las pistas de auditoría de los registros originales de pólizas.

Solución 1: Iteración basada en cursor procedural. Una propuesta inicial utilizó un procedimiento almacenado con un cursor ordenado por start_date, manteniendo las variables @current_start y @current_end. Para cada fila, si start_date ≤ @current_end, el código actualizaba @current_end a max(@current_end, end_date); de lo contrario, emitía el rango actual y reiniciaba las variables. Pros: Conceptualmente sencillo para desarrolladores con antecedentes imperativos; fácil de depurar paso a paso. Contras: Requiere extensiones procedimentales de PL/pgSQL o T-SQL; ejecuta fila por fila con O(n) de memoria pero bajo rendimiento de E/S; viola el requisito de un ANSI SQL puramente declarativo que puede ejecutarse en cualquier motor compatible.

Solución 2: Auto-unión con detección de cierre transitivo. Otro enfoque realizó una auto-unión t1 JOIN t2 ON t1.start_date <= t2.end_date AND t1.end_date >= t2.start_date para encontrar superposiciones inmediatas, luego utilizó un CTE recursivo para recorrer el gráfico de superposición e identificar componentes conectados. Pros: Maneja relaciones transitorias complejas teóricamente correctas sin funciones de ventana. Contras: Genera O(n²) filas intermedias antes de la recursión; computacionalmente explosivo para conjuntos de datos grandes; depende de CTEs recursivos que, aunque son estándar de ANSI SQL, son menos eficientes que las funciones de ventana para este problema específico de ordenación lineal.

Solución 3: Detección de brechas de función de ventana (elegida). El equipo implementó una solución pura de función de ventana: bandera is_new_island = CASE WHEN start_date > MAX(end_date) OVER (PARTITION BY patient_id ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) THEN 1 ELSE 0 END, luego calculó island_id = SUM(is_new_island) OVER (PARTITION BY patient_id ORDER BY start_date). La agregación final se agrupó por patient_id, island_id. Pros: Ejecución de pasada única que aprovecha la sintaxis estándar de ANSI SQL; complejidad O(n log n) limitada por la ordenación; maneja superposiciones transitivas implícitamente a través del máximo en ejecución. Contras: Requiere un manejo cuidadoso de las fechas de finalización NULL (cobertura indefinida) y la semántica de intervalos adyacentes (si los rangos en contacto se fusionan).

Resultado. La implementación consolidó 2.3 millones de registros de pólizas en 890,000 bloques de cobertura continua en menos de 12 segundos en hardware estándar, reemplazando un trabajo por lotes basado en cursor de 45 minutos. La consulta se encapsuló como una vista, permitiendo comprobaciones de elegibilidad en tiempo real y eliminando el 99% de los rechazos de reclamaciones duplicadas durante el trimestre siguiente.

WITH coverage_flags AS (
    SELECT patient_id, start_date, end_date,
           CASE 
             WHEN start_date > MAX(end_date) OVER (
                 PARTITION BY patient_id 
                 ORDER BY start_date 
                 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
             ) THEN 1 
             ELSE 0 
           END AS is_new_island
    FROM insurance_periods
),
islands AS (
    SELECT patient_id, start_date, end_date,
           SUM(is_new_island) OVER (
               PARTITION BY patient_id 
               ORDER BY start_date
           ) AS island_id
    FROM coverage_flags
)
SELECT patient_id, 
       MIN(start_date) AS consolidated_start,
       MAX(end_date) AS consolidated_end
FROM islands
GROUP BY patient_id, island_id;

Lo que los candidatos suelen pasar por alto

¿Cómo manejas los intervalos adyacentes que tocan en los extremos, como [1 de enero-10] y [11 de enero-20], y qué modificación de predicado es necesaria?

Los candidatos suelen utilizar una desigualdad estricta start_date > previous_end_date, que trata los intervalos adyacentes como islas separadas. Para la cobertura de salud o la programación continua, los períodos adyacentes suelen representar un servicio ininterrumpido y deben fusionarse. El predicado debe acomodar el tipo de intervalo: para intervalos cerrados (inicio y fin inclusivos), utiliza start_date > previous_end_date + INTERVAL '1' DAY. Para intervalos semi-abiertos [start, end) (donde el final es exclusivo), la condición start_date > previous_end_date fusiona naturalmente los adyacentes porque el 11 de enero es igual al 11 de enero. ANSI SQL admite directamente la aritmética de intervalos, por lo que la solución requiere CASE WHEN start_date > MAX(end_date) OVER (...) + INTERVAL '1' DAY THEN 1 ELSE 0 END.

¿Por qué la función de ventana MAX(end_date) produce límites de isla incorrectos cuando la entrada contiene valores NULL que representan cobertura indefinida?

Las funciones de ventana agregadas de ANSI SQL como MAX() ignoran los valores NULL en el marco. Si una póliza no tiene fecha de finalización (NULL significando “actual”), MAX(end_date) sobre filas anteriores devuelve la última fecha no NULL, potencialmente fusionando intervalos subsiguientes que deberían iniciar una nueva isla después de un hueco indefinido. Los candidatos deben reconocer que los NULL requieren un tratamiento especial: filtrar en un CTE preliminar, o usar COALESCE(end_date, DATE '9999-12-31') para tratar la cobertura indefinida como extendiéndose hasta el infinito. Alternativamente, tratar NULL como una ruptura forzada utilizando la lógica CASE WHEN end_date IS NULL THEN 0 ELSE 1 END, asegurando que la siguiente fila inicie una nueva isla.

¿Cómo extenderías esta lógica a un empaquetamiento multidimensional, como consolidar intervalos por separado para cada combinación de patient_id e insurance_type sin perder atomicidad?

Muchos candidatos intentan subconsultas o auto-uniones particionadas manualmente. El enfoque correcto aprovecha la cláusula PARTITION BY en las funciones de ventana de ANSI SQL. Modifica la especificación del marco a PARTITION BY patient_id, insurance_type tanto en el cálculo de MAX(end_date) como en SUM(is_new_island). Esto asegura que el máximo en ejecución y el contador de ID de isla se reinicien para cada grupo distinto, manteniendo un rendimiento O(n log n) en todas las particiones. No particionar correctamente provoca “sangrado” donde un hueco en la línea de tiempo de un paciente desencadena incorrectamente una nueva isla para otro paciente, corrompiendo la lógica de consolidación.