Neteja: unitats i duplicats¶

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

En un joc de crafting, dos objectes iguals s'apilen en una sola ranura. De tant en tant, però, dos objectes semblen idèntics i tot i això es neguen a apilar-se. La mateixa icona, el mateix nom, dues ranures. Alguna cosa que no pots veure els fa diferents.

Les dades fan això tot el temps. Un espai que no és un espai. Tres jocs que es diuen exactament igual i que no són el mateix joc. Un nombre que sembla «quanta gent té aquest joc» i no ho és. En aquesta pàgina aprens a trobar la diferència amagada abans de fusionar, separar o esborrar res.

Semblen idèntics, no s'apilen¶

L'inventari de brutícia va deixar dues línies per a avui: owners és text i conté un interval, i 74 noms de jocs apareixen més d'una vegada. Tots dos amaguen el mateix tipus de trampa, de manera que mantenim una sola analogia per a tota la pàgina: dos objectes que semblen iguals i no s'apilen.

Aquesta lliçó continua amb normalitzar: un tipus, un format i una unitat per columna, i una fila per cosa real.

flowchart LR
    subgraph A ["<b>Part 1</b>: la columna owners"]
        direction TB
        A1["'10,000,000 .. 20,000,000'<br>text, un interval"] --> A2["<b>owners_min</b><br>10000000, un nombre"]
    end
    subgraph B ["<b>Part 2</b>: noms repetits"]
        direction TB
        B1["74 noms apareixen<br>més d'una vegada"] --> B2["Duplicat real,<br>duplicat aparent<br>o zona grisa?"]
    end
    A --> B

L'espai que no és un espai¶

owners diu 10,000,000 .. 20,000,000. Volem el primer nombre. L'eina és SPLIT_PART(text, separator, n): talla un text en un separador i retorna la peça número n. El separador sembla evident: espai, dos punts, espai.

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

No s'ha tallat res. first_part torna a ser el text sencer. Comprova quantes files contenen «espai, dos punts, espai»:

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

Zero. Ni una fila. Els espais els veus a la pantalla, i la base de dades diu que no hi són.

Pren-li l'empremta¶

Un codi de caràcter és el nombre que un ordinador emmagatzema per a cada caràcter. La lletra, la xifra o l'espai que veus és només la manera com es dibuixa aquest nombre a la pantalla.

ASCII(character) mostra el codi. Agafa el caràcter que hi ha just abans dels punts i compara'l amb un espai escrit al teu teclat:

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

El teu teclat escriu el caràcter 32. El fitxer conté el caràcter 160.

Un espai dur (non-breaking space) és un caràcter que sembla exactament un espai però diu a un programa «no trenquis la línia aquí». El seu codi és 160 (a l'estàndard Unicode, U+00A0; en una pàgina web s'escriu  ).

Aquesta taula es va recollir d'un lloc web, Steam Spy, i la decoració de la pàgina va venir amb les dades. Mateix aspecte, objecte diferent: no s'apilen, de manera que el separador mai no va coincidir.

Un caràcter ocult és qualsevol caràcter que no pots distingir a ull: un espai dur, un tabulador, un salt de línia enganxat al final d'un valor.

D'un interval en text a un nombre¶

Ara construeix el nombre, pas a pas. Cada fracàs t'ensenya una cosa.

Talla pel que et pots refiar. Els dos punts són caràcters ordinaris. Talla per aquí i mesura la peça:

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';

Onze caràcters on en comptes deu. L'extra és l'espai dur, encara enganxat al final.

Falla a propòsit. Fes servir les eines de la lliçó anterior: TRIM l'espai, REPLACE les comes, converteix.

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

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

Mira dins de les cometes, després de les xifres: encara hi ha un buit. TRIM no l'ha tret. Un error sorollós, per sort.

Queda't només amb les xifres. REGEXP_REPLACE(text, pattern, new, 'g') substitueix cada peça del text que coincideix amb un patró. El patró [^0-9] vol dir «qualsevol cosa que no sigui una xifra»: comes, espais de qualsevol classe i el caràcter estrany que aparegui l'any que ve.

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;

Tretze valors, ordenats com a nombres, de 0 a 100,000,000. I la classificació que era un sense-sentit a l'auditoria ara té sentit: ordenat per owners_min de més a menys, Dota 2 és a dalt amb 100,000,000, seguit de tres jocs amb 50,000,000 (Counter-Strike: Global Offensive, Team Fortress 2 i PLAYERUNKNOWN'S BATTLEGROUNDS).

Un nombre necessita la seva unitat¶

Una unitat és el que un nombre compta o mesura: dòlars, minuts, persones, quilòmetres. Un nombre sense la seva unitat es pot llegir de diverses maneres, i almenys una és errònia.

Doncs quina és la unitat d'owners_min? Fa venir ganes de dir «persones que tenen el joc». No ho és.

17,923 jocs tenen owners_min = 0. Això no vol dir que ningú no els hagi comprat. Vol dir «entre 0 i 20,000». Hem posat a la columna el nom owners_min, i no owners, justament per això: el nom porta la unitat.

El mateix val per a la resta de la taula. price és en dòlars dels EUA, no en euros. Res a la columna ho diu: només ho saps perquè la descripció del conjunt de dades ho indica. Apunta-ho.

Reals, aparents i grisos: tres tipus de «duplicat»¶

Ara els 74 noms que apareixen més d'una vegada. Esborrem les còpies? Abans de respondre, hi ha tres casos diferents.

