🎯 SQL para limpieza y casting de datos en MySQL

Objetivo principal: Identificar los factores clave que predicen el incumplimiento de pago mediante el análisis de datos financieros y sociodemográficos.
Acción propuesta: Desarrollar un modelo de machine learning para predecir la probabilidad de que un préstamo caiga en morosidad.
Propósito: Automatizar la evaluación del riesgo crediticio para instituciones financieras, permitiéndoles tomar decisiones más informadas y reducir su cartera vencida.
Se implementó un flujo de limpieza de datos en MySQL utilizando SQL puro. El objetivo fue depurar, transformar y preparar una tabla con datos crudos mediante eliminación de duplicados, tratamiento de nulos, limpieza de texto y conversión de tipos.

1. Estructurar esquema para la tabla 

🔹 Objetivo: Preparar el espacio donde se trabajarán y almacenarán los datos.

Esquema de trabajo
Se crea un esquema (autos) para mantener el proyecto organizado.

Tablas (crudas)
Se crean tablas para importar los datos y se importan los datos.

Creación de esquema

CREATE SCHEMA `automoviles` ;


CREATE TABLE `autos`.`dataautos` (
  `unnamed` INT NULL,
  `DataAutoscol` VARCHAR(45) NULL,
  `date_crawled` VARCHAR(45) NULL,
  `car_name` VARCHAR(45) NULL,
  `price_EUR` VARCHAR(45) NULL,
  `ab_test` VARCHAR(45) NULL,
  `vehicle_type` VARCHAR(45) NULL,
  `registration_year` VARCHAR(45) NULL,
  `transmission` VARCHAR(45) NULL,
  `power_ps` VARCHAR(45) NULL,
  `model` VARCHAR(45) NULL,
  `odometer_km` VARCHAR(45) NULL,
  `registration_month` VARCHAR(45) NULL,
  `fuel_type` VARCHAR(45) NULL,
  `brand` VARCHAR(45) NULL,
  `unrepaired_damage` VARCHAR(45) NULL,
  `ad_created` VARCHAR(45) NULL,
  `postal_code` VARCHAR(45) NULL,
  `last_seen` VARCHAR(45) NULL);


-- Se crea el Store Procedure para monitorear continuamente las actualizaciones
DELIMITER //
CREATE PROCEDURE tabComp ()
BEGIN 
	SELECT * FROM dataautos LIMIT 20;
END //
DELIMITER ;

CALL tabComp ();

2. Limpieza de los datos

🔹 Objetivo: Corregir errores, formatos inconsistentes o datos sucios sin afectar aún las tablas finales.

Revisión de tipos y formatos
Asegurar que las fechas sean válidas, los números estén bien escritos, que no haya espacios antes o después de campos, etc.

Corrección de textos
Quitar espacios extra, poner nombres en mayúscula o minúscula, etc.

Valores nulos y registros incompletos
Decidir si rellenar, eliminar o dejar registros con datos faltantes.

Detección y tratamiento de duplicados
Identifica claves duplicadas o filas exactamente iguales y decide si conservar solo una.

Borrar columnas innecesarias

-- Se elimina por ser un ID inconsistente 
ALTER TABLE dataautos
DROP COLUMN unnamed;

-- Esta columna Se mostraba totalmente nula
ALTER TABLE dataautos
DROP COLUMN DataAutoscol;

CALL tabComp ();


-- Desactivar el modo seguro (safe update mode) para ejecutar UPDATE
SET SQL_SAFE_UPDATES = 0;

-- Limpiar los espacios al inicio y al final de cada columna
UPDATE dataautos 
SET 
    date_crawled = TRIM(date_crawled);
UPDATE dataautos 
SET 
    car_name = TRIM(car_name);
UPDATE dataautos 
SET 
    price_EUR = TRIM(price_EUR);
UPDATE dataautos 
SET 
    ab_test = TRIM(ab_test);
UPDATE dataautos 
SET 
    vehicle_type = TRIM(vehicle_type);
UPDATE dataautos 
SET 
    registration_year = TRIM(registration_year);
UPDATE dataautos 
SET 
    transmission = TRIM(transmission);
UPDATE dataautos 
SET 
    power_ps = TRIM(power_ps);
UPDATE dataautos 
SET 
    model = TRIM(model);
UPDATE dataautos 
SET 
    odometer_km = TRIM(odometer_km);
UPDATE dataautos 
SET 
    registration_month = TRIM(registration_month);
UPDATE dataautos 
SET 
    fuel_type = TRIM(fuel_type);
UPDATE dataautos 
SET 
    brand = TRIM(brand);
UPDATE dataautos 
SET 
    unrepaired_damage = TRIM(unrepaired_damage);
