Limpieza: tipos y formatos¶

Ejercicio cleaning-types-formats-annex.pdf · PDF

Pregunta rápida: ¿qué es mayor, 10 o 9? Ni has tenido que pensarlo. Ahora hazle la misma pregunta a una base de datos, con un pequeño cambio:

SELECT '10' < '9' AS as_text, 10 < 9 AS as_number;

Con comillas, 10 es menor que 9. Sin comillas, no. Las mismas cifras, respuestas opuestas. Las comillas convierten un número en texto, y el texto se compara como las palabras de un diccionario: 1 va antes que 9, y punto. Esta es exactamente la razón por la que games no podía ordenar sus propias fechas de lanzamiento. En esta página lo arreglas.

El horno: del mineral a los lingotes¶

En la auditoría de calidad de los datos abriste el cofre y solo miraste. Ahora enciendes el horno. En un juego de crafteo, el mineral parece metal, pero no puedes craftear con él hasta fundirlo en un lingote. '19.99' parece un número. Todavía no lo es.

Un tipo de dato es la clase de valor que contiene una columna. Decide qué operaciones tienen sentido: puedes sumar dos números, puedes restar dos fechas, solo puedes pegar dos textos.

Normalizar una columna significa darle un único tipo y un único formato, de modo que todos los valores se escriban de la misma manera.

Mantenemos la analogía del horno durante toda la página: el texto es el mineral, los valores con tipo son los lingotes.

Casting: encender el horno¶

El casting es convertir un valor de un tipo a otro. O el valor sale con el tipo nuevo, o la consulta se detiene con un error.

PostgreSQL tiene dos formas de escribirlo. Hacen exactamente lo mismo:

SELECT '19.99'::numeric + 1, CAST('42' AS integer) * 2;

Cuando el horno no puede tragarse un valor, lo dice:

SELECT '1,234.50'::numeric;

ERROR:  invalid input syntax for type numeric: "1,234.50"

Esa coma es el problema. Quédate con este error: volveremos a si es una buena o una mala noticia.

Recortar: espacios que no ves¶

Recortar (trimming) es cortar los espacios de ambos extremos de un texto.

Un espacio es un carácter como cualquier otro. Para ti, ' Valve ' y 'Valve' son la misma distribuidora. Para una base de datos son dos distintas:

SELECT ' Valve ' = 'Valve' AS as_typed,
       TRIM(' Valve ') = 'Valve' AS trimmed;

Un solo espacio perdido en un archivo y tu GROUP BY publisher muestra la misma empresa dos veces, con los juegos repartidos entre las dos filas. TRIM(x) quita los espacios del principio y del final. No toca los del medio, así que 'Half-Life 2' conserva su espacio.

En games, TRIM(game) cambia 0 filas: los nombres ya están ordenados. Lo dejamos igualmente en la consulta de limpieza. No cuesta nada y te protege el día que alguien suba un archivo menos ordenado.

Separadores: decoración para humanos¶

Un separador decimal marca dónde termina la parte entera de un número. Un separador de millares solo agrupa las cifras para que las personas puedan leerlas.

Aquí está el truco: los países no se ponen de acuerdo sobre qué símbolo hace cada trabajo.

Las dos primeras líneas son el mismo número: mil doscientos treinta y cuatro y medio. El horno solo acepta la tercera forma. Así que primero quitas la decoración, con REPLACE(text, old, new), que cambia cada old por new:

SELECT REPLACE('1,234.50', ',', '')::numeric;                      -- 1234.50
SELECT REPLACE(REPLACE('1.234,50', '.', ''), ',', '.')::numeric;   -- 1234.50

La versión española necesita dos pasos, y el orden importa: primero borra los puntos (millares), luego convierte la coma en punto (decimales). Hazlo al revés y borrarás el punto decimal que acabas de crear.

Fechas: un valor, muchas grafías¶

Las fechas son las peores, porque cada país y cada sitio web las escribe de forma distinta. TO_DATE(text, pattern) lee una fecha escrita como dice el patrón:

La mayoría de las fechas de games tienen el aspecto Nov 16, 2004, así que el patrón es 'Mon DD, YYYY'. Prueba primero la forma perezosa, sobre toda la columna:

SELECT release_date::date FROM games;

ERROR:  invalid input syntax for type date: "Jun 2009"

SELECT TO_DATE(release_date, 'Mon DD, YYYY') FROM games;

ERROR:  date/time field value out of range: "Jun 2009"

Las dos líneas se rompen con el mismo tipo de valor: una fecha sin día. La auditoría ya nos avisó de que 42 filas tenían otra forma. Antes de convertir, cuenta las formas con exactitud.

Cuenta las formas¶

Dos herramientas nuevas. CASE es una bifurcación en el camino: cada fila toma la primera rama cuya condición sea verdadera (tiene su propia lección en Categorización). Y ~ pregunta "¿este texto encaja con este patrón?".

SELECT CASE WHEN release_date ~ '^[A-Z][a-z]{2} [0-9]{1,2}, [0-9]{4}$' THEN 'full'
            WHEN release_date ~ '^[A-Z][a-z]{2} [0-9]{4}$'             THEN 'month only'
            ELSE 'other' END AS shape,
       COUNT(*) AS n
FROM games GROUP BY shape ORDER BY n DESC;

No necesitas escribir estos patrones de memoria, solo leerlos:

La única fila de other es 8 Oct, 2014, el juego Hazard Ops: primero el día, después el mes, al revés que todas las demás.

Construye el conversor¶

La misma bifurcación, pero ahora cada camino funde su propia forma. No hay ELSE: lo que no encaja en ningún camino se convierte en NULL.

