SQL Lab: filtres¶

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

Algú t'allarga una capsa de sabates amb 949 cartes col·leccionables de Pokémon i et demana: quin és el més ràpid de tipus elèctric? No llegeixes en veu alta les 949 cartes. Treus les elèctriques, les ordenes per velocitat, mires la carta de dalt de tot i en llegeixes el nom.

Quatre moviments amb les mans. En una base de dades aquests quatre moviments tenen nom, i junts formen una frase que la base de dades entén. A la lliçó anterior vas connectar Superset a la base de dades de classe i vas pujar la taula pokemon. Ara aprens a demanar-li menys: menys columnes, menys files, en un ordre útil. En el joc de crafting d'aquest mòdul encara ets a l'etapa de farm i loot, però un bon jugador no es duu a casa tot el mapa, només el que necessita la recepta.

Una capsa de sabates amb 949 cartes¶

Una consulta és una pregunta escrita en un llenguatge que la base de dades entén. Aquest llenguatge és SQL (Structured Query Language).

Una sola analogia per a tota la pàgina: la taula pokemon és una capsa de sabates plena de cartes col·leccionables.

La capsa conté 949 cartes, i cada carta té 22 línies impreses. En aquesta lliçó en fem servir vuit:

Obre SQL → SQL Lab a Superset, tria la base de dades Dades classe i l'esquema public, i ja pots escriure. Executes una consulta amb el botó Run o amb Ctrl+Enter.

La forma d'una consulta¶

Una consulta té fins a cinc línies, sempre en aquest ordre. Cadascuna és un moviment de les mans.

flowchart LR
    A["<b>FROM</b><br>obre la capsa:<br>949 cartes"] --> B["<b>WHERE</b><br>treu les cartes<br>que vols"]
    B --> C["<b>ORDER BY</b><br>ordena el munt"]
    C --> D["<b>LIMIT</b><br>guarda les de dalt"]
    D --> E["<b>SELECT</b><br>llegeix només les línies<br>que has demanat"]

Aquí tens la pregunta de dalt de tot de la pàgina, escrita com a consulta:

SELECT pokemon, speed
FROM pokemon
WHERE type_1 = 'electric'
ORDER BY speed DESC
LIMIT 1;

Una fila, dues columnes. La resposta, i res més. Les seccions següents construeixen aquesta frase línia per línia.

SELECT: llegeix només les línies que necessites¶

SELECT tria les columnes del resultat. FROM indica la taula de la qual surten.

SELECT * FROM pokemon et dona les 22 columnes de les 949 files. Va bé per fer una primera ullada, i no serveix com a resposta. Posa-hi el nom de les columnes:

SELECT pokemon, type_1, attack FROM pokemon;

Continua retornant 949 files, però només tres columnes. Les quatre primeres:

WHERE: treu cartes de la capsa¶

WHERE conserva només les files per a les quals una condició és certa. Una condició és una pregunta de sí o no que es fa a cada fila, una per una.

SELECT pokemon, type_1, attack FROM pokemon WHERE type_1 = 'fire';

Això retorna 59 files: charmander, charmeleon, charizard, vulpix... La condició type_1 = 'fire' s'ha preguntat 949 vegades, i 59 cartes han dit que sí.

El = és un operador de comparació. N'hi ha sis:

Dues regles causen la majoria dels errors la primera setmana d'SQL:

Dues condicions: AND, OR¶

Un operador lògic uneix dues condicions. AND: les dues han de ser certes. OR: n'hi ha prou amb una.

SELECT pokemon, type_1, type_2 FROM pokemon
WHERE type_1 = 'dragon' AND type_2 = 'flying';

Sis files: dragonite, altaria, salamence, rayquaza i les formes Mega dels dos últims. Canvia AND per OR i pregunta alguna cosa més àmplia:

SELECT pokemon, type_1, type_2 FROM pokemon
WHERE type_1 = 'dragon' OR type_2 = 'dragon';

61 files: tots els Pokémon que són de tipus drac en qualsevol de les dues ranures. AND fa un resultat més petit, OR el fa més gran.

ORDER BY i LIMIT: ordena el munt, guarda el de dalt¶

ORDER BY ordena el resultat per una columna. LIMIT conserva només les primeres files.

SELECT pokemon, type_1, attack FROM pokemon
WHERE attack > 150
ORDER BY attack DESC;

19 files, primer els més forts. Juntes, aquestes dues línies responen qualsevol pregunta de «top N»:

SELECT pokemon, attack FROM pokemon ORDER BY attack DESC LIMIT 5;

Mira les dues últimes files. Tres Pokémon tenen un atac de 180: deoxys-attack, groudon-primal i rayquaza-mega. Només n'hi caben dos en un top 5, i la base de dades ha triat com ha volgut. La teva pantalla pot mostrar un altre parell, i tots dos tindríeu raó.

DISTINCT: conèixer una columna¶