UPDATE dataautos 
SET 
    ad_created = TRIM(ad_created);
UPDATE dataautos 
SET 
    postal_code = TRIM(postal_code);
UPDATE dataautos 
SET 
    last_seen = TRIM(last_seen);

-- Verificar
CALL tabCElimar las filas repetidasomp ();

-- Verificar la existencia de filas duplicadas
SELECT 
    COUNT(*) - COUNT(DISTINCT CONCAT_WS('|',
                date_crawled,
                car_name,
                price_EUR,
                ab_test,
                vehicle_type,
                registration_year,
                transmission,
                power_ps,
                model,
                odometer_km,
                registration_month,
                fuel_type,
                brand,
                unrepaired_damage,
                ad_created,
                postal_code,
                last_seen)) AS filas_duplicadas
FROM
    dataautos;


/* Se crea una tabla temporal con solo filas 
unicas. Dedido a que no contamos con una primary key,
Elegimos solo las filas unicas en una tabla temporal 
a la que eliminaremos despues de limpiar.*/

CREATE TEMPORARY TABLE tmp AS
SELECT DISTINCT * FROM dataautos;

TRUNCATE TABLE dataautos;

INSERT INTO dataautos
SELECT * FROM tmp;

DROP TABLE tmp;

-- Verificar la existencia de filas duplicadas
SELECT 
    COUNT(*) - COUNT(DISTINCT CONCAT_WS('|',
                date_crawled,
                car_name,
                price_EUR,
                ab_test,
                vehicle_type,
                registration_year,
                transmission,
                power_ps,
                model,
                odometer_km,
                registration_month,
                fuel_type,
                brand,
                unrepaired_damage,
                ad_created,
                postal_code,
                last_seen)) AS filas_duplicadas
FROM
    dataautos;


/*Elimino los valores nulos de la columna car_name debido 
a que ese dato no lo podemos estimar*/
DELETE FROM dataautos 
WHERE
    car_name IS NULL OR car_name = '';

CALL tabComp() ;


-- Rellenar columnas con valores estimados
UPDATE dataautos 
SET 
    date_crawled = '2016-03-30 12:00'
WHERE
    date_crawled IS NULL
        OR date_crawled = '';

UPDATE dataautos 
SET 
    ab_test = 'unknown'
WHERE
    ab_test IS NULL
        OR ab_test = '';

UPDATE dataautos 
SET 
    vehicle_type = 'unknown'
WHERE
    vehicle_type IS NULL
        OR vehicle_type = '';
        
UPDATE dataautos 
SET 
   transmission  = 'unknown'
WHERE
    transmission IS NULL
        OR transmission = '';

UPDATE dataautos 
SET 
    model = 'unknown'
WHERE
    model IS NULL
        OR model = '';

UPDATE dataautos 
SET 
    fuel_type = 'Unknown'
WHERE
    fuel_type IS NULL
        OR fuel_type = '';

UPDATE dataautos 
SET 
    unrepaired_damage = 'Unknown'
WHERE
    unrepaired_damage IS NULL
        OR unrepaired_damage = '';

UPDATE dataautos 
SET 
    ad_created = '2016-05-30 12:00'
WHERE
    ad_created IS NULL
        OR ad_created = '';
        
UPDATE dataautos 
SET 
    postal_code = '00000'
WHERE
    postal_code IS NULL
        OR postal_code = '';

UPDATE dataautos 
SET 
    last_seen = '2016-06-30 12:00'
WHERE
    last_seen IS NULL
        OR last_seen = '';

-- Verificar
CALL tabComp() ;


-- Quitar caracteres 
UPDATE dataautos
SET price_EUR = TRIM(REPLACE(price_EUR, '€', ''));

-- Verificar
CALL tabComp() ;

🔹 Objetivo: Corregir errores, formatos inconsistentes o datos sucios sin afectar aún las tablas finales.

Revisión de tipos y formatos
Asegurar que las fechas sean válidas, los números estén bien escritos, que no haya espacios antes o después de campos, etc.

Corrección de textos
Quitar espacios extra, poner nombres en mayúscula o minúscula, etc.

Valores nulos y registros incompletos
Decidir si rellenar, eliminar o dejar registros con datos faltantes.

Detección y tratamiento de duplicados
Identifica claves duplicadas o filas exactamente iguales y decide si conservar solo una.

3. Conversión de tipos de datos

Objetivo: Optimizar la estructura de la base de datos definiendo tipos de datos precisos (INT, VARCHAR, DATE, etc.) para cada columna, mejorando el rendimiento, la integridad de los datos y la eficiencia en consultas.

- Conversión de formatos incorrectos (ej: texto → fechas).

