Categorització¶

Exercici categorisation-annex.pdf · PDF

Obres un cofre en un joc i en surt una espasa. Et llegeixes el percentatge de drop, "0.37%"? No. Mires el color del nom. Comuna, Rara, Èpica, Llegendària. Una sola paraula i ja saps si te la quedes.

Algú va decidir on acaba "Rara" i on comença "Èpica". Aquella persona va convertir milers de números possibles en quatre etiquetes, perquè tu pogueres decidir en mig segon. En aquesta pàgina fas la mateixa feina amb games: 199 preus diferents es converteixen en cinc franges, i cada joc rep un nivell segons la mida de la seva audiència.

De números a nivells de raresa¶

Compta quants preus diferents té la taula:

SELECT COUNT(DISTINCT price) FROM games;   -- 199

199 preus diferents. Dibuixa una barra per preu i obtens 199 barres: il·legible. Ningú llegeix "drop rate 0.37%". Tothom llegeix "Èpic".

Una categoria és una etiqueta que posa un valor dins d'un grup. Categoritzar és substituir molts valors diferents per unes poques etiquetes.

Un interval (o franja, en anglès bin o band) és un d'aquests grups quan el valor original és un número: un rang amb un límit inferior i un de superior.

L'analogia de tota la pàgina és el nivell de raresa: una categoria és un número que s'ha fet llegible.

CASE WHEN: la regla de classificació¶

CASE WHEN és una columna calculada amb condicions: per a cada fila dona l'etiqueta de la primera condició que és certa.

L'esquelet:

CASE WHEN condition_1 THEN 'label 1'
     WHEN condition_2 THEN 'label 2'
     ELSE 'label for the rest' END AS new_column

Cada línia WHEN ... THEN ... és una branca. Tres regles ho decideixen tot:

flowchart TB
    R["<b>Una fila</b><br>price = ?"] --> W1{"price IS NULL?"}
    W1 -- "cert" --> L1["'Free'"]
    W1 -- "no cert" --> W2{"price menor que 5?"}
    W2 -- "cert" --> L2["'Under 5'"]
    W2 -- "no cert" --> W3{"price menor que 15?"}
    W3 -- "cert" --> L3["'5 to 15'"]
    W3 -- "no cert" --> W4{"price menor que 30?"}
    W4 -- "cert" --> L4["'15 to 30'"]
    W4 -- "no cert" --> E["<b>ELSE</b><br>'30 or more'"]

El joc gratuït que semblava car¶

Prova la regla més simple possible amb tres jocs que coneixes:

SELECT game, price,
       CASE WHEN price < 5 THEN 'Under 5' ELSE '5 or more' END AS band
FROM games WHERE game IN ('Half-Life 2', 'Dota 2', 'Portal') ORDER BY game;

Dota 2 és gratuït. Ha rebut la franja 5 or more. El seu preu és NULL, NULL < 5 no és cert, així que la fila ha caigut a ELSE. El joc amb més propietaris de tota la taula, i gratuït, etiquetat com a car. Cap missatge d'error.

Construir price_band¶

Cinc franges, preus en dòlars dels Estats Units. El cas NULL va primer:

SELECT CASE WHEN price IS NULL THEN 'Free'
            WHEN price < 5  THEN 'Under 5'
            WHEN price < 15 THEN '5 to 15'
            WHEN price < 30 THEN '15 to 30'
            ELSE '30 or more' END AS price_band,
       COUNT(*) AS n
FROM games WHERE game IS NOT NULL
GROUP BY price_band ORDER BY MIN(price) NULLS FIRST;

%%{init: {"themeVariables": {"xyChart": {"plotColorPalette": "#4051b5"}}}}%%
xychart-beta
    title "Jocs per franja de preu"
    x-axis ["Free", "Under 5", "5 to 15", "15 to 30", "30 or more"]
    y-axis "Jocs" 0 --> 12000
    bar [3094, 11613, 9046, 2367, 565]

Cinc files, que sumen 26,685. Cinc barres en lloc de 199.

Fixa't en la branca WHEN price < 15 THEN '5 to 15'. No diu price >= 5 AND price < 15. No cal: una fila només arriba a aquesta branca si price < 5 no ha estat cert. L'ordre ja fa aquesta feina.

Trenca-la dues vegades, a propòsit¶

La millor manera d'entendre les regles és fer malbé la consulta i mirar què passa.

Trencament 1 — mou una branca a dalt de tot. Posa WHEN price < 30 THEN '15 to 30' per sobre de totes les altres:

Tots els jocs de menys de 30 dòlars s'aturen a la primera branca. Under 5 i 5 to 15 han desaparegut: 23,026 jocs porten ara l'etiqueta equivocada.

Trencament 2 — esborra la branca Free. Torna a posar l'ordre i treu la línia WHEN price IS NULL:

Els 3,094 jocs gratuïts han caigut a ELSE i s'han unit als 565 cars: 3,659 jocs "de 30 o més". Les dues consultes trencades s'executen sense cap error. Això és, una altra vegada, l'error silenciós de la lliçó de tipus.

Ordenar les categories en el seu ordre lògic¶

Per què ORDER BY MIN(price) i no ORDER BY price_band? Perquè una etiqueta és text, i el text s'ordena com un diccionari:

Alfabèticament, 15 to 30 va primer, perquè el caràcter 1 va abans que 3 i 5. Per obtenir l'ordre lògic ordenes per alguna cosa que encara és un número: el preu més baix dins de cada franja. NULLS FIRST posa a dalt els jocs gratuïts, que no tenen preu.

Construir owners_tier¶

