Capítulo 12 — Consultas que molan (combinaciones y agregados)

Recordatorio: combinar dos tablas

Antes de subir de nivel, un repaso rápido del capítulo 5. Un JOIN (combinación) une dos tablas emparejando sus filas según una condición. Querías ver qué cartas tiene cada jugador en su colección, pero los nombres estaban en carta y las cantidades en coleccion. Los juntaste así:

SELECT jugador.alias, carta.nombre, coleccion.cantidad
FROM jugador
JOIN coleccion ON jugador.id = coleccion.jugador_id
JOIN carta     ON coleccion.carta_id = carta.id;

La pieza clave es el ON: le dice a la base de datos cómo se emparejan las filas (la clave foránea de un lado contra la clave primaria del otro). Sin ON, la base de datos combina todas las filas con todas y sale un disparate (el producto cartesiano del que avisamos en el capítulo 5).

Ese era el JOIN para resolver una relación N:M. Ahora vamos a usar la misma herramienta para preguntas más ambiciosas.

Combinar tres o más tablas

La buena noticia es que no hay truco nuevo. Para unir más tablas, sigues encadenando JOIN … ON, una pareja cada vez. La base de datos las va juntando en orden.

Una pregunta natural en Kriaturas: ¿qué cartas lleva cada mazo, y de quién es ese mazo? Esa información está repartida en cuatro tablas: el nombre del jugador en jugador, el nombre del mazo en mazo, qué cartas lleva en mazo_carta y el nombre de cada carta en carta. Las encadenas todas:

SELECT jugador.alias, mazo.nombre, carta.nombre, mazo_carta.cantidad
FROM jugador
JOIN mazo       ON mazo.jugador_id = jugador.id
JOIN mazo_carta ON mazo_carta.mazo_id = mazo.id
JOIN carta      ON mazo_carta.carta_id = carta.id;

Léelo como un camino. Arrancas en jugador. Saltas a sus mazo (por jugador_id). De cada mazo saltas a sus cartas en mazo_carta (por mazo_id). Y de cada una saltas a la carta para sacar su nombre (por carta_id). Cada JOIN añade un eslabón a la cadena.

Con el reparto de Kriaturas, el resultado es:

Cuatro tablas, una sola consulta, y una respuesta legible que no vivía en ninguna de ellas por separado. Eso es cruzar datos. Fíjate en que dos columnas se llaman nombre (la del mazo y la de la carta): por eso conviene escribir delante de qué tabla viene cada una (mazo.nombre, carta.nombre), para no liarte ni liar a la base de datos.

Un apunte de comodidad. Escribir mazo_carta.cantidad cada vez cansa. SQL permite dar a cada tabla un alias corto en el propio FROM, igual que dabas alias a las columnas en el capítulo 3. Así, FROM jugador j JOIN mazo m ON m.jugador_id = j.id te deja escribir j.alias y m.nombre. Es la misma consulta, más corta. Verás este estilo por todas partes en el mundo real.

Resumir muchas filas en un número: las funciones de agregado

Hasta ahora cada consulta devolvía filas: una por jugador, una por carta. Pero muchas preguntas no quieren una lista, quieren un número. "¿Cuántas cartas épicas hay?" no se responde con una lista de cartas, sino con una cuenta.

Para eso están las funciones de agregado: operaciones que toman un montón de filas y las resumen en un solo valor. Son cinco, y con su nombre casi adivinas qué hacen:

• COUNT — cuenta filas.
• SUM — suma los valores de una columna.
• AVG — calcula la media (average).
• MAX — el valor máximo.
• MIN — el valor mínimo.

La más sencilla, contar. ¿Cuántas cartas épicas hay en el juego?

SELECT COUNT(*) AS cartas_epicas
FROM carta
WHERE rareza = 'épica';

Solo Hojarasca, en el reparto actual. La función COUNT(*) cuenta las filas que pasan el filtro WHERE, y devuelve el total. Un único número, no una lista.

Las cinco funcionan igual. Esta consulta resume de un vistazo todo el historial de partidas:

SELECT COUNT(*)      AS total_partidas,
       AVG(duracion) AS duracion_media,
       MAX(duracion) AS mas_larga,
       MIN(duracion) AS mas_corta
