Variables sintètiques¶

Exercici synthetic-variables-annex.pdf · PDF

Compres tres coses al supermercat i mires el tiquet. El preu de cada producte era al prestatge. Però la línia que diu "3 × 1.20 = 3.60", i el TOTAL de baix de tot? Ningú no les va enganxar a cap prestatge. Les va calcular la caixa.

Les teves dades són igual. El fitxer dels jocs de Steam et diu quanta gent té cada joc i què costa. No et diu quants diners ha mogut cada joc, ni quina part del catàleg és gratuïta. Aquestes respostes no són a la taula. Les has de fabricar. Al joc de crafting, aquest és el moment en què deixes de classificar botí i comences a fabricar: objectes nous, construïts amb els que ja tens.

Columnes que ningú no ha recollit¶

Una variable sintètica és una columna que ningú no ha recollit. Es calcula a partir de les columnes que sí que es van recollir.

"Sintètica" no vol dir falsa. Vol dir composta. El total d'una línia del tiquet no és una mentida: és 3 × 1.20, i qualsevol ho pot comprovar. Una variable sintètica és honesta pel mateix motiu: ve amb una recepta.

Ja n'has fet algunes sense dir-ne així. A Categorització vas construir price_band a partir de price. A Imputació vas construir is_free i has_metascore. Avui posem nom a la idea i aprenem les tres maneres de fer-ho. Mantindrem el tiquet durant tota la pàgina:

flowchart LR
    R["<b>Files de la taula</b><br>un joc per fila"]
    R --> C["<b>Columna calculada</b><br>el total de línia:<br>26,685 files d'entrada, 26,685 files de sortida"]
    R --> M["<b>Mètrica</b><br>el TOTAL:<br>26,685 files d'entrada, 1 fila per grup de sortida"]
    R --> W["<b>Funció de finestra</b><br>la proporció del tiquet:<br>26,685 files d'entrada, 26,685 files de sortida,<br>cadascuna amb el valor del seu grup"]

Primer, fes permanent la neteja¶

Cada arranjament que has escrit en aquesta unitat era un SELECT. Mostrava un resultat net i desapareixia quan tancaves la pestanya. La taula games no va canviar mai. Abans de fabricar res de nou, desa la neteja com una taula real.

Ja coneixes l'eina de Datasets i destí: CREATE TABLE ... AS SELECT desa el resultat d'una consulta com una taula nova. El guió de classe games_clean.sql posa tots els passos de neteja de la unitat en una sola sentència. Llegeix-lo i reconeixeràs cada línia:

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;

L'última línia és la primera variable sintètica del dia.

Una columna generada és una columna que la base de dades calcula tota sola a partir d'altres columnes de la mateixa fila, i manté actualitzada.

Ningú no escriu un release_year. PostgreSQL llegeix release_date i n'escriu l'any al costat:

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

Comprova que la teva taula és completa:

SELECT COUNT(*) FROM games_clean;

La resposta és 26,685: les 26,688 files de games menys els tres jocs sense nom.

Després registra la taula perquè Superset la pugui representar: Datasets → + Dataset → Database Dades classe, Schema public, Table games_clean → Create dataset (la fletxa al costat de Create and explore dataset).

El total de línia: una columna calculada¶

Una columna calculada és una columna nova el valor de la qual es calcula fila per fila, fent servir només els valors d'aquella mateixa fila.

Al tiquet és "3 × 1.20 = 3.60". La caixa no necessita res més que aquella línia per imprimir-ho.

El nostre total de línia: quants diners va moure un joc, més o menys? Pren els propietaris i multiplica pel preu. L'anomenem est_revenue, de estimated revenue (ingressos estimats).

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

Llegeix la primera xifra en veu alta: mil quatre-cents noranta-nou milions. Uns 1.500 milions de dòlars. Un número tan gros mereix una segona mirada, i li la donarem al final d'aquesta pàgina.

A SQL Lab aquesta columna només viu dins de la teva consulta. Per tenir-la a punt per a tots els gràfics futurs, afegeix-la al dataset:

Les altres columnes calculades segueixen el mateix patró. El preu amb un 21% d'IVA és price * 1.21: una fila d'entrada, un valor de sortida.

El TOTAL: una mètrica¶

Una mètrica és un càlcul sobre moltes files que retorna un valor per grup. Sempre és un agregat: SUM, AVG, COUNT, MIN, MAX.

Al tiquet és el TOTAL. Les línies desapareixen i queda un sol número. Vas conèixer els agregats a SQL Lab: grups; una mètrica és un agregat amb nom, desat al dataset perquè no l'hagis de tornar a escriure mai.

N'afegeixes dues, al mateix editor: pestanya Metrics → Add item, dues vegades.

Després Save → OK.

