sábado, 27 de junio de 2020

MySQL. agrupar por fecha y contar incluido los ceros cuando no hay datos para una fecha



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.