FROM partida;

Con las cuatro partidas del historial (duraciones de 12, 18, 9 y 15 minutos), la base de datos cuenta cuántas hay, promedia sus duraciones, y saca la más larga y la más corta. Esa fila es, ni más ni menos, la pantalla de estadísticas del juego.

Contar por grupos: `GROUP BY`

Aquí viene el salto importante. COUNT(*) sobre toda la tabla partida te da el total de partidas. Pero la pregunta que de verdad interesa es otra: ¿cuántas partidas ganó cada jugador? No quieres un número, quieres un número por jugador.

Eso es exactamente lo que hace GROUP BY. Agrupa las filas que comparten un valor y aplica el agregado a cada grupo por separado. Mira la tabla partida por su columna ganador:

SELECT ganador, COUNT(*) AS victorias
FROM partida
GROUP BY ganador
ORDER BY victorias DESC;

Paso a paso, esto le dice a la base de datos: "junta las partidas que tienen el mismo ganador, y para cada grupo cuéntame cuántas hay". El resultado, con id de jugador en vez de nombre por ahora:

El jugador 1 ganó dos partidas; el 2 y el 4, una cada uno. Eso es el ranking. Y es, palabra por palabra, el cálculo del atributo derivado que te debíamos desde el capítulo 8: las victorias no estaban guardadas en ninguna columna, las acabas de contar tú.

Solo falta un detalle para que quede presentable: nadie quiere ver id numéricos en un ranking, quiere nombres. Combínalo con un JOIN a jugador, que ya sabes hacer:

SELECT jugador.alias, COUNT(*) AS victorias
FROM partida
JOIN jugador ON partida.ganador = jugador.id
GROUP BY jugador.alias
ORDER BY victorias DESC;

Ahí lo tienes: el ranking de Kriaturas, con nombres, ordenado de más a menos victorias, calculado al vuelo desde el historial. Una pantalla entera de tu juego en cinco líneas de SQL.

Pero mira con atención y verás algo raro. PixelPunk no aparece. Y no es un error: Pixel no ha ganado ninguna partida, así que no figura como ganador en ninguna fila de partida, así que el JOIN no encuentra nada que emparejar y se lo salta. El ranking solo enseña a quien ha ganado al menos una vez. A veces es justo lo que quieres. Y a veces no. Guarda esa pega: la resolvemos en un momento.

Filtrar grupos: `HAVING`

Imagina que solo te interesan los jugadores destacados, los que llevan más de una victoria. Tu instinto, después del capítulo 3, será echar mano de WHERE. Pero hay un problema de fondo, y conviene entenderlo bien.

WHERE filtra filas, y lo hace antes de agrupar. En el momento en que WHERE actúa, los grupos todavía no existen, así que no puede preguntar "¿cuántas victorias tiene este grupo?". Esa cuenta aún no está hecha.

Para filtrar grupos ya formados existe HAVING. Es como WHERE, pero entra en escena después de agrupar, cuando los agregados ya están calculados:

SELECT jugador.alias, COUNT(*) AS victorias
FROM partida
JOIN jugador ON partida.ganador = jugador.id
GROUP BY jugador.alias
HAVING COUNT(*) > 1;

Solo Drago, el único con más de una victoria. La regla para no confundirlos es simple: WHERE filtra filas antes de agrupar; HAVING filtra grupos después. Si la condición habla de una columna normal (rareza = 'épica'), es WHERE. Si habla de un agregado (COUNT(*) > 1), es HAVING.

Y los que no tienen pareja, ¿qué? La combinación externa

Volvamos a la pega de antes: PixelPunk no salía en el ranking. El JOIN normal —al que ahora llamaremos por su nombre completo, combinación interna— solo devuelve filas que casan en los dos lados. Si un jugador no tiene ninguna victoria, no hay fila en partida que emparejar, y desaparece del resultado.

Para esos casos existe la combinación externa (outer join). La variante más usada es LEFT JOIN (o LEFT OUTER JOIN): devuelve todas las filas de la tabla de la izquierda, tengan pareja o no. A las que no la tienen, les rellena los huecos con NULL (ese "aquí no hay dato" del capítulo 11).

