Neteja: tipus i formats¶

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

Pregunta ràpida: què és més gran, 10 o 9? Ni t'ha calgut pensar-hi. Ara fes-li la mateixa pregunta a una base de dades, amb un petit canvi:

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

Amb cometes, 10 és més petit que 9. Sense cometes, no. Les mateixes xifres, respostes oposades. Les cometes converteixen un nombre en text, i el text es compara com les paraules d'un diccionari: 1 va abans que 9, i punt. Aquest és exactament el motiu pel qual games no podia ordenar les seves pròpies dates de llançament. En aquesta pàgina ho arregles.

El forn: del mineral als lingots¶

A l'auditoria de qualitat de les dades vas obrir el cofre i només vas mirar. Ara encens el forn. En un joc de crafting, el mineral sembla metall, però no pots fabricar res amb ell fins que es fon en un lingot. '19.99' sembla un nombre. Encara no ho és.

Un tipus de dades és la classe de valor que conté una columna. Decideix quines operacions tenen sentit: pots sumar dos nombres, pots restar dues dates, només pots enganxar dos textos.

Normalitzar una columna vol dir donar-li un sol tipus i un sol format, de manera que cada valor s'escrigui de la mateixa manera.

Mantenim l'analogia del forn durant tota la pàgina: el text és el mineral, els valors amb tipus són els lingots.

Conversió: encendre el forn¶

Convertir (en anglès, casting) és passar un valor d'un tipus a un altre. El valor surt en el tipus nou, o la consulta s'atura amb un error.

PostgreSQL té dues maneres d'escriure-ho. Fan exactament el mateix:

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

Quan el forn no pot empassar-se un valor, ho diu:

SELECT '1,234.50'::numeric;

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

Aquesta coma és el problema. Guarda't aquest error: tornarem a veure si és una bona o una mala notícia.

Retallar: espais que no veus¶

Retallar (trimming) és treure els espais dels dos extrems d'un text.

Un espai és un caràcter com qualsevol altre. Per a tu, ' Valve ' i 'Valve' són el mateix distribuïdor. Per a una base de dades, són dos de diferents:

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

Un sol espai sobrer en un fitxer i el teu GROUP BY publisher mostra la mateixa empresa dues vegades, amb els jocs repartits entre les dues files. TRIM(x) treu els espais del principi i del final. No toca els del mig, de manera que 'Half-Life 2' conserva el seu espai.

A games, TRIM(game) canvia 0 files: els noms ja són nets. Igualment el mantenim a la consulta de neteja. No costa res i et protegeix el dia que algú pugi un fitxer que no estigui tan endreçat.

Separadors: decoració per a humans¶

Un separador decimal marca on acaba la part sencera d'un nombre. Un separador de milers només agrupa les xifres perquè les persones les puguin llegir.

Aquí hi ha el truc: els països no es posen d'acord sobre quin símbol fa cada feina.

Les dues primeres línies són el mateix nombre: mil dos-cents trenta-quatre i mig. El forn només accepta la tercera forma. Per tant, primer treus la decoració, amb REPLACE(text, old, new), que canvia cada old per new:

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

L'espanyola necessita dos passos, i l'ordre importa: primer esborra els punts (milers), després converteix la coma en un punt (decimals). Fes-ho al revés i esborraràs el punt decimal que acabes de crear.

Dates: un valor, moltes grafies¶

Les dates són les pitjors, perquè cada país i cada pàgina web les escriu de manera diferent. TO_DATE(text, pattern) llegeix una data escrita com diu el patró:

La majoria de les dates de games són com Nov 16, 2004, de manera que el patró és 'Mon DD, YYYY'. Prova primer la manera mandrosa, sobre tota 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"

Les dues línies fallen amb el mateix tipus de valor: una data sense dia. L'auditoria ja ens va avisar que 42 files tenien una altra forma. Abans de convertir, compta les formes amb exactitud.

Compta les formes¶

Dues eines noves. CASE és una bifurcació del camí: cada fila pren la primera branca la condició de la qual és certa (té la seva pròpia lliçó a Categorització). I ~ pregunta «aquest text coincideix amb aquest patró?».

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 cal que escriguis aquests patrons de memòria, només que els sàpigues llegir:

L'única fila d'other és 8 Oct, 2014, el joc Hazard Ops: primer el dia, després el mes, al revés de tots els altres.

Construeix el convertidor¶

La mateixa bifurcació, però ara cada camí fon la seva pròpia forma. No hi ha ELSE: tot el que no encaixa en cap camí es converteix 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í hi ha tres coses noves:

L'últim SELECT és una fila de comprovació: quantes files, la data més antiga, la data més recent, quantes no tenen data. Diu 26,685 jocs, del 2004 al 2018, i exactament un sense data. Aquest és Hazard Ops.

Fes-ho servir¶

Amb una data de debò, una pregunta que era impossible a la taula original es resol en una línia. Conserva el bloc WITH i canvia només l'últim SELECT:

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 "Jocs llançats per any a la taula de classe"
    x-axis [2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018]
    y-axis "Jocs" 0 --> 9000
    bar [53, 58, 112, 143, 183, 334, 340, 364, 426, 556, 1352, 2544, 4503, 6871, 8845]

53 jocs el 2004, 8,845 el 2018. Aquesta corba era amagada dins d'una columna de text des del principi.

Errors sorollosos i errors silenciosos¶

Tornem al forn que rebutja '1,234.50'. Bona o mala notícia?

Un error sorollós atura la consulta i et diu quin valor no ha pogut convertir. Un error silenciós et dona un valor incorrecte i cap avís.

Bona notícia. Un error sorollós és el tipus fàcil: el veus, l'arregles. Ara coneix l'altre tipus. Pren el 4 de novembre de 2026, escrit a l'estil espanyol:

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

La primera línia ha retornat l'11 d'abril. Cap error. PostgreSQL ha hagut d'endevinar, i ha endevinat primer el mes, a l'americana. A la segona línia se li ha dit el format, i l'ha encertat.

flowchart TB
    T["<b>Valor en text</b><br>'04/11/2026'"] --> G{"Has indicat<br>el format?"}
    G -- "No: '04/11/2026'::date" --> S["<b>Error silenciós</b><br>2026-04-11, sense avís"]
    G -- "Sí: TO_DATE(..., 'DD/MM/YYYY')" --> OK["<b>Correcte</b><br>2026-11-04"]
    S --> CH["Acaba en un gràfic.<br>Algú hi pren decisions."]

La regla: quan converteixes, li dius al forn el format. Mai no el deixes endevinar. I sempre comproves el resultat amb una fila de comprovació, encara que res no s'hagi queixat.

Prova-ho: release_date es converteix en una data de debò¶

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 — Prova la manera mandrosa. Executa les dues línies que fallen (release_date::date i TO_DATE amb un sol patró). Hauries d'obtenir dos missatges d'error, tots dos anomenant un valor com "Jun 2009".

Pas 2 — Compta les formes. Executa la consulta amb CASE. Hauries de veure full 26,646, month only 41 i other 1. Copia els patrons d'aquesta pàgina en lloc de tornar a escriure'ls: un espai que falti costa cinc minuts.

Pas 3 — Construeix el convertidor. Executa la consulta amb WITH step1. La fila de comprovació ha de dir 26,685 files, la més antiga 2004-01-11, la més recent 2018-12-31, no_date 1. Si no_date és 42 o 26,685, algun patró s'ha escrit malament.

Pas 4 — Fes-lo servir i desa'l. Canvia l'últim SELECT per la consulta de jocs per any. Després prem Save, al costat de Run, i posa-li a la consulta el nom games_step1. Apareix a SQL → Saved Queries. La lliçó següent comença des d'ella.

Idees clau¶

• Un tipus de dades decideix què pots fer amb una columna. Un nombre o una data emmagatzemats com a text s'ordenen com un diccionari: '10' < '9' és cert.
• Convertir (CAST(x AS type) o x::type) transforma un valor o s'atura amb un error. Canvia el resultat de la consulta, mai la taula.
• TRIM talla els espais dels dos extrems; REPLACE treu els separadors de milers i arregla el separador decimal abans d'una conversió.
• TO_DATE(text, pattern) llegeix una data en el format que declares. Formes diferents necessiten patrons diferents, triats amb CASE.
• Un error sorollós t'atura. Un error silenciós ('04/11/2026'::date donant l'11 d'abril) és pitjor: res no t'avisa.
• Verifica sempre una conversió amb una fila de comprovació: recompte, MIN, MAX, nombre de NULL.
• Cada decisió de neteja (una data NULL, 41 dates posades a l'1) és una decisió que s'ha d'apuntar.

Comprovació ràpida¶

Les dates i els preus ja són valors de debò. Però owners continua sent un interval en text, i 74 noms de jocs apareixen més d'una vegada. Tot seguit: Neteja: unitats i duplicats, on dues coses que semblen idèntiques resulten no ser-ho.