Variables sintéticas¶

Ejercicio synthetic-variables-annex.pdf · PDF

Compras tres cosas en el supermercado y miras el tique. El precio de cada producto estaba en la estantería. ¿Pero la línea que dice "3 × 1.20 = 3.60", y el TOTAL de abajo? Nadie los pegó en una estantería. Los calculó la caja.

Tus datos son lo mismo. El archivo de los juegos de Steam te dice cuánta gente tiene cada juego y cuánto cuesta. No te dice cuánto dinero movió cada juego, ni qué porcentaje del catálogo es gratuito. Esas respuestas no están en la tabla. Tienes que fabricarlas. En el juego de crafteo, este es el momento en que dejas de clasificar botín y empiezas a craftear: objetos nuevos, construidos con los que ya tienes.

Columnas que nadie recogió¶

Una variable sintética es una columna que nadie recogió. Se calcula a partir de las columnas que sí se recogieron.

"Sintética" no significa falsa. Significa montada a partir de piezas. El total de línea de un tique no es mentira: es 3 × 1.20, y cualquiera puede comprobarlo. Una variable sintética es honesta por la misma razón: viene con su receta.

Ya has hecho algunas sin llamarlas así. En Categorización construiste price_band a partir de price. En Imputación construiste is_free y has_metascore. Hoy ponemos nombre a la idea y aprendemos las tres maneras de hacerlo. Usaremos el tique durante toda la página:

flowchart LR
    R["<b>Filas de la tabla</b><br>un juego por fila"]
    R --> C["<b>Columna calculada</b><br>el total de línea:<br>entran 26,685 filas, salen 26,685 filas"]
    R --> M["<b>Métrica</b><br>el TOTAL:<br>entran 26,685 filas, sale 1 fila por grupo"]
    R --> W["<b>Función de ventana</b><br>la proporción del tique:<br>entran 26,685 filas, salen 26,685 filas,<br>cada una con el valor de su grupo"]

Primero, haz permanente la limpieza¶

Cada arreglo que escribiste en esta unidad era un SELECT. Mostraba un resultado limpio y desaparecía al cerrar la pestaña. La tabla games nunca cambió. Antes de craftear nada nuevo, guardas la limpieza como una tabla de verdad.

Ya conoces la herramienta de Datasets y destino: CREATE TABLE ... AS SELECT guarda el resultado de una consulta como una tabla nueva. El guion de clase games_clean.sql reúne todos los pasos de limpieza de la unidad en una sola sentencia. Léelo y reconocerás cada línea:

DROP TABLE IF EXISTS games_clean;
CREATE TABLE games_clean AS
SELECT
  number,
  TRIM(game) AS game,
  COALESCE(NULLIF(TRIM(developer), ''), 'Unknown') AS developer,
  COALESCE(NULLIF(TRIM(publisher), ''), 'Unknown') AS publisher,
  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,
  COALESCE(price, 0)::numeric(8,2) AS price,
  (price IS NULL) AS is_free,
  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,
  REGEXP_REPLACE(SPLIT_PART(owners, '..', 1), '[^0-9]', '', 'g')::bigint AS owners_min,
  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,
  average_playtime, median_playtime,
  metascore,
  (metascore IS NOT NULL) AS has_metascore
FROM games WHERE game IS NOT NULL;
ALTER TABLE games_clean ADD COLUMN release_year int
  GENERATED ALWAYS AS (EXTRACT(YEAR FROM release_date)::int) STORED;

La última línea es la primera variable sintética del día.

Una columna generada es una columna que la base de datos calcula por sí sola a partir de otras columnas de la misma fila, y mantiene al día.

Nadie escribe un release_year. PostgreSQL lee release_date y escribe el año a su lado:

SELECT game, release_date, release_year
FROM games_clean ORDER BY number LIMIT 3;

Comprueba que tu tabla está completa:

SELECT COUNT(*) FROM games_clean;

