update - MySQL Query GRUPO POR dia/mes/año

order by mysql

Question

¿Es posible hacer una consulta simple para contar cuántos registros tengo en un período determinado de tiempo como un año, un mes o un día, teniendo un campo TIMESTAMP , 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!

Answer 1

Intenté usar la declaración "DONDE" de arriba, pensé que era correcta ya que nadie la 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 se vuelve así:

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

Answer 2

La siguiente consulta me funcionó 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);

Answer 3

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

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

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

Además, como es de suponer que solo estamos contando filas e id nunca es NULL , prefiero COUNT(*) a COUNT(id) .

Answer 4

Puedes hacer esto simplemente con la función Mysql DATE_FORMAT() en GROUP BY. Es posible que desee agregar una columna adicional para mayor claridad en algunos casos, como cuando los registros abarcan varios años y el mismo mes ocurre en años diferentes. Aquí hay muchas opciones que puede personalizar. Por favor, lea esto antes de comenzar. Espero que te sea de mucha ayuda. Aquí hay una muestra de consulta 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 '');

Answer 5

Si desea agrupar por fecha en MySQL, use 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.

Answer 6

Si desea filtrar registros para un año en particular (por ejemplo, 2000), optimice la cláusula WHERE 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 contenía 300k filas e índice en la columna de fecha.

En cuanto a la cláusula GROUP BY , probé las tres variantes con 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.

Answer 7

Si desea obtener estadísticas mensuales con los 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

Answer 8

Si su búsqueda ha durado varios años y aún desea agruparse 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 tabla grande con 1,357,918 filas ( innodb ), y la segunda versión parece tener mejores resultados.

version1 (promedio de 10 ejecuciones ) : 1.404 segundos
version2 (promedio de 10 ejecuciones) : 0.780 segundos

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

Answer 9

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

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

Answer 10

prueba este

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

La función EXTRACTO (unidad desde la fecha) 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 utilizar function | field que devuelva un valor no de cadena para la condición de comparación de SQL (WHERE, HAVING, ORDER BY, GROUP BY).

Answer 11

.... 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'') ---> MAYO

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

Answer 12

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

Nota (principalmente, a potenciales downvoters). Actualmente, 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 son otras soluciones. (Porque realmente no se puede decir rápido desde lento hasta que vea 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 tanto). distante) punto en el futuro, para llegar a ser bastante comparable en eficiencia con la mayoría de los demás.

Answer 13

GROUP BY YEAR(record_date), MONTH(record_date)

Echa un vistazo a las funciones de fecha y hora en MySQL.