Limpieza: unidades y duplicados¶

Ejercicio cleaning-units-duplicates-annex.pdf · PDF

En un juego de crafteo, dos objetos iguales se apilan en un solo hueco. De vez en cuando, dos objetos parecen idénticos y aun así se niegan a apilarse. Mismo icono, mismo nombre, dos huecos. Algo que no ves los hace distintos.

Los datos hacen esto todo el rato. Un espacio que no es un espacio. Tres juegos que se llaman exactamente igual y no son el mismo juego. Un número que parece "cuánta gente tiene este juego" y no lo es. En esta página aprendes a encontrar la diferencia oculta antes de fusionar, separar o borrar nada.

Se ven idénticos, no se apilan¶

El inventario de suciedad dejó dos líneas para hoy: owners es texto y contiene un rango, y 74 nombres de juegos aparecen más de una vez. Las dos esconden el mismo tipo de trampa, así que mantenemos una sola analogía en toda la página: dos objetos que se ven iguales y no se apilan.

Esta lección continúa con normalizar: un tipo, un formato y una unidad por columna, y una fila por cada cosa real.

flowchart LR
    subgraph A ["<b>Parte 1</b>: la columna owners"]
        direction TB
        A1["'10,000,000 .. 20,000,000'<br>texto, un rango"] --> A2["<b>owners_min</b><br>10000000, un número"]
    end
    subgraph B ["<b>Parte 2</b>: nombres repetidos"]
        direction TB
        B1["74 nombres aparecen<br>más de una vez"] --> B2["¿Duplicado verdadero,<br>duplicado aparente<br>o zona gris?"]
    end
    A --> B

El espacio que no es un espacio¶

owners dice 10,000,000 .. 20,000,000. Queremos el primer número. La herramienta es SPLIT_PART(text, separator, n): corta un texto en un separador y devuelve el trozo número n. El separador parece obvio: espacio, dos puntos, espacio.

SELECT owners, SPLIT_PART(owners, ' .. ', 1) AS first_part
FROM games WHERE game = 'Half-Life 2';

No se cortó nada. first_part es otra vez el texto entero. Comprueba cuántas filas contienen "espacio, dos puntos, espacio":

SELECT COUNT(*) FROM games WHERE owners LIKE '% .. %';   -- 0

Cero. Ni una fila. Los espacios los ves en la pantalla, y la base de datos dice que no están.

Toma la huella dactilar¶

Un código de carácter es el número que un ordenador guarda para cada carácter. La letra, la cifra o el espacio que ves es solo la forma en que ese número se dibuja en pantalla.

ASCII(character) muestra el código. Toma el carácter que está justo antes de los puntos y compáralo con un espacio escrito con tu teclado:

SELECT ASCII(SUBSTR(owners, POSITION('..' IN owners) - 1, 1)) AS mystery,
       ASCII(' ') AS normal_space
FROM games WHERE game = 'Half-Life 2';

Tu teclado escribe el carácter 32. El archivo contiene el carácter 160.

Un espacio de no separación (non-breaking space) es un carácter que se ve exactamente como un espacio pero le dice a un programa "no partas la línea aquí". Su código es 160 (en el estándar Unicode, U+00A0; en una página web se escribe  ).

Esta tabla se recogió de un sitio web, Steam Spy, y la decoración de la página vino con los datos. Mismo aspecto, objeto distinto: no se apilan, así que el separador nunca encajó.

Un carácter oculto es cualquier carácter que no puedes distinguir a simple vista: un espacio de no separación, un tabulador, un salto de línea pegado al final de un valor.

De un rango de texto a un número¶

Ahora construye el número, paso a paso. Cada fallo enseña algo.

Corta por lo que es de fiar. Los dos puntos son caracteres normales. Corta ahí y mide el trozo:

SELECT SPLIT_PART(owners, '..', 1) AS part,
       LENGTH(SPLIT_PART(owners, '..', 1)) AS len,
       LENGTH('10,000,000') AS expected
FROM games WHERE game = 'Half-Life 2';

Once caracteres donde tú cuentas diez. El de más es el espacio de no separación, todavía pegado al final.

Falla a propósito. Usa las herramientas de la lección anterior: TRIM al espacio, REPLACE a las comas, cast.

SELECT REPLACE(TRIM(SPLIT_PART(owners, '..', 1)), ',', '')::bigint FROM games;