Mira pct_free. CASE WHEN is_free THEN 1 ELSE 0 END converteix cada joc en un 1 o un 0. Sumar-los compta els gratuïts. Dividir entre COUNT(*) en fa una proporció. El 100.0, amb el seu punt decimal, evita que PostgreSQL faci una divisió entera i respongui 0.

Una mètrica no té una mida fixa. Respon per al grup que li donis. Per a tota la taula:

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;

I amb un GROUP BY owners_tier, les mateixes dues mètriques responen tres vegades:

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;

Gairebé tres de cada deu Hits són gratuïts. I el nivell Niche ha guanyat... zero? Guarda't aquest pensament.

La proporció del tiquet: funcions de finestra¶

De vegades vols les dues coses alhora: cada fila, i el total al costat. "Aquesta línia és el 40% del teu tiquet." Amb GROUP BY no pots: les files ja no hi són.

Una funció de finestra calcula un valor sobre un grup de files i l'escriu al costat de cada fila, sense col·lapsar-les. La reconeixes per la paraula OVER.

Llegeix OVER () com "sobre totes les files, sense col·lapsar-les". Aquí dona la proporció de cada franja de preu:

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(*) és la línia. SUM(COUNT(*)) OVER () és el TOTAL, imprès a cada línia: 26,685 cinc vegades. Divideix l'un per l'altre i tens una proporció del total en una sola consulta, sense cap subconsulta.

Una finestra també es pot tallar en grups.

PARTITION BY és el GROUP BY d'una finestra: divideix les files en grups, però les files es queden.

Aquesta consulta compara cada joc amb el preu mitjà del seu propi any de llançament:

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;

Cinc files, cinc jocs, i cadascun porta la mitjana del seu any. PUBG va costar 22.30 dòlars més que el joc mitjà del 2017. vs_year_avg és una variable sintètica construïda a partir d'un càlcul d'agregació.

Què no és est_revenue¶

Ara, la segona mirada que havíem promès. Aquí tens la proporció de jocs i la proporció d'ingressos estimats per nivell:

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 jocs de 26,685 s'enduen dos terços dels diners. Aquest és un patró real i interessant.

Però 17,921 jocs no han guanyat res? No. Recorda d'on venia owners_min: de la part baixa d'un rang. El fitxer original diu "0 .. 20,000", i la part baixa d'aquest rang és 0. Zero propietaris per qualsevol preu és zero.

La variable només és tan bona com les columnes amb què s'ha fet. Així que est_revenue ha de viatjar amb la seva etiqueta:

Prova-ho¶

Fes servir games_clean a SQL Lab (base de dades Dades classe, esquema public). El fitxer original, video_games.csv, és a la pàgina de Recursos.

Pas 1 — Afegeix la columna calculada est_revenue i les mètriques total_est_revenue i pct_free al teu dataset, seguint les taules de dalt.

Pas 2 — Primer prediu, després executa: quin nivell té el pct_free més alt? Comprova-ho amb la consulta GROUP BY owners_tier.

Pas 3 — Acaba la frase amb l'última consulta: "El nivell Hit és el __% dels jocs i el __% dels ingressos estimats."

Pas 4 — Fes un rànquing dins d'un grup. RANK() és una funció de finestra que numera les files de cada grup. Quins són els jocs amb més propietaris del 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;

Hauries d'obtenir PLAYERUNKNOWN'S BATTLEGROUNDS (1), Unturned (2), i Robocraft i Brawlhalla compartint la posició 3: empaten a 10,000,000.

Pas 5 — Escriu una raó per la qual est_revenue no és el que un joc va guanyar realment. Si en saps escriure una, has entès la lliçó.

Idees clau¶

• Una variable sintètica és una columna que ningú no ha recollit: es calcula a partir de les que sí. És honesta mentre porti la seva recepta.
• CREATE TABLE ... AS SELECT fa permanent la neteja a games_clean (26,685 files). Una columna generada com release_year la calcula la mateixa base de dades.
• Una columna calculada dona un valor per fila (owners_min * price). Una mètrica dona un valor per grup (SUM(owners_min * price)).
• Una mètrica ha d'usar l'expressió completa: les columnes calculades de Superset no existeixen a la taula de PostgreSQL.
• Una funció de finestra (OVER, PARTITION BY) escriu el valor d'un grup al costat de cada fila. COUNT(*) / SUM(COUNT(*)) OVER () és una proporció del total.
• est_revenue és un mínim, no uns ingressos: una variable només és tan bona com les columnes amb què s'ha fet.

Comprovació ràpida¶

games_clean ja existeix, amb columnes que mai no van ser al fitxer. A continuació reconstruiràs la mateixa neteja sense escriure ni una línia d'SQL: Power Query, on cliques i l'eina escriu la recepta per tu.