Un duplicat real és una fila idèntica a una altra en totes les columnes: el mateix registre dues vegades. Un duplicat aparent només comparteix algunes columnes, com el nom, i en realitat és una cosa diferent.

Dels 74 noms repetits, 32 tenen desenvolupadors diferents i 42 tenen el mateix desenvolupador amb dates diferents. Ni una sola parella comparteix alhora el nom i la data.

Té games duplicats reals?¶

SELECT DISTINCT * conserva una còpia de cada fila totalment idèntica. Compta abans i després:

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

El mateix nombre. games no té duplicats reals. Els 74 noms repetits són tots duplicats aparents o zona grisa.

L'accident: un fitxer pujat dues vegades¶

Qui no ha premut mai Upload dues vegades? Ho pots simular sense tocar la taula. UNION ALL enganxa dos resultats un sota l'altre, còpies incloses:

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

53,376 files: cada joc dues vegades. Ara DISTINCT es guanya el lloc:

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

Tornem a 26,688. Les còpies eren duplicats reals, de manera que eliminar-les no va fer perdre res.

Eliminar duplicats amb una clau¶

Una clau és la columna, o el grup de columnes, que diu «això és la mateixa cosa». Dues files amb la mateixa clau són el mateix objecte real.

De vegades les còpies no són perfectament idèntiques (una té un espai de més, una altra es va actualitzar més tard), i DISTINCT * no les atrapa. Llavors tries una clau i numeres les files dins de cada grup. ROW_NUMBER() fa la numeració: 1, 2, 3... tornant a començar a cada grup.

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 anomena la clau: nom, data i desenvolupador junts. Cada joc ha rebut un número de fila 1 i una còpia amb el número de fila 2. Queda't amb WHERE rn = 1 i les còpies han desaparegut.

flowchart TB
    Q{"La fila sencera és<br>idèntica a una altra?"}
    Q -- "Sí" --> T["<b>Duplicat real</b><br>elimina la còpia:<br>DISTINCT o rn = 1"]
    Q -- "No" --> K{"Coincideixen totes<br>les columnes de la clau?"}
    K -- "No: altre desenvolupador, altra data" --> A["<b>Duplicat aparent</b><br>una cosa diferent: conserva-la"]
    K -- "Algunes sí, d'altres no" --> G["<b>Zona grisa</b><br>decideix, i apunta-ho"]

La clau descuidada¶

Ara l'error que tothom té ganes de cometre: «un joc és el seu nom». Fes servir només PARTITION BY game, sobre la taula 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;

Si només et quedes amb rn = 1, en queden 26,611 files. Aquesta «neteja» esborraria 77 files, i cap no és una còpia. Dos dels tres jocs Dark Matter hi són, i també el segon Penumbra Overture. Cap error. Cap avís. La taula simplement es fa més petita, i sembla més endreçada que abans.

Prova-ho: owners_min i la caça de duplicats¶

Obre SQL → SQL Lab, base de dades Dades classe, esquema public. Executa una sentència cada vegada: selecciona-la i després Run.

Pas 1 — Troba el culpable. Executa el SPLIT_PART amb ' .. ', el recompte amb LIKE i la consulta amb ASCII. Hauries d'obtenir el text sencer, després 0, després 160 i 32.

Pas 2 — Construeix owners_min. Executa la consulta que mesura (11 contra 10), la consulta que falla a propòsit i la de REGEXP_REPLACE. Hauries de veure 13 files ordenades com a nombres.

Pas 3 — Afegeix-la a la consulta de neteja. Obre games_step1 a SQL → Saved Queries i fes clic al seu nom. Al bloc WITH, substitueix l'owners tal qual per l'expressió REGEXP_REPLACE AS owners_min, i canvia el nom del bloc a step2. Comprova-ho amb SELECT game, owners_min FROM step2 ORDER BY owners_min DESC LIMIT 4;. Dota 2 ha de ser a dalt.

Pas 4 — Desa-la amb un nom nou. Prem Save, canvia el nom a games_step2 i tria Save as new. Si triessis Update, sobreescriuries games_step1.

Pas 5 — Caça duplicats. Executa els dos recomptes amb DISTINCT *, les consultes twice i, finalment, la clau descuidada. Hauries de poder dir quantes files reals esborraria PARTITION BY game, i anomenar-ne una.

Idees clau¶

• Cada caràcter s'emmagatzema com a codi de caràcter. Un espai dur (codi 160) sembla un espai (codi 32) i és un caràcter diferent: TRIM i ' ' no el toquen.
• SPLIT_PART talla un text en un separador; REGEXP_REPLACE(..., '[^0-9]', '', 'g') deixa només les xifres; després converteixes.
• Un nombre necessita la seva unitat. owners_min és el límit inferior d'una estimació, no un recompte: 0 vol dir «entre 0 i 20,000».
• Un duplicat real és la fila sencera repetida. Un duplicat aparent només comparteix un nom. Entremig hi ha una zona grisa que necessita una decisió escrita.
• games no té duplicats reals: DISTINCT * retorna les mateixes 26,688 files.
• Una clau diu què fa que dues files siguin la mateixa cosa. Amb ROW_NUMBER() OVER (PARTITION BY key), les còpies són les files amb rn superior a 1.
• Una clau descuidada esborra dades reals sense cap error: una fila per nom de joc perdria 77 files.

Comprovació ràpida¶

Ara cada columna té un tipus, un format i una unitat que pots anomenar. Però 199 preus diferents i 13 valors de propietaris encara són difícils de llegir d'un cop d'ull. Tot seguit: Categorització, on els nombres es converteixen en etiquetes com Common, Rare i Epic.