Teoría BBDD

Cuando empezó el apocalipsis, todo era caos: refugios al límite, suministros escasos, datos sin contexto. Pero gracias a nuestras habilidades a base de SELECT, de JOIN, de WHERE y de horas frente al terminal, la resistencia se organizó.

En esta entrega, volvemos sobre los 10 retos iniciales (parte 1 y parte 2) para mostrar cómo lo resolvimos. No simplemente hablamos de respuestas frías, estamos hablando de pasos clave en la defensa de la humanidad. Porque cada consulta lanzada a la base de datos fue una decisión crítica, y cada decisión… salvó vidas.

Parte 1: Primeros pasos bajo presión

Empezaba la primera parte, sin mucha complicación, sin imaginarnos lo que después se iba a complicar. En estos primeros retos pudimos salir del paso con consultas sencillas que vamos a ver a continuación.

Reto 1.1 – Detectar refugios al borde del colapso

Tras semanas sin recibir suministros, varios refugios estaban al borde del colapso. Necesitábamos listar los refugios con menos de 10 raciones de comida o menos de 50 litros de agua. Teníamos que conocer el RefugeID, FoodRations y WaterLiters, ordenados de menor a mayor por FoodRations. 

Para resolver este reto necesitabamos ejecutar la consulta:

SELECT RefugeID, FoodRations, WaterLiters
FROM RefugeSupplies
WHERE FoodRations < 10 OR WaterLiters < 50
ORDER BY FoodRations ASC;

En este caso seleccionamos solo las columnas que necesitábamos, seleccionar más iría contra el rendimiento. Además, el WHERE nos filtra por las condiciones críticas que necesitamos y el ORDER BY pone en primer lugar a los que se están quedando sin comida. Porque el hambre puede causar más bajas que los mutantes. Los filtros se combinan con un OR porque con cumplir cualquiera de las dos condiciones el refugio está en riesgo de colapso.

Reto 1.2 – Localizar los mejor armados

Los refugios que hemos detectado antes necesitan ayuda y solo los mejor armados podrán proporcionarles. En este momento tenemos que localizar los 5 refugios con más armamento disponible.

SELECT TOP 5 RefugeID, Weapons
FROM RefugeSupplies
ORDER BY Weapons DESC;

En esta consulta, el TOP 5 combinado con el ORDER BY nos da los resultados deseados. Ordenamos descendente para tener primero los refugios con más armas y nos quedamos con los 5 primeros resultados.

Reto 1.3 – Delimitar la zona caliente

Nos informan de movimiento de mutantes entre las latitudes 39 y 41 y longitudes -75 y -73. Tenemos que localizar qué refugios están en esa zona.

SELECT RefugeID, Latitude, Longitude
FROM RefugeSupplies
WHERE Latitude BETWEEN 39 AND 41 AND Longitude BETWEEN -75 AND -73;

Seleccionamos solo las columnas necesarias y usamos BETWEEN para filtrar por latitud y longitud. Al contrario que en el primer escenario, usamos un AND para combinar los filtros porque para estar en la zona de los mutantes los registros tienen que cumplir ambas condiciones (estar en la misma latitud y longitud).

Reto 1.4 – Cruzar población y recursos

Tener recursos está bien. Tener gente también. Pero si no cruzas esos datos, vuelas a ciegas. Esta unión entre tablas nos permitió ver la capacidad real de cada refugio: cuántas personas había y con qué contaban para resistir.

SELECT rs.RefugeID, s.Population, rs.FoodRations, rs.Weapons
FROM RefugeSupplies rs
INNER JOIN SurvivorStats s ON rs.RefugeID = s.RefugeID;

En este caso usamos INNER JOIN para unir las tablas RefugeSupplies y SurvivorStats usando el campo RefugeID que es común entre ellas en el ON. Sin esta consulta, no puedes tomar decisiones que impliquen vidas humanas.

Reto 1.5 – Refugios en riesgo inmediato

La cosa se ponía fea, teníamos que detectar que refugios tenían demasiada gente y pocas armas. Pero claro, esos datos para filtrar estaban en tablas distintas. Primero debíamos unirlas y después filtrar por los refugios que cumpliesen con las dos condiciones.

SELECT rs.RefugeID, s.Population, rs.Weapons
FROM RefugeSupplies rs
INNER JOIN SurvivorStats s ON rs.RefugeID = s.RefugeID
WHERE rs.Weapons < 5 AND s.Population > 50;

