MySQL Consulta GROUP BY dia / mês / ano

É possível fazer uma consulta simples para contar quantos registros eu tenho em um determinado período de tempo como um ano, mês ou dia, tendo um campo TIMESTAMP , como:

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

Ou até mesmo:

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

Para ter uma estatística mensal.

Obrigado!

 GROUP BY YEAR(record_date), MONTH(record_date) 

Confira as funções de data e hora no MySQL.

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

Observe (principalmente, para possíveis downvoters). Atualmente, isso pode não ser tão eficiente quanto outras sugestões. Ainda assim, deixo como uma alternativa, e também uma que pode servir para ver o quão mais rápidas são as outras soluções. (Pois você não pode dizer rápido de forma lenta até ver a diferença.) Além disso, com o passar do tempo, mudanças poderiam ser feitas no mecanismo do MySQL com relação à otimização, de modo a tornar essa solução, em alguns (talvez não tão distante) no futuro, para se tornar bastante comparável em eficiência com a maioria dos outros.

Eu tentei usar a instrução ‘WHERE’ acima, achei correto, pois ninguém corrigiu, mas eu estava errado; Depois de algumas buscas eu descobri que esta é a fórmula certa para a declaração WHERE, então o código fica assim:

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

tente este

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

A function EXTRACT (unit FROM date) é melhor, pois é usado menos agrupamento e a function retorna um valor numérico.

Condição de comparação quando o agrupamento será mais rápido que a function DATE_FORMAT (que retorna um valor de sequência). Tente usar o campo function | que retorna o valor não-string para a condição de comparação SQL (WHERE, HAVING, ORDER BY, GROUP BY).

Se a sua pesquisa durar vários anos e você ainda quiser agrupar mensalmente, sugiro:

versão 1:

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

versão # 2 (mais eficiente) :

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

Eu comparei essas versões em uma tabela grande com 1.357.918 linhas ( innodb ), e a segunda versão parece ter melhores resultados.

version1 (média de 10 execuções) : 1.404 segundos
version2 (média de 10 execuções) : 0.780 segundos

(Chave SQL_NO_CACHE adicionada para evitar que o MySQL do CACHING para consultas.)

Se você quiser agrupar por data no MySQL, use o código abaixo:

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

Espero que isso economize algum tempo para aqueles que estão indo para encontrar este segmento.

Se você quiser filtrar registros para um ano específico (por exemplo, 2000), otimize a cláusula WHERE assim:

 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. 

Ao invés de:

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

Os resultados foram gerados em uma tabela contendo 300k linhas e índice na coluna de data.

Quanto à cláusula GROUP BY , testei as três variantes em relação à tabela acima mencionada; aqui estão os 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. 

O último é o vencedor.

Se você deseja obter statistics mensais com contagens de linha por mês de cada ano, ordenadas pelo último mês, tente o seguinte:

 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 

A consulta a seguir funcionou para mim no 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); 

Solução completa e simples com alternativa similar ainda mais curta e mais flexível atualmente ativa:

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

Você pode fazer isso simplesmente na function Mysql DATE_FORMAT () em GROUP BY. Você pode querer adicionar uma coluna extra para maior clareza em alguns casos, como onde os registros abrangem vários anos, em seguida, o mesmo mês ocorre em anos diferentes. Aqui há tantas opções que você pode personalizar isso. Por favor, leia isto antes de começar. Espero que seja muito útil para você. Aqui está uma consulta de exemplo para sua compreensão

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

Eu prefiro otimizar a seleção de grupo de um ano da seguinte forma:

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

Dessa forma, você pode ligar o ano de uma só vez, por exemplo, '2009' , com um parâmetro nomeado e não precisa se preocupar em adicionar '-01-01' ou passar em '2010' separadamente.

Além disso, como presumivelmente estamos apenas contando linhas e id nunca é NULL , prefiro 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