Para un ranking que incluya a todos los jugadores, empiezas por jugador (que los tiene todos) y haces LEFT JOIN con partida:

SELECT jugador.alias, COUNT(partida.ganador) AS victorias
FROM jugador
LEFT JOIN partida ON jugador.id = partida.ganador
GROUP BY jugador.alias
ORDER BY victorias DESC;

Ahora sí: Pixel aparece, con su honesto 0. La tabla de la izquierda (jugador) manda, así que nadie se queda fuera.

Hay un detalle fino y muy importante aquí, y es la diferencia entre COUNT(partida.ganador) y COUNT(*). Para Pixel, el LEFT JOIN crea una fila con los datos de partida a NULL (no jugó ninguna que ganara). COUNT(*) contaría esa fila igual y le daría un 1 falso. Pero COUNT(de una columna) no cuenta los NULL: solo cuenta valores reales. Por eso COUNT(partida.ganador) le da a Pixel el 0 que merece. Cuando uses LEFT JOIN con COUNT, cuenta siempre una columna del lado que puede faltar, no *.

Una pregunta dentro de otra: las subconsultas

Última herramienta del capítulo, y de las más elegantes. A veces el valor que buscas es, él mismo, el resultado de otra consulta. Necesitas hacer una pregunta para poder hacer la de verdad.

Ejemplo: ¿cuál es la carta de mayor coste del juego? Si supieras el coste máximo, filtrarías por él con un WHERE y listo. Pero no lo sabes de memoria. Lo que sí sabes es cómo calcularlo: SELECT MAX(coste) FROM carta. Una subconsulta te deja meter esa pregunta dentro de la otra:

SELECT nombre
FROM carta
WHERE coste = (SELECT MAX(coste) FROM carta);

Léelo de dentro hacia fuera. Primero la base de datos resuelve la subconsulta (los paréntesis): MAX(coste) da 4. Luego ejecuta la de fuera como si hubieras escrito WHERE coste = 4. Y devuelve Hojarasca, la carta más cara del reparto. La gracia es que no has tenido que averiguar el 4 tú: lo calcula la propia consulta, y seguirá funcionando aunque mañana metas una carta de coste 9.

Las subconsultas también encajan bien con la idea de "pertenecer a un conjunto". ¿Qué jugadores han ganado al menos una partida? La subconsulta saca todos los ganador distintos, y IN se queda con los jugadores que están en esa lista:

SELECT alias
FROM jugador
WHERE id IN (SELECT ganador FROM partida);

Otra vez Pixel se queda fuera (no está entre los ganadores), igual que en el JOIN interno. De hecho, muchas preguntas se pueden plantear de las dos formas, con JOIN o con subconsulta; con el tiempo elegirás la que se lea más clara en cada caso. De momento quédate con la idea: una subconsulta es una pregunta cuya respuesta necesitaba otra pregunta antes.

Resumen

En este capítulo tus datos pasaron de estar bien guardados a responder preguntas. Repasaste el JOIN del capítulo 5 y lo estiraste para combinar tres y cuatro tablas encadenando JOIN … ON. Conociste las cinco funciones de agregado (COUNT, SUM, AVG, MAX, MIN), que resumen muchas filas en un número, y GROUP BY, que las aplica grupo a grupo. Con eso construiste el ranking de Kriaturas y, de paso, cerraste el hilo del atributo derivado del capítulo 8: el rango no se guarda, se cuenta. Aprendiste a filtrar grupos con HAVING (distinto de WHERE), a no dejar fuera a nadie con LEFT JOIN —contando una columna, no *, para que los NULL no engañen— y a anidar una pregunta dentro de otra con subconsultas.

Tus datos ya están ordenados, protegidos y, ahora, exprimidos. Pero queda una promesa sin cumplir. En el capítulo 9 dejaste pendiente un intercambio de cartas seguro: dos jugadores cambian cartas a la vez, y o se hace el cambio entero o no se hace nada, nunca a medias. Esa idea de "todo o nada" tiene nombre, y es la red de seguridad que mantiene la coherencia que tanto te ha costado construir. Es lo que viene en el capítulo 13: las transacciones.