En este caso no hay nada nuevo, simplemente combinamos el INNER JOIN del reto anterior con filtros del WHERE que combinan las dos condiciones, muchas bocas, pocas balas. Si no se actuaba rápido, no quedaría nadie a quien alimentar.

Parte 2: Cuando los informes salvan vidas

La cosa se empieza a complicar, hasta ahora hemos leído datos tal como están en la base de datos pero no hemos operado con ellos. Si queremos salvar a la humanidad tenemos que ir un paso más allá.

Reto 2.1 – Calcular la tasa de infección

No basta con contar infectados. Hay que calcular su proporción. Este cálculo nos dará la tasa de infección por refugio, y necesitábamos saber los que superaban el 5%. Una columna más que números: un indicador de si la situación estaba bajo control… o fuera de él

SELECT RefugeID, Population,Infected,
   CAST((CAST(Infected AS FLOAT) / Population) * 100 AS DECIMAL(5,2)) AS InfectionRate
FROM  SurvivorStats
WHERE  (CAST(Infected AS FLOAT) / Population) * 100 > 5
ORDER BY  InfectionRate DESC;

Hay que hacer una división entre campos pero no es tan sencillo. El doble CAST es esencial, primero lo usamos para convertir a FLOAT para que la división no se redondee a entero y luego ya, el resultado multiplicado por 100 lo convertimos a DECIMAL(5,2) para obtener un porcentaje legible. Podríamos haberlo hecho también con CONVERT en vez de CAST siguiendo la misma lógica. 

Reto 2.2 – Clasificar automáticamente los refugios

El tiempo iba en nuestra contra y no podíamos revisar cada fila a mano. Necesitábamos etiquetar los refugios automáticamente. 

SELECT RefugeID, FoodRations, WaterLiters,
    CASE
        WHEN FoodRations < 10 OR WaterLiters < 50 THEN 'CRITICAL'
        ELSE 'OK'
    END AS Status
FROM  RefugeSupplies;

Usamos CASE para definir una lógica simple, si la comida o el agua está por debajo del mínimo, el refugio está en estado CRITICAL. Si no, está OK. Esta clasificación era la base de cualquier estrategia.

Reto 2.3 – ¿Cuántos están en cada estado?

Somos gente de datos, y de automatismos, no podemos estar contando cuántos refugios están bien y cuántos críticos. Tenemos que dar ese dato en la misma consulta.

SELECT
    CASE
        WHEN FoodRations < 10 OR WaterLiters < 50 THEN 'CRITICAL'
        ELSE 'OK'
    END AS Status,
    COUNT(*) AS RefugeCount
FROM RefugeSupplies
GROUP BY
    CASE
        WHEN FoodRations < 10 OR WaterLiters < 50 THEN 'CRITICAL'
        ELSE 'OK'
    END;

Ya teníamos el estado individual de cada refugio. Nos basamos en la consulta anterior, quitamos las columnas que no nos interesan y usamos GROUP BY para agrupar por estado (CRITICAL u OK). Con eso y un COUNT(*) nos daba el número de refugios en cada grupo. 

Reto 2.4 – Avistamientos recientes por día

Las hordas no atacan a ciegas. Tampoco nosotros. Necesitábamos construir una consulta para seguir la evolución diaria de los avistamientos durante la última semana.

SELECT
    CAST(SightingDate AS DATE) AS SightDate,
    COUNT(*) AS SightingsCount
FROM MutantSightings
WHERE SightingDate >= DATEADD(DAY, -7, GETDATE())
GROUP BY CAST(SightingDate AS DATE)
ORDER BY SightDate ASC;

En este caso el CAST(… AS DATE) elimina la hora para agrupar correctamente por dia. Después, con DATEADD(…, -7, GETDATE()) calculábamos la fecha hace siete días. El resultado: una línea temporal del infierno.

Reto 2.5 – Amenazas cercanas a refugios vulnerables

Este fue el punto en que las cosas se pusieron serias de verdad. Necesitábamos una consulta que detectara avistamientos recientes cerca de los refugios más vulnerables.

Para ello crearemos una CTE con los refugios críticos y luego la consultaremos cruzando los datos con los de los avistamientos y las zonas.

