Creando Valor con SQL (Buenos Ejemplos de Consultas)

Este es el segundo artículo de SQL. El fin es entregar un material práctico, conciso y fácil de entender acerca de como manejar las bases de datos relaciones. Sin más introducción se empezará a mostrar las “queries” o consultas a base de datos que transforman los datos en información cada vez más relevantes.

Siguiendo el ejemplo, y las bases presentadas de este artículo se crearán las consultas a base de datos. Para tenerlas las tablas más a mano aquí están nuevamente ellas.

Tabla cliente: Aquí están todos los clientes registrados en su plataforma.

id_cliente	nombre	email	fecha_de_creación
1	Pedro ko	pko@algo.cl	10/11/2019 11:20:32
2	Juan Ta	jta@algo.cl	11/11/2019 11:22:42
3	jushoue Yte	jyte@algo.com	10/03/2020 11:20:00
4	Johan Re	jre@@algo.com	11/11/2020 11:20:32

Tabla acciones_plataforma: Tabla donde cada fila o registro indica una posible acción en su plataforma.

id_accion	nombre_accion
1	log-in
2	ver producto

Tabla registro_acciones: Tabla que muestra efectivamente cada acción realizada por sus usuarios.

id_registro	id_cliente	id_accion	Fecha_acción
100	1	1	10/03/2020 11:20:01
101	2	1	10/03/2020 11:20:02
102	1	2	10/03/2020 11:20:03

Antes de ver las respuestas, trate de taparlas y escribir las consultas usted mismo si ya tiene experiencia y luego verifique.

Punto de Partida de todas las Consultas (Consultas Simples)

Supongamos que usted tiene una empresa con las mismas bases de datos presentadas en el artículo anterior. Además suponga que a usted le gustaría saber los últimos clientes que fueron agregados para darles una cordial bienvenida. ¿Cuál sería esa consulta que tiene que hacer?

SELECT * 
FROM cliente
ORDER BY fecha_de_creación DESC

• FROM: Indica que tabla es de la que se extrae la información
• SELECT: Indicas que columnas de la tabla se necesitan. (*) Asterisco indica que se quieren todas las columnas de la tabla.
• ORDER BY: Indica el orden con el que aparecen los resultados de la consulta. DESC indica que vaya de mayor a menor (orden descendente) en las fechas.

El resultado arrojará todos los registros desde el último cliente ingresado hasta el primero. Aquí habría que seleccionar manualmente a los clientes que se les quiere dar bienvenida. ¿ Se puede hacer mejor? es decir, ¿se puede hacer la consulta de tal forma que seleccione solo a los clientes que ingresaron ayer y hoy? La respuesta es que sí ¡y con poca modificación de la primera consulta!

SELECT * 
FROM cliente
WHERE fecha_de_creación > CURRENT_DATE - INTERVAL 2 DAY

WHERE: Indica una restricción que deben cumplir los registros. En este caso se le solicitó a la fecha_de_creación fuera estrictamente mayor que la fecha de anteayer (para así seleccionar a los registros de hoy y ayer).

¡Ha logrado identificar a sus usuarios para darles una bienvenida! ¡felicítese!

Creando Nuevas Variables

Ahora suponga tiene el siguiente problema:

Varios de sus clientes se van al cabo de 2 meses de su compañía.

Recolectando información logra hablar con Pedro, uno de sus clientes. Y dentro de la conversación este le comenta que había olvidado que usted proporcionaba un sitio web y por eso el solo navego dos o tres veces a lo sumo desde que empezó a ser su cliente.

Esto último lo dejo altamente preocupado. Ya que su sitio web es su producto estrella con el maneja todos los datos y solicitudes de sus clientes.

Dado lo anterior, a usted, le gustaría saber que tan constantes son sus clientes para usar su plataforma web. Por ende, le gustaría saber cuantas sesiones han realizado cada uno de ellos.

Para lograr esto, usted necesita hacer dos tareas presentadas abajo:

