Categorización¶

Ejercicio categorisation-annex.pdf · PDF

Abres un cofre en un juego y cae una espada. ¿Lees su probabilidad de aparición, "0.37%"? No. Miras el color de su nombre. Común, Rara, Épica, Legendaria. Una palabra y ya sabes si te la quedas.

Alguien decidió dónde acaba "Rara" y dónde empieza "Épica". Esa persona convirtió miles de números posibles en cuatro etiquetas, para que tú pudieras decidir en medio segundo. En esta página haces el mismo trabajo con games: 199 precios distintos se convierten en cinco franjas, y cada juego recibe un nivel según el tamaño de su audiencia.

De números a niveles de rareza¶

Cuenta cuántos precios distintos tiene la tabla:

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

199 precios distintos. Dibuja una barra por precio y tendrás 199 barras: ilegible. Nadie lee "probabilidad 0.37%". Todo el mundo lee "Épico".

Una categoría es una etiqueta que mete un valor en un grupo. Categorizar es sustituir muchos valores distintos por unas pocas etiquetas.

Un intervalo (o franja, en inglés bin o band) es uno de esos grupos cuando el valor original es un número: un rango con un límite inferior y uno superior.

La analogía de toda la página es el nivel de rareza: una categoría es un número que se ha vuelto legible.

CASE WHEN: la regla de clasificación¶

CASE WHEN es una columna calculada con condiciones: para cada fila da la etiqueta de la primera condición que se cumple.

El esqueleto:

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

Cada línea WHEN ... THEN ... es una rama. Tres reglas lo deciden todo:

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

El juego gratuito que parecía caro¶

Prueba la regla más simple posible con tres juegos que conoces:

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 es gratis. Recibió la franja 5 or more. Su precio es NULL, NULL < 5 no es verdadero, así que la fila cayó en ELSE. El juego con más propietarios de toda la tabla, y gratuito, etiquetado como caro. Sin ningún mensaje de error.

Construir price_band¶

Cinco franjas, precios en dólares estadounidenses. El caso NULL va el primero:

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 "Juegos por franja de precio"
    x-axis ["Free", "Under 5", "5 to 15", "15 to 30", "30 or more"]
    y-axis "Juegos" 0 --> 12000
    bar [3094, 11613, 9046, 2367, 565]

Cinco filas que suman 26,685. Cinco barras en lugar de 199.

Fíjate en la rama WHEN price < 15 THEN '5 to 15'. No dice price >= 5 AND price < 15. No hace falta: una fila solo llega a esa rama si price < 5 no era verdadero. El orden ya hace ese trabajo.

Rómpela dos veces, a propósito¶

La mejor manera de entender las reglas es estropear la consulta y mirar qué pasa.

Rotura 1 — mueve una rama arriba del todo. Pon WHEN price < 30 THEN '15 to 30' por encima de todas las demás:

Todos los juegos de menos de 30 dólares se detienen en la primera rama. Under 5 y 5 to 15 han desaparecido: 23,026 juegos llevan ahora la etiqueta equivocada.

Rotura 2 — borra la rama Free. Devuelve el orden a su sitio y quita la línea WHEN price IS NULL:

Los 3,094 juegos gratuitos cayeron en ELSE y se unieron a los 565 caros: 3,659 juegos "de 30 o más". Las dos consultas rotas se ejecutan sin un solo error. Es otra vez el error silencioso de la lección de tipos.

Ordenar las categorías en su orden lógico¶

¿Por qué ORDER BY MIN(price) y no ORDER BY price_band? Porque una etiqueta es texto, y el texto se ordena como un diccionario:

Alfabéticamente, 15 to 30 va el primero, porque el carácter 1 va antes que 3 y 5. Para obtener el orden lógico ordenas por algo que todavía es un número: el precio más bajo dentro de cada franja. NULLS FIRST pone arriba los juegos gratuitos, que no tienen precio.

Construir owners_tier¶

La segunda categoría describe la audiencia, usando el owners_min construido en la lección anterior. Tres niveles:

Una columna creada en un SELECT no se puede reutilizar en ese mismo SELECT, así que la expresión de owners_min se escribe otra vez dentro 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,

