SQL Lab: grups¶

Exercici sql-lab-groups-annex.pdf · PDF

Quants Pokémon de foc hi ha? Ja saps com esbrinar-ho: una consulta amb WHERE type_1 = 'fire', i la resposta és 59. Ara fes-ho amb l'aigua. Després amb l'herba. Després amb els altres quinze tipus.

Divuit consultes per a una pregunta petita. Ningú no ho vol. I les preguntes que de debò es fan sobre les dades gairebé mai no són «ensenya'm les cartes». Són quantes, quant, quina és la mitjana, quin grup guanya. A la lliçó anterior cada resposta era una llista de cartes. En aquesta, la resposta és un nombre sobre moltes cartes, i després el mateix nombre per a cada grup alhora.

Un nombre per a tota la capsa¶

Una funció d'agregació llegeix moltes files i retorna un valor.

Mantenim la capsa de sabates de SQL Lab: filtres: la taula pokemon és una capsa de 949 cartes col·leccionables, una fila és una carta, una columna és una línia impresa a la carta. Una funció d'agregació llegeix un munt sencer de cartes i diu un sol nombre.

Obre SQL → SQL Lab (base de dades Dades classe, esquema public) i executa:

SELECT MIN(attack), MAX(attack), SUM(attack), AVG(attack) FROM pokemon;

Hi han entrat 949 cartes. N'ha sortit una sola fila. Aquesta és la marca d'una agregació: per gran que sigui la taula, obtens una sola línia.

La mitjana és correcta i il·legible. Dues eines petites ho arreglen:

SELECT ROUND(AVG(attack), 1) AS avg_attack FROM pokemon;

• ROUND(value, 1) arrodoneix a un decimal. ROUND(value) arrodoneix a un nombre enter.
• AS dona a la columna del resultat un nom que tries tu. Aquest nom s'anomena àlies.

Una agregació funciona amb WHERE tal com t'esperes: primer es treuen les cartes, després es compten. SELECT COUNT(*) FROM pokemon WHERE type_1 = 'fire' retorna 59.

COUNT(*) no és COUNT(column)¶

La mateixa taula, tres recomptes:

SELECT COUNT(*) AS total,
       COUNT(type_2) AS with_second_type,
       COUNT(DISTINCT type_1) AS types
FROM pokemon;

949 i 510, de la mateixa capsa. Qui menteix? Ningú. Responen preguntes diferents:

I 949 − 510 = 439: els Pokémon d'un sol tipus que vas trobar amb IS NULL a la lliçó anterior.

GROUP BY: fer munts¶

GROUP BY divideix les files en grups, un grup per cada valor diferent d'una columna, i retorna una fila de resultat per grup.

Agafa una baralla de cartes i reparteix-la en munts, un munt per pal. Quants munts? Un per cada pal diferent. Ara compta cada munt. Quants nombres obtens? Un per munt.

Això és tot el que fa GROUP BY: un munt per cada valor diferent, una fila de resultat per munt.

SELECT type_1, COUNT(*) AS n
FROM pokemon
GROUP BY type_1
ORDER BY n DESC;

18 files. Les tres primeres i l'última:

Les divuit consultes de dalt de tot de la pàgina, en quatre línies. Recordes SELECT DISTINCT type_1? També retornava 18 files. Els mateixos munts; ara, a més, cada munt es compta.

Pots demanar diverses xifres de cada munt alhora:

SELECT type_1, COUNT(*) AS n, ROUND(AVG(attack)) AS avg_attack
FROM pokemon
GROUP BY type_1
ORDER BY avg_attack DESC;

flowchart LR
    A["<b>La capsa</b><br>949 cartes"] --> B["<b>GROUP BY type_1</b><br>reparteix-les en munts"]
    B --> P1["water<br>126 cartes"]
    B --> P2["normal<br>111 cartes"]
    B --> P3["...16 munts més"]
    P1 --> R["<b>Resultat</b><br>18 files:<br>una per munt"]
    P2 --> R
    P3 --> R

L'error que es troba tothom¶

Afegeix el nom del Pokémon a la consulta de dalt:

SELECT type_1, pokemon, COUNT(*) FROM pokemon GROUP BY type_1;

ERROR:  column "pokemon.pokemon" must appear in the GROUP BY clause
        or be used in an aggregate function

