La idea la encontré en stack overflow "MySQL group by date and count including missing dates".
Consiste en generar tantos números como días se quiera. En el ejemplo se generan 10 mil filas. Y como se toma como referencia la fecha actual, son 10 mil días hacia atrás. Eso son más de 27 años.
create view datetable as
select
date_format(adddate(now(),-(a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a))),'%Y-%m-%d') AS date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
;
Entonces, la idea es realizar un LEFT JOIN con esta vista para así obtener todas las filas incluidas las no relacionadas, y condicionar para el rango de fechas deseado. Por ejemplo, a continuación con una tabla de usuarios.
select
datetable.date,
count(users.id) users
from
datetable
left join users on datetable.date = date_format(users.createdAt,'%Y-%m-%d')
where
datetable.date
between date_format('2020-06-20','%Y-%m-%d')
and date_format('2020-06-30','%Y-%m-%d')
group by
datetable.date;
+------------+-------+
| date | users |
+------------+-------+
| 2020-06-20 | 0 |
| 2020-06-21 | 0 |
| 2020-06-22 | 0 |
| 2020-06-23 | 0 |
| 2020-06-24 | 0 |
| 2020-06-25 | 0 |
| 2020-06-26 | 207 |
| 2020-06-27 | 0 |
+------------+-------+
Todos los usuarios fuerondatos de alta el 2020-06-26 por eso se ve que ese día tiene los 207 usuarios y el resto de días cero. Aunque en el rango se indica entre el 20 y 30 de junio. La sentencia se ejecuta el 27 de junio que es la fecha actual que toma de referencia en la vista. Por ello, no se ve datos para el 28, 29, 30.
Entendiendo la idea se puede adaptar al problema a resolver.
No hay comentarios:
Publicar un comentario