SQL sobre eventos

5 tareas · 40 min · Principiante

Muchas preguntas de detección se contestan con una consulta: quién falló más veces, desde dónde se probaron muchas cuentas, qué usuario aparece en los eventos y no existe en el directorio. En Bodegas Costa Alta tienes una tabla de eventos de inicio de sesión y una de cuentas, con las consultas ya guardadas y su resultado a la vista. No escribes SQL en el navegador: lees cada sentencia, entiendes qué pregunta hace y compruebas que su resultado sale de las filas. Aprendes a agrupar, a filtrar antes y después de agrupar, a cruzar tablas y a desconfiar de un conteo cuando hay valores vacíos.

0 de 5 · 0%

Objetivo de la sala

GROUP BY reúne las filas que comparten el mismo valor en una columna y permite calcular algo por grupo: cuántas hay, cuántos valores distintos. COUNT(*) cuenta las filas de cada grupo. La condición del WHERE se aplica antes: solo las filas que la cumplen llegan a ser contadas.

Abre la consulta de fallos por usuario en eventos_auth: filtra los eventos con resultado = 'fallo' y los agrupa por usuario. Cada fila del resultado resume un grupo.

Responde para continuar

Escribe cuántos fallos de inicio de sesión acumula `sergio.nava`.

Ver pista de ayuda

Cuenta en la tabla `eventos_auth` las filas de ese usuario cuyo resultado es fallo, sin contar las correctas.

Agrupar por usuario responde «quién falla mucho». Agrupar por origen y contar usuarios distintos responde otra cosa: «desde dónde se probaron muchas cuentas». COUNT(DISTINCT usuario) cuenta cada usuario una sola vez por grupo, aunque haya aparecido muchas veces. Dos orígenes pueden tener el mismo número de intentos y tener una forma de comportamiento muy diferente.

Compara la consulta de usuarios distintos por origen con la anterior. Fíjate en la columna usuarios_distintos, no en intentos.

Responde para continuar

Escribe el origen que probó más usuarios distintos con fallos.

Ver pista de ayuda

Ordena mentalmente la consulta por `usuarios_distintos` y toma el origen de la primera fila.

Hay dos momentos para filtrar. WHERE actúa sobre las filas antes de agrupar; HAVING actúa sobre los grupos ya formados, y por eso puede usar resultados de agregación, como el número de filas del grupo. Un filtro por «cinco o más fallos» no se puede escribir en el WHERE, porque en ese momento el conteo todavía no existe.

Abre la consulta de usuarios con cinco o más fallos y fíjate en dónde está cada condición.

Responde para continuar

En la consulta de usuarios con cinco o más fallos, ¿por qué esa condición va en `HAVING`?

Porque `HAVING` es la forma más rápida de filtrar y el `WHERE` solo se usa con tablas pequeñas Porque depende del conteo de cada grupo, que solo existe después de agrupar las filas Porque `WHERE` no admite números y `fallos` tendría que ser texto

Para encontrar valores de una tabla que no tienen pareja en otra se usa un LEFT JOIN seguido de una condición IS NULL sobre la columna de la tabla de la derecha. El LEFT JOIN conserva todas las filas de la izquierda; donde no hay pareja, las columnas de la derecha quedan vacías. Esa ausencia es lo que se busca.

En detección, la pregunta tiene un valor concreto: un usuario que inicia sesión y no tiene cuenta registrada puede ser una cuenta de servicio olvidada, un directorio sin sincronizar o algo que alguien debe explicar.

Abre la consulta que cruza eventos_auth con cuentas.

Responde para continuar

Escribe el usuario que aparece en los eventos y no tiene cuenta registrada.

Ver pista de ayuda

El resultado de la consulta de cruce tiene una sola fila; compara también con la tabla `cuentas`.

COUNT(*) cuenta filas. COUNT(columna) cuenta solo las filas en las que esa columna tiene valor; los vacíos (NULL) no suman. Es una diferencia pequeña que cambia cifras sin dar ningún error: dos conteos sobre la misma tabla pueden dar números distintos y ambos ser correctos.

Abre la consulta de conteos y compara sus dos números con la tabla.

Responde para continuar

La consulta de conteos devuelve dos cifras distintas sobre la misma tabla. ¿Qué lectura es correcta?

La tabla se modificó entre las dos cuentas, así que las cifras no se pueden comparar con seguridad Una de las dos consultas está mal escrita y hay que repetirla hasta que ambas devuelvan el mismo número `COUNT(origen)` no cuenta las filas con el origen vacío; la diferencia son eventos sin origen registrado

Inicia sesión para registrar tus puntos y progreso en el ranking.

Entrar · Registrarse

1 Agrupar y contar Lab

2 La clave de agrupación cambia la pregunta Lab

3 WHERE o HAVING

4 Lo que está en una tabla y falta en la otra Lab

5 Un conteo que no cuadra

Whoami-Labs Pro utiliza cookies