DISTINCT elimina les files repetides del resultat, de manera que cada valor diferent apareix una sola vegada.

Abans de filtrar per una columna, esbrina quins valors pot tenir:

SELECT DISTINCT type_1 FROM pokemon ORDER BY type_1;

18 files, de bug a water: els 18 tipus, cadascun una vegada. Ara saps que el tipus s'escriu fire, no Fire ni FIRE. DISTINCT és el remei de la trampa de zero files de dalt.

Quatre filtres especials¶

Els operadors de comparació cobreixen els nombres i el text exacte. Quatre filtres més cobreixen la resta.

LIKE fa servir dos comodins, símbols que representen text que no coneixes: % és qualsevol text (també cap), _ és exactament un caràcter. 'pika%' vol dir «comença per pika»: pikachu i les seves dotze disfresses (pikachu-rock-star, pikachu-phd...).

IN és una forma abreujada de diversos OR: type_1 = 'fire' OR type_1 = 'water' OR type_1 = 'grass'. Posa-hi NOT davant per al contrari: type_1 NOT IN ('fire', 'water', 'grass') retorna 680 files, i 269 + 680 = 949. Ningú s'ha perdut.

BETWEEN inclou els dos extrems: speed BETWEEN 100 AND 120 és el mateix que speed >= 100 AND speed <= 120. Tots dos retornen 126. Amb > i < n'obtindries 83, perquè els Pokémon que tenen exactament 100 i exactament 120 queden fora.

IS NULL troba els blancs: els 439 Pokémon que només tenen un tipus. IS NOT NULL troba els altres 510. Aquest es mereix una secció pròpia.

NULL: la línia en blanc de la carta¶

NULL vol dir desconegut: aquí no hi ha res escrit. No és zero, i no és un text buit.

Executa això i compta:

SELECT pokemon, type_1, type_2 FROM pokemon WHERE type_2 = NULL;

0 files. Cap error. Han desaparegut els 439 Pokémon d'un sol tipus?

No. Pensa en dues cartes amb una línia en blanc. Els dos blancs són iguals? No ho pots saber: potser hi hauria d'haver alguna cosa, i no saps què. Per tant, quan compares qualsevol cosa amb NULL, la base de dades respon «desconegut», i WHERE només conserva les files on la resposta és un sí clar.

Per això SQL té una pregunta a part per als blancs. Recorda-ho: IS NULL, mai = NULL.

Respostes errònies que semblen correctes¶

Totes les trampes d'aquesta pàgina tenen una cosa en comú: cap no dona error. La base de dades fa exactament el que has escrit i retorna un nombre creïble.

La tercera, amb detall. Vols els Pokémon de foc o d'aigua amb un atac superior a 100:

SELECT COUNT(*) FROM pokemon
WHERE type_1 = 'fire' OR type_1 = 'water' AND attack > 100;

79. La base de dades ho ha llegit com «tots els Pokémon de foc (59), més els d'aigua amb atac superior a 100 (20)». Posa parèntesis al voltant de la part de l'OR:

SELECT COUNT(*) FROM pokemon
WHERE (type_1 = 'fire' OR type_1 = 'water') AND attack > 100;

34. Les mateixes paraules, una altra pregunta. (COUNT(*) compta files; és l'entrada de la lliçó següent.)

Prova-ho: la cacera de la Pokédex¶

Obre SQL Lab i respon cada pregunta amb una sola consulta. Escriu primer la consulta, després obre la solució i compara el teu número.

Si SQL Lab respon amb un quadre vermell, llegeix el missatge: normalment diu la paraula amb què ha ensopegat. Mira just abans d'aquesta paraula per veure si falta una cometa o hi ha una paraula clau mal escrita. I executa una sola consulta cada vegada: si n'hi ha diverses a l'editor, només veus el resultat de l'última.

Idees clau¶

• Una consulta té una forma fixa: SELECT (columnes) · FROM (taula) · WHERE (files) · ORDER BY (ordre) · LIMIT (retall).
• WHERE pregunta una condició a cada fila. El text va entre cometes simples i distingeix majúscules i minúscules; els nombres no porten cometes.
• AND estreny, OR eixampla. AND s'aplica primer: quan els barrejes, fes servir parèntesis.
• ORDER BY ... DESC LIMIT n respon «top N», però LIMIT talla els empats arbitràriament.
• DISTINCT mostra els valors diferents d'una columna: fes-lo servir abans de filtrar.
• LIKE coincideix amb un patró, IN amb una llista, BETWEEN amb un interval amb els dos extrems, IS NULL amb un blanc. = NULL no és mai cert.
• Els errors perillosos no donen error. Retornen un nombre que sembla correcte.

Comprovació ràpida¶

Fins ara cada resposta era una llista de cartes. I si la pregunta és «quants Pokémon de foc hi ha?», i després el mateix per als 18 tipus alhora? Tot seguit: SQL Lab: grups.