WITH CriticalRefuges AS (
    SELECT RefugeID, Latitude, Longitude
    FROM RefugeSupplies
    WHERE FoodRations < 10 OR WaterLiters < 50
)
SELECT cr.RefugeID, m.SightingDate, m.Latitude, m.Longitude
FROM CriticalRefuges cr
INNER JOIN MutantSightings m ON
        ABS(cr.Latitude - m.Latitude) < 0.5
        AND ABS(cr.Longitude - m.Longitude) < 0.5
        AND m.SightingDate > DATEADD(DAY, -3, GETDATE())
ORDER BY cr.RefugeID, m.SightingDate;

Como decía primero creamos una CTE (CriticalRefuges) para aislar a los vulnerables. Luego, hacemos un JOIN con los avistamientos y filtramos:

• Usamos ABS(…) < 0.5 para ver si la distancia (en coordenadas) entre refugio y avistamiento es menor de medio grado. ABS devuelve el valor absoluto (sin negativo), útil para comparar distancias.
• También filtramos por fecha: solo avistamientos de los últimos 3 días.

Esta consulta era difícil. No tenemos un filtro de igualdad en el JOIN, lo que no es habitual. En su lugar tenemos los filtros con ABS que nos dan un cuadrado de 1 grado (0.5 arriba, abajo, izquierda y derecha) alrededor del refugio. En lugar de pedir que las coordenadas sean exactamente iguales, que sería muy improbable, buscamos avistamientos que estén dentro de una distancia tolerable.

¿Es correcto este JOIN sin filtro de igualdad? 

Si lo es. Mientras la condición del ON devuelva TRUE o FALSE para evaluar combinaciones de filas entre tablas, puedes usar cualquier lógica que tenga sentido: comparaciones, funciones, expresiones booleanas…

Eso sí, no es eficiente a gran escala. Si estás trabajando con millones de filas y distancias reales, lo suyo es usar funciones geoespaciales (GEOGRAPHY, STDistance, índices espaciales, etc.). Pero para nuestro contexto postapocalíptico con pocos refugios y unos pocos mutantes… sobra potencia.

En otras palabras, ese JOIN actúa como un filtro espacial aproximado, no como un emparejamiento exacto.

Conclusión

Estos diez retos no son simples ejercicios de SQL. Son decisiones técnicas con consecuencias narrativas y operativas. Cada uno nos enseñó algo: a leer mejor los datos, a cruzarlos con cabeza, a anticipar problemas. Pero si pensabas que eso era todo… no conoces el apocalipsis. Porque las consultas más complejas aún están por llegar. Y cuando lo hagan, necesitaremos algo más que SELECT. Nos vemos en la última entrega. Por si acaso trae casco. …O un bate con clavos.

Las noches son cada vez más largas. Los refugios que siguen en pie son fortalezas solitarias rodeadas de silencio y muerte. Pero los supervivientes que completaron las dos primeras partes de esta serie son distintos: dominan SELECTs afilados, JOINs letales y cálculos capaces de prever el caos. Han llegado hasta aquí por méritos propios.

Si acabas de llegar: detente. Aquí no hay atajos. Vuelve al primer capítulo y luego al segundo capítulo para convertirte en un verdadero experto en análisis de datos para sobrevivir al apocalipsis.

Hoy cruzaremos la última frontera: funciones de ventana, CTEs, ROLLUP y CUBE. Con estas habilidades, no solo resistirás: podrás planificar la evacuación, anticiparte a cada ataque y construir un futuro para los últimos humanos.

SurvivalDB: el campo de batalla definitivo

Nuestra base de datos SurvivalDB ha sido nuestro campo de entrenamiento y nuestra línea de defensa y en este último asalto no iba a ser menos. Sus tablas ya te son conocidas: RefugeSupplies, SurvivorStats, MutantSightings y EvacuationRoutes. Hoy, sacaremos todo el jugo posible de sus datos.

Ejercicios: la misión final para salvar a la humanidad

Después de una semana de resistencia, los refugios que aún aguantan necesitan más que coraje: requieren análisis avanzados para planificar el contragolpe o la evacuación definitiva. Hoy vamos a cruzar la última frontera de SQL: funciones de ventana que muestran lo que sucede a tu alrededor, CTEs que revelan rutas de huida imposibles y agregaciones que transforman el caos en información estratégica.