- Asignación de longitudes adecuadas (ej: VARCHAR para cadenas).

- Validación de datos existentes.

Columnas tipo fecha (DATE o DATETIME)

-- Se cambia el tipo de dato a tipo fecha 

ALTER TABLE dataautos
MODIFY COLUMN date_crawled DATETIME;

ALTER TABLE dataautos
MODIFY COLUMN ad_created DATETIME;

ALTER TABLE dataautos
MODIFY COLUMN last_seen DATETIME;

-- Mostrar el tipo de columna
SHOW COLUMNS FROM dataautos;


-- Convertir (varchar) a (int)
ALTER TABLE dataautos
MODIFY COLUMN price_EUR INT;

ALTER TABLE dataautos
MODIFY COLUMN registration_year INT;

ALTER TABLE dataautos
MODIFY COLUMN power_ps INT;

ALTER TABLE dataautos
MODIFY COLUMN odometer_km INT;

ALTER TABLE dataautos
MODIFY COLUMN registration_month INT;

ALTER TABLE dataautos
MODIFY COLUMN postal_code INT;

-- Mostrar el tipo de columna
SHOW COLUMNS FROM dataautos;

4. Normalización de la Base de datos

🔹 Objetivo: Aplicar 1FN, 2FN y 3FN para eliminar redundancias y garantizar integridad:

1FN: Datos atómicos, sin repeticiones.

2FN: Dependencia completa de la clave primaria.

3FN: Eliminar dependencias transitivas.

Resultado: Base de datos optimizada, sin anomalías y fácil de escalar.

Planteamiento de estructura de la base

/* Teniendo en cuenta que la base debe ser escalable, 
es necesario aplicar un proceso de normalización. 
Actualmente cumplimos con la Primera Forma Normal (1FN), 
ya que no existen grupos repetitivos ni atributos multivaluados.
 El siguiente paso consiste en separar las claves foráneas en 
tablas independientes, con el fin de mejorar la escalabilidad, 
integridad y flexibilidad del sistema.*/

-- Mostrar las columnas
SHOW COLUMNS FROM dataautos;


DELIMITER //

CREATE PROCEDURE `tablas_id`( 
    IN nueva_tabla VARCHAR(64),
    IN columna_original VARCHAR(64), 
    IN tabla_original VARCHAR(64),
    IN columna_id VARCHAR(64)
)
BEGIN
    DECLARE temp_tabla VARCHAR(70);
    SET temp_tabla = CONCAT('tmp_', nueva_tabla);

    -- 1. Crear tabla temporal con columna ID
    SET @crear_tabla_sql = CONCAT(
        'CREATE TABLE IF NOT EXISTS ', temp_tabla, ' AS ',
        'WITH vista_temporal AS ( ',
            'SELECT DISTINCT ', columna_original, ' FROM ', tabla_original, ' ',
        '), Numerado AS ( ',
            'SELECT ROW_NUMBER() OVER (ORDER BY ', columna_original, ') AS ', columna_id, ', ',
            columna_original, ' FROM vista_temporal ',
        ') ',
        'SELECT DA.*, N.', columna_id, ' FROM ', tabla_original, ' DA ',
        'LEFT JOIN Numerado N ON N.', columna_original, ' = DA.', columna_original
    );

    PREPARE crear_stmt FROM @crear_tabla_sql;
    EXECUTE crear_stmt;
    DEALLOCATE PREPARE crear_stmt;
    
    -- 2. Crear tabla de catálogo nueva
    SET @tabla_nueva = CONCAT(
        'CREATE TABLE IF NOT EXISTS ', nueva_tabla, ' AS ',
        'SELECT DISTINCT ', columna_id, ', ', columna_original, ' FROM ', temp_tabla
    );

    PREPARE crea_tabla_ID FROM @tabla_nueva;
    EXECUTE crea_tabla_ID;
    DEALLOCATE PREPARE crea_tabla_ID;

    -- 3. Eliminar columna original de temporal
    SET @alter_sql = CONCAT(
        'ALTER TABLE ', temp_tabla, ' DROP COLUMN ', columna_original
    );

    PREPARE alter_stmt FROM @alter_sql;
    EXECUTE alter_stmt;
    DEALLOCATE PREPARE alter_stmt;

    -- 4. Vaciar y reemplazar datos en tabla original
    SET @truncate_sql = CONCAT('DROP TABLE ', tabla_original);
    SET @insert_sql = CONCAT('CREATE TABLE IF NOT EXISTS ', tabla_original, ' SELECT * FROM ', temp_tabla);

    PREPARE trunc_stmt FROM @truncate_sql;
    EXECUTE trunc_stmt;
    DEALLOCATE PREPARE trunc_stmt;

    PREPARE insert_stmt FROM @insert_sql;
    EXECUTE insert_stmt;
    DEALLOCATE PREPARE insert_stmt;

    -- 5. Eliminar tabla temporal
    SET @drop_sql = CONCAT('DROP TABLE ', temp_tabla);
    PREPARE drop_stmt FROM @drop_sql;
    EXECUTE drop_stmt;
    DEALLOCATE PREPARE drop_stmt;

    -- 6. Mensaje final
    SELECT CONCAT('Tabla ', nueva_tabla, ' creada con éxito.') AS resultado;
