El tiempo por equipo, en una pregunta
La jefa de sistemas pregunta: ¿cuántas incidencias atiende cada equipo y cuánto tiempo nos cuestan en total?
Las incidencias están en una tabla. Los nombres de los equipos están en otra. La respuesta necesita las dos.
Combinar y agrupar es como una base de datos responde a preguntas así.
Al terminar esta lección
Nota
Puedo combinar tablas y resumirlas.
Palabras clave
| Palabra | Qué significa | Ejemplo |
|---|---|---|
| Join | pone lado a lado filas de dos tablas donde coincide una clave; en español, combinación | |
| Alias | un nombre corto que das a una columna o a una tabla en una consulta | AS total |
| Agregado | una función que convierte muchas filas en un valor | SUM(minutes) |
| GROUP BY | crea una fila de resultado por cada valor distinto | |
| HAVING | deja solo los grupos que pasan una prueba | |
| Left join | un join que conserva todas las filas de la primera tabla |
Las celdas que rellenan los estudiantes quedan en blanco en esta vista previa.
Un join pone dos tablas lado a lado
Los datos se reparten en tablas para no escribir nada dos veces. Un join los vuelve a juntar.
La cláusula ON dice qué columnas coinciden. Suele ser una clave foránea de un lado y una clave primaria del otro.
Si dos tablas tienen una columna con el mismo nombre, escribe antes la tabla: suppliers.city.
Ejecuta las líneas de preparación. Son las mismas de antes.
Aceptar el certificado del servidor
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfSe muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Un nombre corto para la tienda
alias sql='mariadb -h db -u student -pstudent shop -t -e'Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Los productos y de dónde vienen
sql "SELECT products.name, suppliers.city FROM products JOIN suppliers ON suppliers.id = products.supplier_id WHERE suppliers.country = 'Japan' ORDER BY products.name"Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Ejemplo resuelto: coincidir por la clave
| incidents.title | incidents.team_id | teams.id | teams.name |
|---|---|---|---|
| VPN caída | 1 | 1 | Redes |
| Impresora atascada | 2 | 2 | Soporte |
| Correo sospechoso | 3 | 3 | Seguridad |
| Una incidencia con team_id 9 | 9 | no hay fila | La descarta un JOIN simple |
Las celdas que rellenan los estudiantes quedan en blanco en esta vista previa.
GROUP BY da una fila por valor
Un agregado convierte muchas filas en un valor. COUNT(*) cuenta filas. SUM, AVG, MIN y MAX trabajan sobre una columna.
Solo, un agregado resume toda la tabla. Con GROUP BY obtienes un resumen por cada valor distinto de una columna.
Ponle nombre a la respuesta con AS.
Personal y sueldos por puesto
sql "SELECT job_title, COUNT(*) AS staff, SUM(salary) AS payroll FROM employees GROUP BY job_title ORDER BY job_title"Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Primero el join, luego el grupo
sql "SELECT suppliers.country, COUNT(*) AS items, SUM(products.stock) AS units FROM products JOIN suppliers ON suppliers.id = products.supplier_id GROUP BY suppliers.country ORDER BY suppliers.country"Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Pruébalo: resumir por equipo
-- ¿Cuántas incidencias y cuántos minutos tiene cada equipo?
SELECT team_id, COUNT(*) AS cases, SUM(minutes) AS total
FROM incidents
GROUP BY team_id
ORDER BY team_id;
-- Añade HAVING COUNT(*) > 1 antes de ORDER BY.
HAVING prueba un grupo
WHERE prueba filas sueltas, antes de agruparlas. En ese momento todavía no existe ningún total.
HAVING prueba los grupos, una vez formados. Úsalo con un agregado: HAVING COUNT(*) >= 25.
El orden es: WHERE, luego GROUP BY, luego HAVING, luego ORDER BY.
Empleados con 25 pedidos vivos o más
sql "SELECT employee_id, COUNT(*) AS orders FROM orders WHERE status <> 'cancelled' GROUP BY employee_id HAVING COUNT(*) >= 25 ORDER BY orders DESC"Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Un error frecuente
Advertencia
Mucha gente cree que WHERE COUNT(*) >= 25 filtra los grupos. Da error, porque WHERE se ejecuta antes de que exista ningún grupo. Usa WHERE para filas y HAVING para grupos.
El error, ejecutado de verdad
sql "SELECT employee_id, COUNT(*) AS orders FROM orders WHERE COUNT(*) >= 25 GROUP BY employee_id"Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Un left join conserva las filas sin pareja
Un join simple descarta la fila que no tiene pareja. A veces lo que buscas es justo esa falta.
LEFT JOIN conserva todas las filas de la primera tabla. Donde nada coincide, las otras columnas valen NULL.
Añade WHERE ... IS NULL para listar solo las filas sin pareja.
Productos sin categoría
sql "SELECT products.name, categories.name AS category FROM products LEFT JOIN categories ON categories.id = products.category_id WHERE categories.id IS NULL ORDER BY products.name"Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Pruébalo: incidencias junto a su equipo
-- Cada incidencia junto al nombre de su equipo.
SELECT incidents.title, teams.name AS team
FROM incidents
JOIN teams ON teams.id = incidents.team_id
ORDER BY incidents.title;
-- Añade WHERE teams.name = 'Soporte' antes de ORDER BY.
Para ir más allá
En employees, la columna manager_id apunta a otra fila de la misma tabla. Combinar una tabla consigo misma se llama autocombinación (self-join).
Lista el nombre de pila de cada empleado junto al de su responsable. Da a la tabla dos alias, como e y m. La directora general no tiene responsable, así que elige el join que la sigue mostrando.