La respuesta es 26,685: las 26,688 filas de games menos los tres juegos sin nombre.

Después registra la tabla para que Superset pueda graficarla: Datasets → + Dataset → Database Dades classe, Schema public, Table games_clean → Create dataset (la flecha junto a Create and explore dataset).

El total de línea: una columna calculada¶

Una columna calculada es una columna nueva cuyo valor se calcula fila por fila, usando solo los valores de esa misma fila.

En el tique es "3 × 1.20 = 3.60". La caja no necesita nada más que esa línea para imprimirlo.

Nuestro total de línea: ¿cuánto dinero movió un juego, más o menos? Toma los propietarios y multiplícalos por el precio. Lo llamamos est_revenue, de estimated revenue (ingresos estimados).

SELECT game, owners_min, price, owners_min * price AS est_revenue
FROM games_clean ORDER BY est_revenue DESC LIMIT 5;

Lee la primera cifra en voz alta: mil cuatrocientos noventa y nueve millones. Unos 1,500 millones de dólares. Un número tan grande merece una segunda mirada, y se la daremos al final de esta página.

En SQL Lab esa columna vive solo dentro de tu consulta. Para tenerla lista para todos los gráficos futuros, añádela al dataset:

Otras columnas calculadas siguen el mismo patrón. El precio con un 21% de IVA es price * 1.21: entra una fila, sale un valor.

El TOTAL: una métrica¶

Una métrica es un cálculo sobre muchas filas que devuelve un valor por grupo. Siempre es un agregado: SUM, AVG, COUNT, MIN, MAX.

En el tique es el TOTAL. Las líneas desaparecen y queda un solo número. Conociste los agregados en SQL Lab: grupos; una métrica es un agregado con nombre, guardado en el dataset para que nunca tengas que reescribirlo.

Añade dos, en el mismo editor: pestaña Metrics → Add item, dos veces.

Luego Save → OK.

Mira pct_free. CASE WHEN is_free THEN 1 ELSE 0 END convierte cada juego en un 1 o un 0. Sumarlos cuenta los gratuitos. Dividir entre COUNT(*) lo convierte en una proporción. El 100.0, con su punto decimal, evita que PostgreSQL haga una división entera y responda 0.

Una métrica no tiene un tamaño fijo. Responde para el grupo que le pases. Para toda la tabla:

SELECT SUM(owners_min * price) AS total_est_revenue,
       ROUND(100.0 * SUM(CASE WHEN is_free THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_free
FROM games_clean;

Y con un GROUP BY owners_tier, las mismas dos métricas responden tres veces:

SELECT owners_tier,
       SUM(owners_min * price) AS total_est_revenue,
       ROUND(100.0 * SUM(CASE WHEN is_free THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_free
FROM games_clean GROUP BY owners_tier ORDER BY owners_tier;

Casi tres de cada diez Hits son gratuitos. ¿Y el nivel Niche ganó... cero? Guarda ese pensamiento.

La proporción del tique: funciones de ventana¶

A veces quieres las dos cosas a la vez: todas las filas, y el total al lado. "Esta línea es el 40% de tu tique." Con GROUP BY no se puede: las filas han desaparecido.

Una función de ventana calcula un valor sobre un grupo de filas y lo escribe junto a cada fila, sin colapsarlas. La reconoces por la palabra OVER.

Lee OVER () como "sobre todas las filas, sin colapsarlas". Aquí da la proporción de cada franja de precio:

SELECT price_band, COUNT(*) AS games,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_games
FROM games_clean GROUP BY price_band ORDER BY games DESC;

COUNT(*) es la línea. SUM(COUNT(*)) OVER () es el TOTAL, impreso en cada línea: 26,685 cinco veces. Divide uno entre otro y tienes una proporción sobre el total en una consulta, sin subconsulta.

Una ventana también se puede cortar en grupos.

PARTITION BY es el GROUP BY de una ventana: divide las filas en grupos, pero las filas se quedan.

Esta consulta compara cada juego con el precio medio de su propio año de lanzamiento:

SELECT game, release_year, price,
       ROUND(AVG(price) OVER (PARTITION BY release_year), 2)         AS year_avg,
       ROUND(price - AVG(price) OVER (PARTITION BY release_year), 2) AS vs_year_avg
FROM games_clean ORDER BY owners_min DESC, number LIMIT 5;

Cinco filas, cinco juegos, y cada uno lleva la media de su año. PUBG costó 22.30 dólares más que el juego medio de 2017. vs_year_avg es una variable sintética construida a partir de un cálculo agregado.

Lo que est_revenue no es¶

Ahora la segunda mirada que prometimos. Aquí tienes la proporción de juegos y la proporción de ingresos estimados por nivel:

SELECT owners_tier, COUNT(*) AS games,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_games,
       ROUND(100.0 * SUM(owners_min * price) / SUM(SUM(owners_min * price)) OVER (), 1) AS pct_revenue
FROM games_clean GROUP BY owners_tier ORDER BY pct_revenue DESC;

623 juegos de 26,685 se llevan dos tercios del dinero. Es un patrón real e interesante.

¿Pero 17,921 juegos ganaron nada? No. Recuerda de dónde salió owners_min: del extremo inferior de un rango. El archivo original dice "0 .. 20,000", y el extremo inferior de ese rango es 0. Cero propietarios por cualquier precio es cero.

La variable es tan buena como las columnas con las que se hizo. Así que est_revenue debe viajar con su etiqueta:

Pruébalo¶

Usa games_clean en SQL Lab (base de datos Dades classe, esquema public). El archivo original, video_games.csv, está en la página de Recursos.

Paso 1 — Añade la columna calculada est_revenue y las métricas total_est_revenue y pct_free a tu dataset, siguiendo las tablas de arriba.

Paso 2 — Predice primero, ejecuta después: ¿qué nivel tiene el pct_free más alto? Compruébalo con la consulta GROUP BY owners_tier.

Paso 3 — Completa la frase con la última consulta: "El nivel Hit es el __% de los juegos y el __% de los ingresos estimados."

Paso 4 — Clasifica dentro de un grupo. RANK() es una función de ventana que numera las filas de cada grupo. ¿Cuáles son los juegos con más propietarios de 2017?

SELECT game, owners_min,
       RANK() OVER (PARTITION BY release_year ORDER BY owners_min DESC) AS position
FROM games_clean WHERE release_year = 2017 ORDER BY position LIMIT 4;

Deberías obtener PLAYERUNKNOWN'S BATTLEGROUNDS (1), Unturned (2), y Robocraft y Brawlhalla compartiendo la posición 3: empatan a 10,000,000.

Paso 5 — Escribe una razón por la que est_revenue no es lo que un juego ganó realmente. Si puedes, has entendido la lección.

Ideas clave¶

• Una variable sintética es una columna que nadie recogió: se calcula a partir de las que sí se recogieron. Es honesta mientras lleve su receta.
• CREATE TABLE ... AS SELECT hace permanente la limpieza en games_clean (26,685 filas). Una columna generada como release_year la calcula la propia base de datos.
• Una columna calculada da un valor por fila (owners_min * price). Una métrica da un valor por grupo (SUM(owners_min * price)).
• Una métrica debe usar la expresión completa: las columnas calculadas de Superset no existen en la tabla de PostgreSQL.
• Una función de ventana (OVER, PARTITION BY) escribe un valor de grupo junto a cada fila. COUNT(*) / SUM(COUNT(*)) OVER () es una proporción sobre el total.
• est_revenue es un suelo, no unos ingresos: una variable es tan buena como las columnas con las que se hizo.

Comprobación rápida¶

games_clean ya existe, con columnas que nunca estuvieron en el archivo. A continuación reconstruyes la misma limpieza sin escribir una línea de SQL: Power Query, donde haces clic y la herramienta escribe la receta por ti.