La segona categoria descriu l'audiència, amb el owners_min construït a la lliçó anterior. Tres nivells:

Una columna creada en un SELECT no es pot reutilitzar en aquell mateix SELECT, així que l'expressió de owners_min s'escriu de nou dins del CASE:

CASE WHEN REGEXP_REPLACE(SPLIT_PART(owners, '..', 1), '[^0-9]', '', 'g')::bigint < 20000
       THEN 'Niche'
     WHEN REGEXP_REPLACE(SPLIT_PART(owners, '..', 1), '[^0-9]', '', 'g')::bigint < 1000000
       THEN 'Mid'
     ELSE 'Hit' END AS owners_tier,

Afegit a la consulta neta (el bloc ara es diu step3) i comptat amb SELECT owners_tier, COUNT(*) AS n FROM step3 GROUP BY owners_tier ORDER BY MIN(owners_min);:

Dos de cada tres jocs de la taula són Niche. Només 623 de 26,685 són un Hit.

Creuar dues categories¶

Les categories existeixen per ser creuades. Amb les dues columnes a step3, una sola consulta respon "quina franja de preu produeix més hits?":

SELECT price_band, COUNT(*) AS games,
       SUM(CASE WHEN owners_tier = 'Hit' THEN 1 ELSE 0 END) AS hits,
       ROUND(100.0 * SUM(CASE WHEN owners_tier = 'Hit' THEN 1 ELSE 0 END) / COUNT(*), 1) AS hit_pct
FROM step3 GROUP BY price_band ORDER BY MIN(price) NULLS FIRST;

El SUM(CASE ... THEN 1 ELSE 0 END) és un truc per comptar: cada hit suma 1, tota la resta suma 0. La proporció de hits va del 0.4% entre els jocs de pagament més barats al 10.4% entre els més cars.

On van els límits?¶

Un límit és el valor on acaba un interval i comença el següent.

Per què < 5 i no <= 5? Mira els preus més habituals:

SELECT price, COUNT(*) FROM games GROUP BY price ORDER BY 2 DESC LIMIT 3;

Els preus s'amunteguen just per sota del número rodó: 4.99, 9.99. Per això el límit va sobre el número rodó, i tothom està d'acord en quin costat cau 4.99.

Categories brutes¶

Les categories de text també s'embruten. Quants editors hi ha?

SELECT COUNT(DISTINCT publisher) AS as_written,
       COUNT(DISTINCT LOWER(publisher)) AS ignoring_case
FROM games;

LOWER converteix un text en minúscules. Ignorant les majúscules, desapareixen 88 "editors": eren segones grafies d'un que ja existia. Mira'n dos:

SELECT publisher, COUNT(*) FROM games
WHERE LOWER(publisher) IN ('big fish games', 'capcom')
GROUP BY publisher ORDER BY 1;

Per a tu, dos editors. Per a una base de dades, quatre. La mateixa eina els fusiona:

CASE WHEN LOWER(publisher) = 'big fish games' THEN 'Big Fish Games'
     ELSE publisher END AS publisher

Això dona un sol Big Fish Games amb 285 jocs. A la taula de classe no arreglem les 88 grafies: les coneixem, i això va a l'informe. Conèixer un límit i deixar-lo escrit també és una decisió.

Prova-ho: franges i nivells¶

Obre SQL → SQL Lab, base de dades Dades classe, esquema public. Executa una sentència cada vegada.

Pas 1 — price_band. Executa la consulta de cinc franges sobre games. Hauries de veure cinc files en l'ordre Free, Under 5, 5 to 15, 15 to 30, 30 or more, que sumen 26,685.

Pas 2 — Trenca-la dues vegades. Fes els dos canvis descrits més amunt, un cada vegada. Abans d'arreglar-ne cap, apunta els recomptes i una frase que digui on han anat a parar els jocs.

Pas 3 — Afegeix les dues columnes a la consulta neta. Obre games_step2 a SQL → Saved Queries, canvia el nom del bloc a step3, i afegeix el teu price_band i l'expressió de owners_tier. Compte amb la coma abans de cada CASE nou. Comprova-ho amb el recompte d'owners_tier: Niche 17,921, Mid 8,141, Hit 623.

Pas 4 — Desa-la. Prem Save, canvia el nom a games_step3, tria Save as new.

Pas 5 — Creua-les. Conserva el bloc WITH step3 i executa la consulta dels hits. Hauries de poder dir quina franja té la proporció de hits més alta i quina la més baixa.

Idees clau¶

• Una categoria fa llegible un número: 199 preus es converteixen en 5 intervals. El número es queda a la seva pròpia columna, perquè una etiqueta no es pot tornar a convertir en número.
• CASE WHEN prova les seves branques de dalt a baix i s'atura a la primera certa. L'ordre forma part de la lògica.
• ELSE agafa tota la resta, inclòs NULL. Anomena primer el cas NULL, amb IS NULL.
• Un ordre equivocat o una branca que falta no dona cap error: comprova els recomptes de cada franja.
• Ordena les categories per un número (ORDER BY MIN(price)), no per l'etiqueta, que s'ordena alfabèticament.
• Els límits són decisió teva. Posa'ls en números rodons, usa una sola direcció i deixa'ls escrits.
• Les categories de text també s'embruten: Capcom i CAPCOM són dos valors fins que els fusiones.

Comprovació ràpida¶

Una de les franges d'avui no era un preu: Free era una suposició sobre què vol dir una cel·la buida. Teníem dret a fer-la? I què passa amb 23,838 puntuacions buides? A continuació: Imputació.