SQL Lab: grupos¶

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

¿Cuántos Pokémon de fuego hay? Sabes cómo averiguarlo: una consulta con WHERE type_1 = 'fire', y la respuesta es 59. Ahora haz agua. Luego planta. Luego los otros quince tipos.

Dieciocho consultas para una pregunta pequeña. Nadie quiere eso. Y las preguntas que la gente hace de verdad a los datos casi nunca son "enséñame las cartas". Son cuántos, cuánto, cuál es la media, qué grupo gana. En la última lección cada respuesta era una lista de cartas. En esta, la respuesta es un número sobre muchas cartas, y después el mismo número para todos los grupos a la vez.

Un número para toda la caja¶

Una función de agregación lee muchas filas y devuelve un valor.

Mantenemos la caja de zapatos de SQL Lab: filtros: la tabla pokemon es una caja con 949 cartas coleccionables, una fila es una carta, una columna es una línea impresa en ella. Una función de agregación lee un montón entero de cartas y dice un número.

Abre SQL → SQL Lab (base de datos Dades classe, esquema public) y ejecuta:

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

Entraron 949 cartas. Salió una fila. Esa es la marca de una agregación: por grande que sea la tabla, obtienes una sola línea.

La media es correcta e ilegible. Dos herramientas pequeñas lo arreglan:

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

• ROUND(valor, 1) redondea a un decimal. ROUND(valor) redondea a un número entero.
• AS da a la columna del resultado un nombre que eliges tú. Ese nombre se llama alias.

Una agregación funciona junto con WHERE tal como esperarías: primero se sacan las cartas, después se cuentan. SELECT COUNT(*) FROM pokemon WHERE type_1 = 'fire' devuelve 59.

COUNT(*) no es COUNT(columna)¶

La misma tabla, tres recuentos:

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

949 y 510, de la misma caja. ¿Quién miente? Nadie. Responden a preguntas distintas:

Y 949 − 510 = 439: los Pokémon de un solo tipo que encontraste con IS NULL en la lección anterior.

GROUP BY: haz montones¶

GROUP BY divide las filas en grupos, un grupo por cada valor distinto de una columna, y devuelve una fila de resultado por grupo.

Coge una baraja y repártela en montones, uno por palo. ¿Cuántos montones? Uno por cada palo distinto. Ahora cuenta cada montón. ¿Cuántos números obtienes? Uno por montón.

Eso es todo lo que hace GROUP BY: un montón por valor distinto, una fila de resultado por montón.

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

18 filas. Las tres primeras y la última:

Las dieciocho consultas del principio de la página, en cuatro líneas. ¿Recuerdas SELECT DISTINCT type_1? También devolvía 18 filas. Los mismos montones; ahora además se cuenta cada montón.

Puedes pedir varias cifras de cada montón a la vez:

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 caja</b><br>949 cartas"] --> B["<b>GROUP BY type_1</b><br>repártelas en montones"]
    B --> P1["water<br>126 cartas"]
    B --> P2["normal<br>111 cartas"]
    B --> P3["...16 montones más"]
    P1 --> R["<b>Resultado</b><br>18 filas:<br>una por montón"]
    P2 --> R
    P3 --> R

El error que todo el mundo encuentra¶

Añade el nombre del Pokémon a la consulta anterior:

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

Léelo con los montones delante. El montón de agua tiene 126 cartas. ¿Qué un nombre debería imprimir la base de datos para todo el montón? No puede elegir, así que se niega.

La regla: en una consulta agrupada, cada columna de SELECT es aquello por lo que has hecho los montones, o va dentro de una función de agregación.

HAVING: tira montones enteros¶

HAVING se queda solo con los grupos para los que una condición es verdadera. Es un filtro para montones, no para cartas.

Mira otra vez la última fila del primer resultado: flying, 4 cartas. Una media de cuatro cartas no vale gran cosa. Quédate solo con los montones grandes:

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;

Quedan ocho montones. dragon, el ganador hace un momento, ha desaparecido: solo tiene 39 cartas.

¿WHERE o HAVING? Cartas o montones¶

Esta es la idea que más se confunde. Las dos líneas filtran. Filtran cosas distintas, en momentos distintos.

Por eso esto falla:

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

ERROR:  aggregate functions are not allowed in WHERE

Cuando se ejecuta WHERE, todavía no hay montones. No hay nada que contar.

Una consulta puede usar las dos. Ataque medio por tipo, solo los tipos con al menos 50 Pokémon, ignorando las evoluciones 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 es una Mega" es una pregunta sobre cada carta: WHERE. "Tiene al menos 50" es una pregunta sobre cada montón: HAVING. Compara con la tabla anterior: rock bajó de 90 a 87 y fire de 84 a 81. Las evoluciones Mega son muy fuertes, así que estaban subiendo las medias.

El orden real de una consulta¶

Ya conoces siete líneas. Las escribes en un orden. La base de datos las ejecuta en otro, y ese orden explica todas las reglas de esta página.

flowchart LR
    F["<b>1 · FROM</b><br>abre la caja"] --> W["<b>2 · WHERE</b><br>saca cartas"]
    W --> G["<b>3 · GROUP BY</b><br>haz montones"]
    G --> H["<b>4 · HAVING</b><br>tira montones enteros"]
    H --> S["<b>5 · SELECT</b><br>lee y calcula"]
    S --> O["<b>6 · ORDER BY</b><br>ordena"]
    O --> L["<b>7 · LIMIT</b><br>quédate con lo de arriba"]

WHERE se ejecuta antes de que existan los montones, así que no puede contarlos. HAVING se ejecuta antes que SELECT, así que no conoce tus alias. ORDER BY se ejecuta después de SELECT, así que sí.

Pruébalo: ¿qué tipo gana?¶

Una consulta por pregunta, en SQL Lab. Escribe primero la tuya y luego compara.

La pregunta 4 deja dos cabos sueltos. ¿Quiénes son las 147 cartas sin generación? ¿Y qué es la generación 5: qué región, qué año? La tabla tiene un número, pero no lo que significa.

Ideas clave¶

• Una función de agregación (COUNT, SUM, AVG, MIN, MAX) convierte muchas filas en un valor. ROUND lo ordena, AS le pone nombre con un alias.
• COUNT(*) cuenta filas; COUNT(columna) cuenta las filas donde esa columna no está en blanco. Las agregaciones se saltan NULL.
• GROUP BY hace un montón por cada valor distinto y devuelve una fila por montón. No ordena, y los blancos forman su propio montón.
• En una consulta agrupada, cada columna de SELECT es una clave de montón o está dentro de una agregación.
• WHERE filtra cartas antes de hacer los montones; HAVING filtra montones después de contarlos.
• La base de datos ejecuta FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

Comprobación rápida¶

La generación 5 tiene 156 Pokémon, pero ¿qué región es la generación 5? Eso está escrito en otra tabla. Siguiente: SQL Lab: joins, donde lees dos tablas como una, y las 147 cartas dan problemas.