Añadida a la consulta limpia (el bloque ahora se llama step3) y contada con SELECT owners_tier, COUNT(*) AS n FROM step3 GROUP BY owners_tier ORDER BY MIN(owners_min);:

Dos de cada tres juegos de la tabla son Niche. Solo 623 de 26,685 son un Hit.

Cruzar dos categorías¶

Las categorías existen para cruzarse. Con las dos columnas en step3, una sola consulta responde a "¿qué franja de precio produce más éxitos?":

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) es un truco para contar: cada éxito suma 1, todo lo demás suma 0. La proporción de éxitos pasa del 0.4% entre los juegos de pago más baratos al 10.4% entre los más caros.

¿Dónde van los límites?¶

Un límite es el valor donde acaba un intervalo y empieza el siguiente.

¿Por qué < 5 y no <= 5? Mira los precios más comunes:

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

Los precios se amontonan justo por debajo del número redondo: 4.99, 9.99. Así que el límite se pone en el número redondo, y todo el mundo está de acuerdo en de qué lado cae 4.99.

Categorías desordenadas¶

Las categorías de texto también se ensucian. ¿Cuántas editoras (publishers) hay?

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

LOWER convierte un texto a minúsculas. Si ignoras las mayúsculas, desaparecen 88 "editoras": eran segundas grafías de una que ya existía. Mira dos de ellas:

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

Para ti, dos editoras. Para una base de datos, cuatro. La misma herramienta las unifica:

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

Eso deja una sola Big Fish Games con 285 juegos. En la tabla de clase no corregimos las 88 grafías: sabemos que existen, y eso va al informe. Conocer un límite y dejarlo escrito también es una decisión.

Pruébalo: franjas y niveles¶

Abre SQL → SQL Lab, base de datos Dades classe, esquema public. Ejecuta una sentencia cada vez.

Paso 1 — price_band. Ejecuta la consulta de cinco franjas sobre games. Deberías ver cinco filas en el orden Free, Under 5, 5 to 15, 15 to 30, 30 or more, que suman 26,685.

Paso 2 — Rómpela dos veces. Haz los dos cambios descritos arriba, uno cada vez. Antes de arreglar cada uno, apunta los recuentos y una frase que diga adónde fueron a parar los juegos.

Paso 3 — Añade las dos columnas a la consulta limpia. Abre games_step2 en SQL → Saved Queries, cambia el nombre del bloque a step3 y añade tu price_band y la expresión de owners_tier. Cuidado con la coma antes de cada CASE nuevo. Comprueba con el recuento de owners_tier: Niche 17,921, Mid 8,141, Hit 623.

Paso 4 — Guárdala. Pulsa Save, cambia el nombre a games_step3, elige Save as new.

Paso 5 — Cruzarlas. Mantén el bloque WITH step3 y ejecuta la consulta de éxitos. Deberías poder decir qué franja tiene la proporción más alta de éxitos y cuál la más baja.

Ideas clave¶

• Una categoría hace legible un número: 199 precios se convierten en 5 intervalos. El número se queda en su propia columna, porque una etiqueta no se puede volver a convertir en número.
• CASE WHEN prueba sus ramas de arriba abajo y se detiene en la primera que se cumple. El orden forma parte de la lógica.
• ELSE se queda con todo lo demás, incluido NULL. Nombra el caso NULL el primero, con IS NULL.
• Un orden equivocado o una rama que falta no dan error: comprueba los recuentos de cada franja.
• Ordena las categorías por un número (ORDER BY MIN(price)), no por la etiqueta, que se ordena alfabéticamente.
• Los límites son decisión tuya. Ponlos en números redondos, usa una sola dirección y déjalos escritos.
• Las categorías de texto también se ensucian: Capcom y CAPCOM son dos valores hasta que los unificas.

Comprobación rápida¶

Una de las franjas de hoy no era un precio en absoluto: Free era una suposición sobre lo que significa una celda vacía. ¿Teníamos derecho a hacerla? ¿Y qué pasa con las 23,838 puntuaciones vacías? A continuación: Imputación.