por - MySQL: seleccione todas las fechas en un rango incluso si no hay registros presentes

generar rango de fechas sql

Question

Tengo una base de datos de usuarios. Me gustaría crear un gráfico basado en el crecimiento de la base de usuarios. La consulta que tengo ahora es:

SELECT DATE(datecreated), count(*) AS number FROM users
WHERE DATE(datecreated) > ''2009-06-21'' AND DATE(datecreated) <= DATE(NOW())
GROUP BY DATE(datecreated) ORDER BY datecreated ASC

Esto devuelve casi lo que quiero. Si obtenemos 0 usuarios un día, ese día no se devuelve como un valor 0, simplemente se omite y al día siguiente se devuelve al menos un usuario. ¿Cómo puedo obtener algo como (psuedo-respuesta):

date1 5
date2 8
date3 0
date4 0
date5 9
etc...

donde las fechas con cero aparecen en orden secuencial con el resto de las fechas?

¡Gracias!

Answer 1

Espero que descubras el resto.

select * from (
select date_add(''2003-01-01 00:00:00.000'', INTERVAL n5.num*10000+n4.num*1000+n3.num*100+n2.num*10+n1.num DAY ) as date from
(select 0 as num
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) n1,
(select 0 as num
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) n2,
(select 0 as num
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) n3,
(select 0 as num
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) n4,
(select 0 as num
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) n5
) a
where date >''2011-01-02 00:00:00.000'' and date < NOW()
order by date

Con

select n3.num*100+n2.num*10+n1.num as date

obtendrá una columna con números de 0 a max (n3) * 100 + max (n2) * 10 + max (n1)

Como aquí tenemos max n3 como 3, SELECT devolverá 399, más 0 -> 400 registros (fechas en el calendario).

Puede sintonizar su calendario dinámico limitándolo, por ejemplo, desde el mínimo (fecha) hasta ahora ().

Answer 2

Esto es mejor hacer como:

-- 7 Days:
set @n:=date(now() + interval 1 day);
SELECT qb.day_series as days , COALESCE(col_byte, 0) as Bytes from tbl1 qa
right join (
select (select @n:= @n - interval 1 day) day_series from tbl1 limit 7 ) as qb
on date(qa.Timestamp) = qb.day_series and
qa.Timestamp > DATE_SUB(curdate(), INTERVAL 7 day) order by qb.day_series asc
-- 30 Days:
set @n:=date(now() + interval 1 day);
SELECT qb.day_series as days , COALESCE(col_byte, 0) as Bytes from tbl1 qa
right join (
select (select @n:= @n - interval 1 day) day_series from tbl1 limit 30 ) as qb
on date(qa.Timestamp) = qb.day_series and
qa.Timestamp > DATE_SUB(curdate(), INTERVAL 30 day) order by qb.day_series asc;

o sin una variable como esta:

SELECT qb.day_series as days , COALESCE(col_byte, 0) as Bytes from tbl1 qa
right join (
select curdate() - INTERVAL a.a day as day_series 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
) as a ) as qb
on date(qa.Timestamp) = qb.day_series and
qa.Timestamp > DATE_SUB(curdate(), INTERVAL 7 day) order by qb.day_series asc;

Answer 3

Haga una combinación externa a la derecha en una tabla, llámelo tblCalendar, que está rellenado previamente con las fechas sobre las que desea informar. Y únete al campo de fecha.

Pablo

Answer 4

Pensándolo bien, algo como esto debería ser lo que quieras:

CREATE TEMPORARY TABLE DateSummary1 ( datenew timestamp ) SELECT DISTINCT(DATE(datecreated)) as datenew FROM users;
CREATE TEMPORARY TABLE DateSummary2 ( datenew timestamp, number int ) SELECT DATE(datecreated) as datenew, count(*) AS number FROM users
WHERE DATE(datecreated) > ''2009-06-21'' AND DATE(datecreated) <= DATE(NOW())
GROUP BY DATE(datecreated) ORDER BY datecreated ASC;
SELECT ds1.datenew,ds2.number FROM DateSummary1 ds1 LEFT JOIN DateSummary2 ds2 on ds1.datenew=ds2.datenew;

Esto le da todas las fechas en la primera tabla, y los datos de resumen de count en la segunda tabla. Es posible que deba reemplazar ds2.number con IF(ISNULL(ds2.number),0,ds2.number) o algo similar.

Answer 5

Query es:

SELECT qb.dy as yourday, COALESCE(count(yourcolumn), 0) as yourcount from yourtable qa
right join (
select curdate() as dy union
select DATE_SUB(curdate(), INTERVAL 1 day) as dy union
select DATE_SUB(curdate(), INTERVAL 2 day) as dy union
select DATE_SUB(curdate(), INTERVAL 3 day) as dy union
select DATE_SUB(curdate(), INTERVAL 4 day) as dy union
select DATE_SUB(curdate(), INTERVAL 5 day) as dy union
select DATE_SUB(curdate(), INTERVAL 6 day) as dy
) as qb
on qa.dates = qb.dy
and qa.dates > DATE_SUB(curdate(), INTERVAL 7 day)
order by qb.dy asc;

y el resultado es:

+------------+-----------+
| yourday | yourcount |
+------------+-----------+
| 2015-06-24 | 274339 |
| 2015-06-25 | 0 |
| 2015-06-26 | 0 |
| 2015-06-27 | 0 |
| 2015-06-28 | 134703 |
| 2015-06-29 | 87613 |
| 2015-06-30 | 0 |
+------------+-----------+

Answer 6

Esta pregunta me pregunta lo mismo, creo. En general, la respuesta aceptada parece ser que usted lo hace en la lógica de su aplicación (lea lo que tiene en una matriz, luego recorra la matriz y cree las fechas faltantes) o utilice tablas temporales con las fechas que desea. unirse.