Pregunta MySQL Query GROUP POR día / mes / año


¿Es posible hacer una consulta simple para contar cuántos registros tengo en un período de tiempo determinado como un año, mes o día, tener un TIMESTAMP campo, como:

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

O incluso:

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

Tener una estadística mensual.

¡Gracias!


532
2018-02-03 20:29


origen


Respuestas:


GROUP BY YEAR(record_date), MONTH(record_date)

Revisar la funciones de fecha y hora en MySQL


835
2018-02-03 20:33



GROUP BY FORMATO DE FECHA(record_date, '%Y%m')

Nota (principalmente, a posibles downvoters). En la actualidad, esto puede no ser tan eficiente como otras sugerencias. Aún así, lo dejo como una alternativa, y una, también, que puede servir para ver qué tan rápido otras soluciones son. (Porque realmente no se puede decir rápido desde lento hasta que se ve la diferencia.) Además, a medida que pasa el tiempo, se podrían hacer cambios en el motor de MySQL con respecto a la optimización para hacer esta solución, en algunos (quizás, no tan distante) punto en el futuro, para ser bastante comparable en eficiencia con la mayoría de los demás.


182
2018-04-23 19:33



Traté de usar la instrucción 'DONDE' anterior, pensé que era correcto ya que nadie lo corrigió pero estaba equivocado; 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 es así:

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

38
2018-04-23 17:52



prueba este

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

EXTRACTO (unidad FROM fecha) la función es mejor ya que se usa menos agrupamiento y la función devuelve un valor numérico.

La 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 la función | campo que devuelve un valor que no sea cadena para la condición de comparación SQL (DONDE, TENER, ORDEN POR, GRUPO POR).


36
2017-10-11 03:44



Si su búsqueda dura varios años y aún desea agrupar mensualmente, 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 gran mesa con 1.357.918 filas (), y la segunda versión parece tener mejores resultados.

versión 1  (promedio de 10 ejecuciones): 1.404 segundos
versión 2  (promedio de 10 ejecuciones): 0.780 segundos

(SQL_NO_CACHE se agregó la clave para evitar que MySQL pase de CACHING a las consultas).


21
2018-06-09 09:17



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.


15
2017-10-10 05:16



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

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 de fecha.

En cuanto a GROUP BY cláusula, 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.


10
2018-01-18 10:26



Si desea obtener una estadística mensual con recuentos de filas por mes de cada año ordenado 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

7
2017-11-22 00:01