1. Saber cuando un usuario hace log-in: Para eso se creará una variable que será 1 si hizo log-in y cero en todos los demás casos. La idea es que se contará está variable.
2. Lograr contar de forma “inteligente” las distintas sesiones: La idea es contar la variable anterior de forma que la primera sesión que realiza un usuario sea la sesión 1 y luego se vayan sumando las siguientes. Note que tiene otra complejidad (simple a primera vista) que es que la suma de sesiones viene dada por cada usuario.

La consulta para solucionar la primera tarea es la siguiente:

SELECT *, CASE WHEN id_accion = 1 THEN 1 ELSE 0 END AS log_in
FROM registro_acciones

El resultado será:

id_registro	id_cliente	id_accion	Fecha_acción	log_in
100	1	1	10/03/2020 11:20:01	1
101	2	1	10/03/2020 11:20:02	1
102	1	2	10/03/2020 11:20:03	0

Note tres puntos de esta consulta:

1. CASE va dentro del SELECT. Y sirve para mostrar donde va esta nueva columna. Esta, a su vez, es creada por medio de las columnas pre-existentes de la tabla llamada por FROM.
2. END AS: indica como se llamará esta columna. En este caso log_in
3. WHEN indica la condición a cumplir THEN indica el valor que se le da en caso de cumplirse. Y ELSE el valor que se le asigna en cualquier otro caso.

Para el segundo punto que es contar de forma “inteligente” hay que aprender otro concepto fundamental en SQL presentado en la siguiente unidad.

Subqueries

Las subqueries son queries internas creadas dentro de otra más grande. Siendo más específico y claro, la tabla resultado 1 de la unidad anterior es una tabla al igual que la tabla registro_acciones. Es decir, esta tabla podría ser llamada por otra query. Y esta tabla resultado 1, vendría siendo una subquery por servir a esta query más grande. En el siguiente párrafo se escribe la consulta que muestra las sesiones por usuarios, usando una subquery, ¿Puede visualizar cuál es esta subquery? Está será analizada, de todas formas, más abajo.

SELECT *, SUM(Log_in) OVER(PARTITION BY id_cliente ORDER BY Fecha_acción ASC) 
AS sesion_actual
FROM
(SELECT *, CASE WHEN id_accion=1 THEN 1 ELSE 0 
END AS log_in
FROM registro_acciones) 
AS tabla_log_in

El resultado de esta query sería:

id_registro	id_cliente	id_accion	Fecha_acción	log_in	sesion_actual
100	1	1	10/03/2020 11:20:01	1	1
101	2	1	10/03/2020 11:20:02	1	1
102	1	2	10/03/2020 11:20:03	0	1
103	1	1	11/03/2020 14:21:03	1	2

Aquí hay varios puntos a notar:

• AS tabla_log_in: Corresponde al nombre de la tabla creada por la subquery que en este caso se llama tabla_log_in. Este paso entremedio generalmente es necesario ¡No lo olvide!.
• Las nuevas líneas de código van dentro del SELECT. Es decir, cuando se seleccionan las columnas se indica como se realizará las operaciones entre ellas.
• Las nuevas líneas de código tiene un SUM(log_in). Esto quiere decir que se realizará una suma de esta variable. La suma “inteligente” para contabilizar las sesiones viene dado por el PARTITION BY. Que básicamente dice suma log_in a los usuarios que tienen la misma id. O dicho de otra forma, suma log_in al conteo que tienes para cada usuario. Y esa suma parte, desde el primer regristro, cómo 1 y los más nuevos se van sumando. Esto último viene dado por el ORDER BY Fecha_acción ASC.

Resolución del Problema

Suponga que al analizar los datos se da cuenta de que sus usuarios en general navegan por su plataforma. Salvo unos pocos. Estos ya los tiene identificado y potenciará sus trabajos para que usen su sitio web. Pero aun así, hay muchos usuarios que navegan y aun así se van de su empresa.

Tras mucho pensar tiene la siguiente idea.

Le gustaría trazar el viaje del cliente por su sitio web. Y ver que acciones realizan y cuánto tiempo están entre cada página.

¿Se podrá hacer esto por SQL?

Y la respuesta es que sí. Este será el tema del siguiente artículo, crear el viaje del usuario por plataforma para ver qué sucede realmente con sus clientes.