GRUPO de consultas MySQL POR día / mes / año

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

Nota (principalmente, a potenciales downvoters). Actualmente, esto puede no ser tan eficiente como otras sugerencias. Sin embargo, lo dejo como una alternativa, y una, también, que puede servir para ver lo rápido que son otras soluciones. (Porque realmente no puedes distinguir rápido de lento hasta que veas la diferencia.) También, a medida que pasa el tiempo, se podrían hacer cambios en el motor de MySQL con respecto a la optimización para que esta solución, en algún momento (quizás, no tan lejano) en el futuro, llegar a ser bastante comparable en eficiencia con la mayoría de los demás.

Traté de usar la declaración 'WHERE' anterior, pensé que era correcta ya que nadie la corrigió, pero me equivoqué; después de algunas búsquedas descubrí que esta es la fórmula correcta para la declaración WHERE, por lo que el código se vuelve así:

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

Prueba este

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

EXTRACT(unit FROM date) la función es mejor ya que se usa menos agrupación y la función devuelve un valor numérico.

Condición de comparación cuando la agrupación será más rápida que la función DATE_FORMAT (que devuelve un valor de cadena). Intente usar función / campo que devuelva valor sin cadena para la condición de comparación SQL (WHERE, HAVING, ORDER BY, GROUP BY).

Si su búsqueda es más de varios años, y todavía desea agrupar mensualmente, le sugiero:

Versión #1:

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

Versión #2 (más eficiente):

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

Comparé estas versiones en una mesa grande con 1,357,918 filas (innodb ), y la segunda versión parece tener mejores resultados.

Version1 (promedio de 10 ejecuta): 1.404 segundos
version2 (promedio de 10 ejecuta): 0.780 segundos

(SQL_NO_CACHE clave añadida para evitar que MySQL almacene en CACHÉ las consultas.)

Si desea agrupar por fecha en MySQL, utilice el siguiente código:

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

Espero que esto ahorre algo de tiempo para los que van a encontrar este hilo.

Si desea filtrar registros para un año en particular (por ejemplo, 2000), optimice la cláusula WHERE de la siguiente manera:

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.

En lugar de:

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

Los resultados se generaron en una tabla que contiene 300k filas e índice en la columna fecha.

En cuanto a la cláusula GROUP BY, probé las tres variantes contra la tabla mencionada anteriormente; aquí están los resultados:

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.

El último es el ganador.

Si desea obtener estadísticas mensuales con recuentos de filas por mes de cada año ordenados por el último mes, intente esto:

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

Solución completa y simple con un rendimiento similar pero alternativa más corta y flexible actualmente activa:

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

La siguiente consulta funcionó para mí en 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);

Puede hacer esto simplemente Mysql DATE_FORMAT() función en GRUPO POR. Es posible que desee agregar una columna adicional para mayor claridad en algunos casos, como cuando los registros abarcan varios años y luego el mismo mes ocurre en años diferentes.Aquí tantas opciones que puede personalizar esto. Por favor, lea esto antes de comenzar. Espero que sea muy útil para usted. Aquí está la consulta de ejemplo para su comprensión

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 ');

Prefiero optimizar la selección del grupo de un año así:

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

De esta manera puede enlazar el año una vez, por ejemplo, '2009', con un parámetro con nombre y no necesita preocuparse por agregar '-01-01' o pasar '2010' por separado.

También, como presumiblemente solo estamos contando filas y id nunca es NULL, prefiero 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') --->MAY

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