Llegeix-lo amb els munts al davant. El munt d'aigua té 126 cartes. Quin un nom ha d'imprimir la base de dades per a tot el munt? No pot triar-ne, així que es nega.

La regla: en una consulta agrupada, cada columna de SELECT és o bé allò pel qual has fet els munts, o bé és dins d'una funció d'agregació.

HAVING: llença munts sencers¶

HAVING conserva només els grups per als quals una condició és certa. És un filtre per a munts, no per a cartes.

Mira un altre cop l'última fila del primer resultat: flying, 4 cartes. Una mitjana de quatre cartes no val gaire. Queda't només amb els munts grans:

SELECT type_1, COUNT(*) AS n, ROUND(AVG(attack)) AS avg_attack
FROM pokemon
GROUP BY type_1
HAVING COUNT(*) >= 50
ORDER BY avg_attack DESC;

Queden vuit munts. dragon, el guanyador fa un moment, ha desaparegut: només té 39 cartes.

WHERE o HAVING? Cartes o munts¶

Aquesta és la idea que la gent confon més. Les dues línies filtren. Filtren coses diferents, en moments diferents.

Per això falla això:

SELECT type_1, COUNT(*) FROM pokemon WHERE COUNT(*) >= 50 GROUP BY type_1;

ERROR:  aggregate functions are not allowed in WHERE

Quan s'executa WHERE, encara no hi ha munts. No hi ha res per comptar.

Una consulta pot fer servir totes dues. Atac mitjà per tipus, només tipus amb almenys 50 Pokémon, ignorant les evolucions Mega:

SELECT type_1, COUNT(*) AS n, ROUND(AVG(attack)) AS avg_attack
FROM pokemon
WHERE pokemon NOT LIKE '%-mega%'
GROUP BY type_1
HAVING COUNT(*) >= 50
ORDER BY avg_attack DESC;

«No és una Mega» és una pregunta sobre cada carta: WHERE. «Té almenys 50» és una pregunta sobre cada munt: HAVING. Compara-ho amb la taula anterior: rock ha baixat de 90 a 87 i fire de 84 a 81. Les evolucions Mega són molt fortes, així que feien pujar les mitjanes.

L'ordre real d'una consulta¶

Ara ja coneixes set línies. Les escrius en un ordre. La base de dades les executa en un altre, i aquest ordre explica totes les regles d'aquesta pàgina.

flowchart LR
    F["<b>1 · FROM</b><br>obre la capsa"] --> W["<b>2 · WHERE</b><br>treu cartes"]
    W --> G["<b>3 · GROUP BY</b><br>fes munts"]
    G --> H["<b>4 · HAVING</b><br>llença munts sencers"]
    H --> S["<b>5 · SELECT</b><br>llegeix i calcula"]
    S --> O["<b>6 · ORDER BY</b><br>ordena"]
    O --> L["<b>7 · LIMIT</b><br>guarda el de dalt"]

WHERE s'executa abans que existeixin els munts, així que no els pot comptar. HAVING s'executa abans que SELECT, així que no coneix els teus àlies. ORDER BY s'executa després de SELECT, així que sí que els coneix.

Prova-ho: quin tipus guanya?¶

Una consulta per pregunta, a SQL Lab. Escriu la teva primer i després compara.

La pregunta 4 deixa dos fils per lligar. Qui són les 147 cartes sense generació? I què és la generació 5: quina regió, quin any? La taula té un número, però no què significa.

Idees clau¶

• Una funció d'agregació (COUNT, SUM, AVG, MIN, MAX) converteix moltes files en un valor. ROUND l'endreça, AS el nomena amb un àlies.
• COUNT(*) compta files; COUNT(column) compta les files on aquella columna no és en blanc. Les agregacions se salten NULL.
• GROUP BY fa un munt per cada valor diferent i retorna una fila per munt. No ordena, i els blancs formen el seu propi munt.
• En una consulta agrupada, cada columna de SELECT és una clau de munt o és dins d'una agregació.
• WHERE filtra cartes abans de fer els munts; HAVING filtra munts després de comptar-los.
• La base de dades executa FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

Comprovació ràpida¶

La generació 5 té 156 Pokémon, però quina regió és la generació 5? Això està escrit en una altra taula. Tot seguit: SQL Lab: joins, on llegiràs dues taules com si fossin una, i les 147 cartes donen problemes.