END //

DELIMITER ;

-- Llamar al procedimiento para crear las tables con sis PK
CALL tablas_id('models', 'model', 'dataautos', 'model_id');
CALL tablas_id('marca', 'nombre_marca', 'dataautos', 'marca_id');
CALL tablas_id('vehicle_type', 'vehicle_type', 'dataautos', 'type_id');


-- Creacion de tabla con nombre "autos"
CREATE TABLE IF NOT EXISTS autos AS 

-- Definimos laestructura de la tabla
WITH no_repetir AS (
SELECT DISTINCT car_name, 
	type_id, model_id, 
    marca_id, power_ps, 
    transmission, 
    fuel_type
FROM dataautos
)

SELECT ROW_NUMBER() OVER (ORDER BY car_name) AS Id, 
	car_name, 
    type_id AS tipo, 
    model_id, 
    marca_id, 
    power_ps AS potencia_CV, 
    transmission, 
    fuel_type 
FROM no_repetir;

-- Creacion de tabla con nombre "anuncios"
CREATE TABLE IF NOT EXISTS anuncios AS 

-- Definimos laestructura de la tabla
SELECT ROW_NUMBER() OVER (ORDER BY DA.date_crawled) AS Id,
	DA.date_crawled AS fecha_rastreo, 
	DA.ad_created AS fecha_anuncio,
    DA.last_seen AS ultima_vez_visto, 
    DA.price_EUR AS precio_eur,
    DA.ab_test AS prueba_ab, 
    DA.postal_code AS codigo_postal,
    DA.unrepaired_damage AS daño_no_reparado,
    DA.odometer_km AS kilometraje, 
    DA.registration_year AS año_registro,
    DA.registration_month AS mes_registro,
    A.Id AS car_id
FROM dataautos DA
LEFT JOIN autos A
ON DA.car_name = A.car_name;

-- Por último eliminamos la tabla no estructurada
DROP TABLE dataautos;

🔹 Objetivo: Aplicar 1FN, 2FN y 3FN para eliminar redundancias y garantizar integridad:

1FN: Datos atómicos, sin repeticiones.

2FN: Dependencia completa de la clave primaria.

3FN: Eliminar dependencias transitivas.

Resultado: Base de datos optimizada, sin anomalías y fácil de escalar.

5. Diseño de integridad relacional e indexación

- Se definieron claves foráneas para establecer relaciones entre tablas y garantizar la integridad referencial.

- Se crearon índices para acelerar búsquedas, filtros y joins.

Estas acciones mejoran el rendimiento de las consultas y aseguran una estructura de datos coherente y optimizada.

Creación de índices

-- Se crean los índices para las tablas

CREATE INDEX idx_autos_id ON autos(Id);
CREATE INDEX idx_marca_id ON marcas(marca_id);
CREATE INDEX idx_models_id ON models(model_id);
CREATE INDEX idx_vehicle_type ON vehicle_type(type_id);


-- Clave foranea "car_id"
ALTER TABLE `autos`.`anuncios` 
ADD CONSTRAINT `car_fk`
  FOREIGN KEY (`car_id`)
  REFERENCES `autos`.`autos` (`Id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;


-- Clave foranea "marca_id"
ALTER TABLE `autos`.`autos` 
ADD CONSTRAINT `marcas_fk`
  FOREIGN KEY (`marca_id`)
  REFERENCES `autos`.`marcas` (`marca_id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

-- Clave foranea "model_id"
ALTER TABLE `autos`.`autos` 
ADD CONSTRAINT `modelo_fk`
  FOREIGN KEY (`model_id`)
  REFERENCES `autos`.`models` (`model_id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

-- Clave foranea "type_id"
ALTER TABLE `autos`.`autos` 
ADD INDEX `vehicle_type_fk_idx` (`tipo` ASC) VISIBLE;
;
ALTER TABLE `autos`.`autos` 
ADD CONSTRAINT `vehicle_type_fk`
  FOREIGN KEY (`tipo`)
  REFERENCES `autos`.`vehicle_type` (`type_id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;