WITH step1 AS (
  SELECT number, TRIM(game) AS game,
    CASE WHEN release_date ~ '^[A-Z][a-z]{2} [0-9]{1,2}, [0-9]{4}$'
           THEN TO_DATE(release_date, 'Mon DD, YYYY')
         WHEN release_date ~ '^[A-Z][a-z]{2} [0-9]{4}$'
           THEN TO_DATE(release_date, 'Mon YYYY')
    END AS release_date,
    price::numeric(8,2) AS price,
    owners, developer, publisher, average_playtime, median_playtime, metascore
  FROM games
  WHERE game IS NOT NULL
)
SELECT COUNT(*), MIN(release_date), MAX(release_date),
       COUNT(*) - COUNT(release_date) AS no_date
FROM step1;

Aquí hay tres cosas nuevas:

El último SELECT es una fila de comprobación: cuántas filas, la fecha más antigua, la más reciente, cuántas no tienen fecha. Dice 26,685 juegos, de 2004 a 2018, y exactamente uno sin fecha. Ese es Hazard Ops.

Úsalo¶

Con una fecha real, una pregunta que era imposible en la tabla original ocupa una línea. Mantén el bloque WITH y cambia solo el SELECT final:

SELECT EXTRACT(YEAR FROM release_date)::int AS year, COUNT(*) AS n
FROM step1 GROUP BY year ORDER BY year;

%%{init: {"themeVariables": {"xyChart": {"plotColorPalette": "#4051b5"}}}}%%
xychart-beta
    title "Juegos lanzados por año en la tabla de clase"
    x-axis [2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018]
    y-axis "Juegos" 0 --> 9000
    bar [53, 58, 112, 143, 183, 334, 340, 364, 426, 556, 1352, 2544, 4503, 6871, 8845]

53 juegos en 2004, 8,845 en 2018. Esa curva llevaba todo el tiempo escondida dentro de una columna de texto.

Errores ruidosos y errores silenciosos¶

Volvamos al horno rechazando '1,234.50'. ¿Buena o mala noticia?

Un error ruidoso detiene la consulta y te dice qué valor no pudo convertir. Un error silencioso te da un valor equivocado y ningún aviso.

Buena noticia. Un error ruidoso es el tipo fácil: lo ves, lo arreglas. Ahora conoce al otro tipo. Toma el 4 de noviembre de 2026, escrito a la española:

SELECT '04/11/2026'::date;                    -- 2026-04-11
SELECT TO_DATE('04/11/2026', 'DD/MM/YYYY');   -- 2026-11-04

La primera línea devolvió el 11 de abril. Sin error. PostgreSQL tuvo que adivinar, y adivinó mes primero, a la americana. A la segunda línea se le dijo el formato, así que acertó.

flowchart TB
    T["<b>Valor de texto</b><br>'04/11/2026'"] --> G{"¿Le dijiste<br>el formato?"}
    G -- "No: '04/11/2026'::date" --> S["<b>Error silencioso</b><br>2026-04-11, sin aviso"]
    G -- "Sí: TO_DATE(..., 'DD/MM/YYYY')" --> OK["<b>Correcto</b><br>2026-11-04"]
    S --> CH["Acaba en un gráfico.<br>Alguien decide con él."]

La regla: cuando conviertes, le dices el formato al horno. Nunca dejas que adivine. Y comprueba siempre el resultado con una fila de comprobación, aunque nada se haya quejado.

Pruébalo: release_date se convierte en una fecha real¶

Abre SQL → SQL Lab, base de datos Dades classe, esquema public. Ejecuta una sentencia cada vez: selecciónala y pulsa Run.

Paso 1 — Prueba la forma perezosa. Ejecuta las dos líneas que fallan (release_date::date y TO_DATE con un solo patrón). Deberías obtener dos mensajes de error, ambos nombrando un valor como "Jun 2009".

Paso 2 — Cuenta las formas. Ejecuta la consulta con CASE. Deberías ver full 26,646, month only 41 y other 1. Copia los patrones de esta página en lugar de reescribirlos: un espacio que falte cuesta cinco minutos.

Paso 3 — Construye el conversor. Ejecuta la consulta WITH step1. La fila de comprobación debe decir 26,685 filas, la más antigua 2004-01-11, la más reciente 2018-12-31, no_date 1. Si no_date es 42 o 26,685, algún patrón está mal escrito.

Paso 4 — Úsalo y guárdalo. Cambia el SELECT final por la consulta de juegos por año. Después pulsa Save, junto a Run, y llama a la consulta games_step1. Aparece en SQL → Saved Queries. La próxima lección empieza desde ella.

Ideas clave¶

• Un tipo de dato decide qué puedes hacer con una columna. Un número o una fecha guardado como texto se ordena como un diccionario: '10' < '9' es verdadero.
• El casting (CAST(x AS type) o x::type) convierte un valor o se detiene con un error. Cambia el resultado de la consulta, nunca la tabla.
• TRIM corta los espacios de ambos extremos; REPLACE quita los separadores de millares y corrige el separador decimal antes de un cast.
• TO_DATE(text, pattern) lee una fecha en el formato que declaras. Formas distintas necesitan patrones distintos, elegidos con CASE.
• Un error ruidoso te detiene. Un error silencioso ('04/11/2026'::date dando 11 de abril) es peor: nada te avisa.
• Verifica siempre una conversión con una fila de comprobación: recuento, MIN, MAX, número de NULL.
• Cada decisión de limpieza (una fecha NULL, 41 fechas puestas en día 1) es una decisión que hay que apuntar.

Comprobación rápida¶

Las fechas y los precios ya son valores reales. Pero owners sigue siendo un rango de texto, y 74 nombres de juegos aparecen más de una vez. A continuación: Limpieza: unidades y duplicados, donde dos cosas que parecen idénticas resultan no serlo.