ERROR:  invalid input syntax for type bigint: "10000000 "

Mira dentro de las comillas, después de las cifras: todavía hay un hueco. TRIM no lo quitó. Un error ruidoso, por suerte.

Quédate solo con las cifras. REGEXP_REPLACE(text, pattern, new, 'g') reemplaza cada trozo del texto que encaje con un patrón. El patrón [^0-9] significa "cualquier cosa que no sea una cifra": comas, espacios de cualquier tipo y el carácter extraño que aparezca el año que viene.

SELECT REGEXP_REPLACE(SPLIT_PART(owners, '..', 1), '[^0-9]', '', 'g')::bigint AS owners_min,
       COUNT(*) AS n
FROM games GROUP BY owners_min ORDER BY owners_min;

Trece valores, ordenados como números, de 0 a 100,000,000. Y el ranking que era un disparate en la auditoría ahora tiene sentido: ordenado por owners_min de mayor a menor, Dota 2 está arriba con 100,000,000, seguido de tres juegos con 50,000,000 (Counter-Strike: Global Offensive, Team Fortress 2 y PLAYERUNKNOWN'S BATTLEGROUNDS).

Un número necesita su unidad¶

Una unidad es lo que cuenta o mide un número: dólares, minutos, personas, kilómetros. Un número sin su unidad se puede leer de varias maneras, y al menos una de ellas es errónea.

Entonces, ¿cuál es la unidad de owners_min? Es tentador decir "personas que tienen el juego". No lo es.

17,923 juegos tienen owners_min = 0. Eso no significa que nadie los comprara. Significa "entre 0 y 20,000". Llamamos a la columna owners_min, y no owners, justo por eso: el nombre lleva la unidad.

Lo mismo vale para el resto de la tabla. price está en dólares estadounidenses, no en euros. Nada en la columna lo dice: solo lo sabes porque la descripción del conjunto de datos lo indica. Apúntalo.

Verdadero, aparente y gris: tres tipos de "duplicado"¶

Ahora los 74 nombres que aparecen más de una vez. ¿Borramos las copias? Antes de contestar, hay tres casos distintos.

Un duplicado verdadero es una fila idéntica a otra en todas las columnas: el mismo registro dos veces. Un duplicado aparente solo comparte algunas columnas, como el nombre, y en realidad es otra cosa.

De los 74 nombres repetidos, 32 tienen desarrolladores distintos y 42 tienen el mismo desarrollador con fechas distintas. Ni un solo par comparte a la vez el nombre y la fecha.

¿Tiene games duplicados verdaderos?¶

SELECT DISTINCT * conserva una copia de cada fila totalmente idéntica. Cuenta antes y después:

SELECT COUNT(*) FROM games;                                  -- 26688
SELECT COUNT(*) FROM (SELECT DISTINCT * FROM games) AS t;    -- 26688

El mismo número. games no tiene duplicados verdaderos. Los 74 nombres repetidos son todos duplicados aparentes o zona gris.

El accidente: un archivo subido dos veces¶

¿Quién no ha pulsado Subir dos veces alguna vez? Puedes simularlo sin tocar la tabla. UNION ALL pega dos resultados uno debajo de otro, copias incluidas:

WITH twice AS (SELECT * FROM games UNION ALL SELECT * FROM games)
SELECT COUNT(*) FROM twice;                                  -- 53376

53,376 filas: cada juego dos veces. Ahora DISTINCT se gana el sueldo:

WITH twice AS (SELECT * FROM games UNION ALL SELECT * FROM games)
SELECT COUNT(*) FROM (SELECT DISTINCT * FROM twice) AS t;    -- 26688

De vuelta a 26,688. Las copias eran duplicados verdaderos, así que quitarlas no perdió nada.

Eliminar duplicados con una clave¶

Una clave es la columna, o el grupo de columnas, que dice "esto es la misma cosa". Dos filas con la misma clave son el mismo objeto real.

A veces las copias no son perfectamente idénticas (una tiene un espacio de más, otra se actualizó después), así que DISTINCT * no las detecta. Entonces eliges una clave y numeras las filas dentro de cada grupo. ROW_NUMBER() hace la numeración: 1, 2, 3... empezando de nuevo en cada grupo.

WITH twice AS (SELECT * FROM games UNION ALL SELECT * FROM games),
numbered AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY game, release_date, developer ORDER BY number) AS rn
  FROM twice
)
SELECT rn, COUNT(*) FROM numbered GROUP BY rn ORDER BY rn;

