Generare una serie di date

Qual è il modo migliore in mysql per generare una serie di date in un determinato intervallo?

L’applicazione che ho in mente è scrivere una query di report che restituisca una riga per ogni data, indipendentemente dal fatto che ci siano dati da segnalare. Nella sua forma più semplice:

select dates.date, sum(sales.amount) from  dates left join sales on date(sales.created) = dates.date group by 1 

Ho provato a creare un tavolo con un sacco di date, ma sembra una soluzione pessima.

Penso che avere una tabella di calendario sia una buona idea; è ansible ottenere molte funzionalità di reporting e query, soprattutto quando si riempiono intervalli di dati sparsi.

Ho trovato questo articolo con quello che sembra essere un buon esempio.

Puoi utilizzare una variabile generare serie di date:

 Set @i:=0; SELECT DATE(DATE_ADD(X, INTERVAL @i:=@i+1 DAY) ) AS datesSeries FROM yourtable, (SELECT @i:=0) r where @i < DATEDIFF(now(), date Y) ; 

Non sono sicuro se questo è quello che hai provato 🙂 però.

Prossimo uso sopra la query generata come una tabella a left join :

 set @i:=0; select d.dates, sum(s.amount) as TotalAmount from( SELECT DATE(DATE_ADD(X, INTERVAL @i:=@i+1 DAY) ) AS dateSeries FROM Sales, (SELECT @i:=0) r where @i < DATEDIFF(now(), date Y) ) dates d left join Sales s on Date(s.Created) = Date(d.dateSeries) group by 1 ; 

se ti trovi in ​​una situazione come me, dove la creazione di tabelle temporanee è vietata e l’ impostazione di variabili non è consentita , ma desideri generare un elenco di date in un periodo specifico , ad esempio l’anno corrente per eseguire alcune aggregazioni, usa questo

 select * from (select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1, (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2, (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3, (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v where gen_date between '2017-01-01' and '2017-12-31' 

Puoi anche utilizzare la tabella temporanea per generare serie di date. Controlla sotto la query:

 CREATE TEMPORARY TABLE daterange (dte DATE); SET @counter := -1; WHILE (@counter < DATEDIFF(DATE(_todate), DATE(_fromdate))) DO INSERT daterange VALUES (DATE_ADD(_fromdate, INTERVAL @counter:=@counter + 1 DAY)); END WHILE; SELECT dates.dte, SUM(sales.amount) FROM daterange dates LEFT JOIN sales ON DATE(sales.created) = dates.date GROUP BY dates.dte;