Detección de islas numéricas en SQL: explicación didáctica paso a paso

Hace unos días vi en LinkedIn este ejercicio: dado un conjunto de números desordenados, identificar los grupos de números consecutivos, también conocidos como islas numéricas.

El ejercicio de detección de islas numéricas en SQL es ideal para enseñar funciones de ventana y el patrón clásico de gaps and islands. En este artículo explico:

1. Qué datos había.
2. Qué resultado se esperaba.
3. Cómo resolverlo usando CTEs.
4. Comparación de dos métodos:
   • el método didáctico con ROW_NUMBER(),
   • el método más eficiente con LAG().

1. Los datos originales

El conjunto de números era este:

20, 18, 17, 15, 14, 13, 11, 9, 7, 6, 5, 4, 2, 1

2. Resultado esperado

island_id	island_size	start_num	end_num
1	2	1	2
2	4	4	7
3	3	13	15
4	2	17	18

El resultado debe indicar el número de la isla, la cantidad de números de la isla, además, el primer y el último número de esta.

3. Método didáctico para la detección de islas numéricas: ROW_NUMBER()

La idea es:

Si ordenamos los números y calculamos num - ROW_NUMBER(), los números consecutivos producen la misma diferencia. Cuando haya un salto la diferencia cambia y aparece una nueva isla.

A continuación explico cada paso.

CTE 1: cte_numbers

with cte_numbers as (
    -- Raw input numbers (unordered), arranged in rows of four
    select *
    from (values
        (20),(18),(17),(15),
        (14),(13),(11),(9),
        (7),(6),(5),(4),
        (2),(1)
    ) v(num)
),

Esto genera la lista de números que mostré al principio

CTE 2: cte_islands

Aquí viene la parte didáctica: asignamos una clave de isla usando num - ROW_NUMBER().

cte_islands as (
    -- Assign island key using the gaps-and-islands trick:
    -- consecutive numbers share the same (num - row_number)
    select
        num,
        num - row_number() over(order by num) as island_key
    from cte_numbers
),

Tabla intermedia:

num	row_number	island_key
1	1	0
2	2	0
4	3	1
5	4	1
6	5	1
7	6	1
9	7	2
11	8	3
13	9	4
14	10	4
15	11	4
17	12	5
18	13	5
20	14	6

Cada valor distinto de island_key representa una isla.

CTE 3: cte_final

Agrupamos por isla y calculamos los límites y tamaños.

cte_final as (
    -- Group by island_key and compute island boundaries
    select
        island_key,
        min(num) as start_num,
        max(num) as end_num,
        count(*) as island_size,
        rank() over(order by island_key) as island_id
    from cte_islands
    group by island_key
    having count(*) > 1   -- keep only islands with more than one element
)
select island_id, island_size, start_num, end_num
from cte_final
order by island_id;

Resultado:

island_id	island_size	start_num	end_num
1	2	1	2
2	4	4	7
3	3	13	15
4	2	17	18

4. Método más eficiente para la detección de islas numéricas: LAG()

Aunque el método con ROW_NUMBER() es más claro, el método con LAG() es más eficiente porque detecta directamente las rupturas en las secuencias.

La idea es:

Si num - LAG(num) no es 1, entonces comienza una nueva isla.

with cte_numbers as (
    -- Raw input numbers (unordered)
    select *
    from (values
        (20),(18),(17),(15),
        (14),(13),(11),(9),
        (7),(6),(5),(4),
        (2),(1)
    ) v(num)
),
cte_lag as (
    -- Compare each number with the previous one
    -- If the difference is not 1, a new island starts
    select
        num,
        lag(num) over(order by num) as prev_num,
        case when num - lag(num) over(order by num) = 1
             then 0 else 1 end as island_break
    from cte_numbers
),
cte_islands as (
    -- Accumulate island breaks to generate island keys
    select
        num,
        sum(island_break) over(order by num) as island_key
    from cte_lag
),
cte_final as (
    -- Compute island boundaries
    select
        island_key,
        min(num) as start_num,
        max(num) as end_num,
        count(*) as island_size,
        rank() over(order by island_key) as island_id
    from cte_islands
    group by island_key
    having count(*) > 1
)
select island_id, island_size, start_num, end_num
from cte_final
order by island_id;

5. Comparación de métodos

Aspecto	Método con ROW_NUMBER	Método con LAG
Claridad didáctica	Alto	Medio
Eficiencia	Medio	Alto
Detección directa de rupturas	No	Sí
Ideal para enseñar	Sí	No
Ideal para producción	No	Sí
Complejidad	Baja	Media

Conclusión sobre los métodos para la detección de islas numéricas

El método de detección de islas numéricas usando ROW_NUMBER() es útil para explicar cómo funcionan. El método con LAG() es más eficiente para producción.

Usted puede descargar el código desde el repositorio.

Imagen de Narcisse Navarre – Pixabay