Cada consulta que planteamos a continuación no es sólo un ejercicio: es un paso esencial para decidir si la humanidad logra resistir un día más… o se convierte en recuerdo. Afilad vuestro teclado: el futuro se escribe en T-SQL.

Ejercicio 1: Prioriza refugios con funciones de ventana

Necesitamos saber el orden de prioridad para reforzar refugios: los que más armas tengan irán al final; primero hay que ayudar a los peores armados.

Tu misión: mostrar la información de los refugios con una columna WeaponsRank que indique la posición de cada refugio en el ranking por número de armas. 

Pista: cuando la prioridad depende del orden, las funciones de ventana son tu aliado.

Ejercicio 2: Compara infectados con LAG y LEAD

El Consejo de la Resistencia necesita saber si las infecciones están empeorando refugio a refugio. Comparar cada refugio con el anterior y el siguiente permitirá identificar focos crecientes.

Tu misión: mostrar RefugeID e Infected para cada refugio además de PrevInfected y NextInfected con los valores del refugio anterior y siguiente respectivamente.

Pista: dos funciones en la misma cláusula OVER te darán el pasado y el futuro.

Ejercicio 3: Totaliza avistamientos con ROLLUP

Los analistas necesitan saber la actividad mutante diaria y por zona, pero también los totales diarios y el gran total. Esta vista global decidirá si debemos evacuar o resistir.

Tu misión: agrupar avistamientos por fecha y coordenadas (Latitude, Longitude) y usa ROLLUP para incluir los totales parciales y el gran total.

Pista: agrupar con ROLLUP es como un seguro: te dará todos los niveles de resumen.

Ejercicio 4: Construye rutas de evacuación con CTE recursiva

La evacuación debe organizarse como una cadena desde el refugio inicial al último refugio seguro. Cada ruta conecta dos refugios; necesitamos todas las rutas posibles desde el refugio 1 para planificar el éxodo.

Tu misión: crear una CTE recursiva que muestre todas las rutas desde el refugio 1, listando la secuencia completa de refugios visitados como Path.

Pista: una CTE que se llame a sí misma traza caminos que una simple consulta jamás encontraría.

Ejercicio 5: Agrega por combinaciones con CUBE

Para decidir la distribución de suministros y armas, necesitamos un análisis de refugios agrupados por su estado (CRITICAL u OK) y por rango de armas (0-20, 21-50, >50). Los totales por cada combinación y los globales son imprescindibles.

Tu misión: agrupar por ambos criterios y usar CUBE para obtener todos los totales parciales y el gran total, mostrando también el número de refugios (RefugeCount).

Pista: CUBE permite ver cada combinación posible y cada subtotal en una sola consulta.

Consejo final: sobrevivir no es suerte, es optimización

Llegar hasta aquí no ha sido casualidad. Cada consulta ha reforzado tus habilidades y tu refugio. Pero como cualquier buen plan de supervivencia, hay que mantenerlo actualizado: los índices se fragmentan, los datos crecen y las hordas evolucionan. No bajes la guardia.

¿Quieres las respuestas?

El próximo martes tendremos en YouTube un vídeo con las soluciones paso a paso de este y los anteriores capítulos para que compruebes tu progreso y consolides tu aprendizaje. Pero si lo ves sin practicar antes… no esperes sobrevivir mucho tiempo. Trata de resolver estos ejercicios por tu cuenta antes de recurrir al vídeo y deja en comentarios tus respuestas.

El amanecer tras la noche más oscura

Han pasado varios días desde los primeros ataques. Gracias a los análisis, los refugios críticos fueron reforzados, los más armados ayudaron a los demás y las rutas de evacuación se activaron a tiempo. Los mutantes siguen ahí, pero los supervivientes también. Ahora dominan SQL como nadie y han aprendido que los datos, más que las balas, son el arma definitiva.

Mientras el sol asoma sobre un paisaje todavía plagado de ruinas, una última transmisión interfiere la radio de los refugios: un código desconocido que habla de nuevas hordas, más grandes, más rápidas… y de supervivientes que podrían estar organizándose más allá de lo que creíamos posible.

Porque en este apocalipsis, el verdadero final nunca llega. Y cuando vuelva la oscuridad, volveremos con nuevas queries, nuevas estrategias… y la misma sed de seguir vivos.