SQL Sakila: rentas por día

Nota: Para seguir mejor este ejemplo y ejecutar las consultas, les recomiendo seguir las instrucciones para configurar el ambiente de trabajo en la parte 1 del tutorial de SQL. Además, en el siguiente enlace pueden ver el modelo de Sakila versión 0.8.

En este ejemplo continuaremos usando la base de datos Sakila. El objetivo es obtener el promedio de rentas por día de la semana. Esto puede ser útil para lanzar una promoción en el día con menos rentas (por ejemplo: martes de 2x1).

Usaremos la tabla rental que tiene un registro por cada renta realizada. El campo que nos interesa es rental_date que es de tipo fecha (datetime). Primero, tenemos que obtener el día de la semana para esa fecha, es decir, a partir de “3 de Julio de 2011” debemos obtener “Domingo”. La función WEEKDAY es justo lo que necesitamos, retorna 0 para lunes, 1 para martes, ... 6 para domingo. La siguiente consulta retorna el día de la semana cuando se realizó la renta.

SELECT WEEKDAY(rental_date) AS wday FROM rental;

Ahora, todo lo que tenemos que hacer es agrupar y contar las ocurrencias para cada día de la semana.

mysql> select weekday(rental_date) as wday, count(*)
    -> from rental
    -> group by weekday(rental_date);

Noten que en la cláusula group by podemos usar el resultado de una función, en este caso weekday. La siguiente imagen muestra el resultado.

Podemos observar, que jueves es cuando se han realizado menos rentas en total con 2200. Noten que obtuvimos el total de rentas, pero ¿cómo podemos obtener el promedio por día? En otras palabras, hasta el momento sabemos que de todos los jueves en la tabla hay un total de 2200 rentas, pero no sabemos cuántas películas se rentan por jueves (por ejemplo, en un jueves se rentan normalmente 70 películas).

Recordemos que la tabla rental tiene una renta por registro. Lo primero que tenemos que hacer es agrupar las retas por día, es decir, una consulta que nos permita obtener: el 17 de Julio se realizaron 45 rentas. Sin embargo, un simple group by rental_date no funciona ya que rental_date contiene fecha y hora (dos rentas en el mismo día pero a diferente hora no serían agrupadas en el mismo día). Para ello utilizaremos la función DATE() que nos permite extraer sólo la fecha. La siguiente consulta agrupa por fecha.

mysql> select date(rental_date) as solo_fecha, count(*) as rentas 
    -> from rental 
    -> group by solo_fecha;

Ahora podemos agrupar por día de la semana y obtener un promedio. Además del promedio, también desplegamos el número de días y el total (es decir, el promedio por día x número de días, éste debe ser igual al total que obtuvimos en la primer consulta).

mysql> select weekday(solo_fecha) as wday, 
    ->        avg(rentas), count(*) as ndias, 
    ->        avg(rentas) * count(*) as total 
    -> from ( 
    -> select date(rental_date) as solo_fecha, count(*) as rentas 
    -> from rental 
    -> group by solo_fecha 
    -> ) agrupado_por_dia 
    -> group by wday;

La siguiente imagen muestra el resultado de la consulta.

En la primer consulta obtuvimos que el día con menos rentas en total era jueves; sin embargo, con esta consulta vemos que en promedio se rentan menos películas los martes. Esto se debe a que en la base de datos hay más días martes en comparación con el resto de los días.

Como pueden ver es posible manipular los datos de la base de diferentes formas para tener varias perspectivas de la misma información. Además existen muchas funciones predeterminadas que nos ayudan a transformar los datos, en este post vimos 2 funciones para la manipulación de fechas (DATE y WEEKDAY).

Compartan sus consultas favoritas de la BD Sakila o describan alguna consulta que les gustaría que explicáramos en un post.