PARTITION BY nombra la clave: nombre, fecha y desarrollador juntos. Cada juego recibió un número de fila 1 y una copia con número de fila 2. Quédate con WHERE rn = 1 y las copias desaparecen.

flowchart TB
    Q{"¿La fila entera es<br>idéntica a otra?"}
    Q -- "Sí" --> T["<b>Duplicado verdadero</b><br>quitar la copia:<br>DISTINCT o rn = 1"]
    Q -- "No" --> K{"¿Coinciden todas las<br>columnas de la clave?"}
    K -- "No: otro desarrollador, otra fecha" --> A["<b>Duplicado aparente</b><br>otra cosa distinta: conservarla"]
    K -- "Unas sí, otras no" --> G["<b>Zona gris</b><br>decidir, y apuntarlo"]

La clave descuidada¶

Ahora el error que todo el mundo tiene la tentación de cometer: "un juego es su nombre". Usa PARTITION BY game a secas, sobre la tabla original:

WITH numbered AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY game ORDER BY number) AS rn
  FROM games
)
SELECT rn, COUNT(*) FROM numbered GROUP BY rn ORDER BY rn;

Quedarse solo con rn = 1 deja 26,611 filas. Esa "limpieza" borraría 77 filas, y ninguna es una copia. Dos de los tres juegos Dark Matter están entre ellas, y también el segundo Penumbra Overture. Sin error. Sin aviso. La tabla simplemente se hace más pequeña, y parece más ordenada que antes.

Pruébalo: owners_min y la caza de duplicados¶

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

Paso 1 — Encuentra al culpable. Ejecuta el SPLIT_PART con ' .. ', el recuento con LIKE y la consulta ASCII. Deberías obtener el texto entero, luego 0, y luego 160 y 32.

Paso 2 — Construye owners_min. Ejecuta la consulta que mide (11 contra 10), la que falla a propósito y la del REGEXP_REPLACE. Deberías ver 13 filas ordenadas como números.

Paso 3 — Añádelo a la consulta de limpieza. Abre games_step1 en SQL → Saved Queries y haz clic en su nombre. En el bloque WITH, sustituye el simple owners por la expresión REGEXP_REPLACE AS owners_min, y renombra el bloque a step2. Compruébalo con SELECT game, owners_min FROM step2 ORDER BY owners_min DESC LIMIT 4;. Dota 2 tiene que estar arriba.

Paso 4 — Guárdalo con otro nombre. Pulsa Save, cambia el nombre a games_step2 y elige Save as new. Elegir Update sobrescribiría games_step1.

Paso 5 — Caza duplicados. Ejecuta los dos recuentos con DISTINCT *, las consultas twice y, por último, la clave descuidada. Deberías poder decir cuántas filas reales borraría PARTITION BY game, y nombrar una de ellas.

Ideas clave¶

• Cada carácter se guarda como un código de carácter. Un espacio de no separación (código 160) se ve como un espacio (código 32) y es un carácter distinto: TRIM y ' ' no lo tocan.
• SPLIT_PART corta un texto en un separador; REGEXP_REPLACE(..., '[^0-9]', '', 'g') deja solo las cifras; después conviertes.
• Un número necesita su unidad. owners_min es el límite inferior de una estimación, no un recuento: 0 significa "entre 0 y 20,000".
• Un duplicado verdadero es la fila entera repetida. Un duplicado aparente solo comparte un nombre. Entre medias hay una zona gris que necesita una decisión escrita.
• games no tiene duplicados verdaderos: DISTINCT * devuelve las mismas 26,688 filas.
• Una clave dice qué hace que dos filas sean la misma cosa. Con ROW_NUMBER() OVER (PARTITION BY key), las copias son las filas con rn mayor que 1.
• Una clave descuidada borra datos reales sin ningún error: una fila por nombre de juego perdería 77 filas.

Comprobación rápida¶

Cada columna ya tiene un tipo, un formato y una unidad que sabes nombrar. Pero 199 precios distintos y 13 valores de propietarios siguen siendo difíciles de leer de un vistazo. A continuación: Categorización, donde los números se convierten en etiquetas como Común, Raro y Épico.