MySQL Query GROUP BY giorno / mese / anno

È ansible fare una semplice query per contare quanti record ho in un determinato periodo di tempo, come un anno, un mese o un giorno, con un campo TIMESTAMP , come:

 SELECT COUNT(id) FROM stats WHERE record_date.YEAR = 2009 GROUP BY record_date.YEAR 

O anche:

 SELECT COUNT(id) FROM stats GROUP BY record_date.YEAR, record_date.MONTH 

Avere una statistica mensile

Grazie!

 GROUP BY YEAR(record_date), MONTH(record_date) 

Controlla le funzioni di data e ora in MySQL.

 GROUP BY DATE_FORMAT (record_date, '%Y%m') 

Nota (principalmente, a potenziali downvoters). Attualmente, questo potrebbe non essere efficiente come altri suggerimenti. Tuttavia, lo lascio come alternativa, e anche uno che può servire a vedere quanto più velocemente altre soluzioni sono. (Perché non si può davvero dire veloce dal lento fino a quando non si vede la differenza.) Inoltre, col passare del tempo, potrebbero essere apportate modifiche al motore di MySQL in termini di ottimizzazione in modo da rendere questa soluzione, ad alcuni (forse, non così distante) punto in futuro, per diventare abbastanza comparabile in termini di efficienza con la maggior parte degli altri.

Ho provato ad usare la frase ‘WHERE’ qui sopra, ho pensato che fosse corretta dato che nessuno lo ha corretto ma ho sbagliato; dopo alcune ricerche ho scoperto che questa è la formula giusta per l’istruzione WHERE così il codice diventa così:

 SELECT COUNT(id) FROM stats WHERE YEAR(record_date) = 2009 GROUP BY MONTH(record_date) 

prova questo

 SELECT COUNT(id) FROM stats GROUP BY EXTRACT(YEAR_MONTH FROM record_date) 

La funzione EXTRACT (unità FROM date) è migliore in quanto viene utilizzato un minor numero di gruppi e la funzione restituisce un valore numerico.

Condizione di confronto quando il raggruppamento sarà più veloce della funzione DATE_FORMAT (che restituisce un valore stringa). Prova a utilizzare function | field che restituisce il valore non stringa per la condizione di confronto SQL (WHERE, HAVING, ORDER BY, GROUP BY).

Se la tua ricerca ha una durata di diversi anni e desideri continuare a raggruppare mensilmente, ti suggerisco di:

versione n. 1:

 SELECT SQL_NO_CACHE YEAR(record_date), MONTH(record_date), COUNT(*) FROM stats GROUP BY DATE_FORMAT(record_date, '%Y%m') 

versione 2 (più efficiente) :

 SELECT SQL_NO_CACHE YEAR(record_date), MONTH(record_date), COUNT(*) FROM stats GROUP BY YEAR(record_date)*100 + MONTH(record_date) 

Ho confrontato queste versioni su un grande tavolo con 1.357.918 righe ( innodb ), e la seconda versione sembra avere risultati migliori.

version1 (media di 10 esecuzioni) : 1.404 secondi
versione 2 (media di 10 esecuzioni) : 0,780 secondi

(Chiave SQL_NO_CACHE aggiunta per impedire a MySQL di CACHING di interrogare).

Se si desidera raggruppare per data in MySQL, utilizzare il seguente codice:

  SELECT COUNT(id) FROM stats GROUP BY DAYOFMONTH(record_date) 

Spero che questo salvi un po ‘di tempo per quelli che troveranno questo thread.

Se vuoi filtrare i record per un particolare anno (es. 2000), allora ottimizza la clausola WHERE come questa:

 SELECT MONTH(date_column), COUNT(*) FROM date_table WHERE date_column >= '2000-01-01' AND date_column < '2001-01-01' GROUP BY MONTH(date_column) -- average 0.016 sec. 

Invece di:

 WHERE YEAR(date_column) = 2000 -- average 0.132 sec. 

I risultati sono stati generati rispetto a una tabella contenente 300k righe e indice nella colonna della data.

Per quanto riguarda la clausola GROUP BY , ho provato le tre varianti rispetto alla tabella sopra menzionata; ecco i risultati:

 SELECT YEAR(date_column), MONTH(date_column), COUNT(*) FROM date_table GROUP BY YEAR(date_column), MONTH(date_column) -- codelogic -- average 0.250 sec. SELECT YEAR(date_column), MONTH(date_column), COUNT(*) FROM date_table GROUP BY DATE_FORMAT(date_column, '%Y%m') -- Andriy M -- average 0.468 sec. SELECT YEAR(date_column), MONTH(date_column), COUNT(*) FROM date_table GROUP BY EXTRACT(YEAR_MONTH FROM date_column) -- fu-chi -- average 0.203 sec. 

L'ultimo è il vincitore.

Se vuoi ottenere una statistica mensile con numero di righe per mese di ogni anno ordinata per l’ultimo mese, prova questo:

 SELECT count(id), YEAR(record_date), MONTH(record_date) FROM `table` GROUP BY YEAR(record_date), MONTH(record_date) ORDER BY YEAR(record_date) DESC, MONTH(record_date) DESC 

La seguente query ha funzionato per me in Oracle Database 12c Release 12.1.0.1.0

 SELECT COUNT(*) FROM stats GROUP BY extract(MONTH FROM TIMESTAMP), extract(MONTH FROM TIMESTAMP), extract(YEAR FROM TIMESTAMP); 

Soluzione completa e semplice con un’alternativa altrettanto performante ma più breve e più flessibile attualmente triggers:

 SELECT COUNT(*) FROM stats -- GROUP BY YEAR(record_date), MONTH(record_date), DAYOFMONTH(record_date) GROUP BY DATE_FORMAT(record_date, '%Y-%m-%d') 

Puoi farlo semplicemente con la funzione Mysql DATE_FORMAT () in GROUP BY. Si consiglia di aggiungere una colonna in più per maggiore chiarezza in alcuni casi, ad esempio in cui i record si estendono per diversi anni, quindi lo stesso mese si verifica in anni diversi. Qui è ansible personalizzare tale opzione. Si prega di leggere questo prima di iniziare. Spero che dovrebbe essere molto utile per te. Ecco una query di esempio per la tua comprensione

 SELECT COUNT(id), DATE_FORMAT(record_date, '%Y-%m-%d') AS DAY, DATE_FORMAT(record_date, '%Y-%m') AS MONTH, DATE_FORMAT(record_date, '%Y') AS YEAR, FROM stats WHERE YEAR = 2009 GROUP BY DATE_FORMAT(record_date, '%Y-%m-%d '); 

Preferisco ottimizzare la selezione di gruppo di un anno in questo modo:

 SELECT COUNT(*) FROM stats WHERE record_date >= :year AND record_date < :year + INTERVAL 1 YEAR; 

In questo modo puoi unire l'anno in una sola volta, ad esempio '2009' , con un parametro denominato e non devi preoccuparti di aggiungere '-01-01' o passare '2010' separatamente.

Inoltre, come presumibilmente stiamo contando solo le righe e id non è mai NULL , preferisco COUNT(*) a COUNT(id) .

.... group by to_char(date, 'YYYY') -> 1989

.... group by to_char(date,'MM') -> 05

.... group by to_char(date,'DD') —> 23

.... group by to_char(date,'MON') —> MAGGIO

.... group by to_char(date,'YY') —> 89