MySQL查询 GROUP BY 天/月/年

GROUP BY YEAR(record_date), MONTH(record_date)

查看 MySQL 中的日期和时间函数。

我尝试使用上面的“WHERE”语句，我认为它是正确的，因为没有人纠正它，但我错了;经过一番搜索，我发现这是 WHERE 语句的正确公式，因此代码如下所示：

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

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

---

注意（主要是对潜在的反对者）。目前，这可能不如其他建议有效。尽管如此，我还是把它作为一个替代方案，也是一个替代方案，它可以帮助你看看其他解决方案的速度有多快。（因为在你看到区别之前，你无法真正区分快和慢。此外，随着时间的流逝，可以对MySQL的引擎进行优化方面的更改，以使该解决方案在未来的某个时间点（也许不是那么遥远）在效率上与大多数其他解决方案相当。

如果要在MySQL中按日期分组，请使用以下代码：

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

希望这能为那些将要找到此线程的人节省一些时间。

试试这个

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

EXTRACT（unit FROM date） 函数更好，因为使用的分组较少，并且函数返回一个数字值。

分组时的比较条件将比DATE_FORMAT函数（返回字符串值）快。尝试使用 function|field 为 SQL 比较条件（WHERE、HAVING、ORDER BY、GROUP BY）返回非字符串值。

如果你的搜索已经持续了好几年，并且你仍然想每月分组，我建议：

版本#1：

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

版本#2（更有效）：

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

我在一张有 1,357,918 行 （innodb） 的大桌子上比较了这些版本， 第二个版本似乎有更好的结果。

版本 1（平均执行 10 次）：1.404 秒
版本 2（平均执行 10 次）：0.780 秒

(SQL_NO_CACHE添加了键以防止 MySQL 缓存到查询。

如果要获取按最新月份排序的每年每月行数的月度统计信息，请尝试以下操作：

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

如果要过滤特定年份（例如 2000 年）的记录，请像这样优化子句：WHERE

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.

而不是：

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

结果是针对包含 300k 行和日期索引列的表生成的。

至于子句，我根据上表测试了三种变体;结果如下：GROUP BY

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.

最后一个是赢家。

我更喜欢像这样优化一年组的选择：

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

这样，您只需将年份绑定一次，例如，使用命名参数，而无需担心单独添加或传入。'2009''-01-01''2010'

另外，大概我们只是在计算行数，而且从来都不是，我更喜欢.idNULLCOUNT(*)COUNT(id)

以下查询在 Oracle Database 12c 版本 12.1.0.1.0 中对我有用

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

.... 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')--->5月

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

您可以简单地在 GROUP BY 中使用 Mysql DATE_FORMAT（） 函数来执行此操作。在某些情况下，您可能需要添加一个额外的列以增加清晰度，例如，记录跨越数年，然后同一月份发生在不同的年份。在这里，您可以自定义这么多选项。请阅读此内容以开始。希望它应该对您很有帮助。以下是示例查询，供您理解

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

完整而简单的解决方案，具有类似性能但更短、更灵活的替代方案，目前处于活动状态：

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

这是另一种方法。这使用 [MySQL 的 LAST_DAY（） 函数][1] 将每个时间戳映射到其月份。如果 上有索引，它还能够通过有效的范围扫描按年份进行过滤。record_date

  SELECT LAST_DAY(record_date) month_ending, COUNT(*) record_count
    FROM stats
   WHERE record_date >= '2000-01-01'
     AND record_date <  '2000-01-01' + INTERVAL 1 YEAR
   GROUP BY LAST_DAY(record_date) 

如果您希望按天获得结果，请改用。DATE(record_date)

如果您希望按日历季度获得结果，请使用 .YEAR(record_date), QUARTER(record_date)

这是一篇文章。https://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/ [1]：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_last-day

我想每天获得类似的数据，经过一些实验，这是我能找到的最快的数据

SELECT COUNT(id)
FROM stats
GROUP BY record_date DIV 1000000;

如果要每月使用，请添加额外的零 （00） 从“使代码可读”的角度来看，我不建议这样做，它也可能在不同的版本中中断。但在我们的案例中，与我测试的其他一些更清晰的查询相比，这花费的时间不到一半。

这是一个MySQL答案（因为MySQL在问题中被标记），并在手册 https://dev.mysql.com/doc/refman/8.0/en/date-and-time-type-conversion.html 中得到了很好的记录

尝试一下

按年（record_date）、月（record_date）分组

或者你可以像这样使用 group by 子句，

//to get data by month and year do this ->
SELECT FORMAT(TIMESTAMP_COLUMN, 'MMMM yy') AS Month, COUNT(ID) FROM TABLE_NAME GROUP BY FORMAT(TIMESTAMP_COLUMN, 'MMMM yy')

如果要按日期获取记录，则按分组方式将格式更改为 'dd-mm-yy' 或 'dd-MMMM-yyy'

简单来说，无需ONLY_FULL_GROUP_BY，您可以编写一个查询，在查询中按一列对数据进行分组，但仍会在结果中显示其他列，而无需聚合它们。这对于某些查询可能更方便，但请务必谨慎使用此功能以避免意外结果。默认情况下，slq_mode ONLY_FULL_GROUP_BY在mysql服务器中，因此请确保每当对数据进行分组时，都无法显示与分组依据不匹配的数据。即，如果您按 THEN 分组，则无法选择必须将其转换为TABLE.creation_dateMONTH(TABLE.creation_date)SELECT MONTH(TABLE.creation_date)

例：

// ❌ WRONG
SELECT DATE(TABLE.creation_date) FROM TABLE GROUP BY MONTH(inv.creation_date); // groups data by month
//END OF WRONG QUERY

// ✅ CORRECT
SELECT MONTH(TABLE.creation_date) FROM TABLE GROUP BY MONTH(inv.creation_date);
//END OF CORRECT QUERY

（粗略地）在 2.5M 行表上对一些解决方案进行基准测试，这似乎是目前明显的性能赢家。EXTRACT(YEAR_MONTH FROM date)

GROUP BY DATE_FORMAT(`date`,'%Y-%m')
2.4555 seconds

GROUP BY EXTRACT(YEAR_MONTH FROM `date`)
1.3241 seconds

GROUP BY YEAR(date), MONTH(`date`)
1.8917 seconds

GROUP BY YEAR(date)*100 + MONTH(date)
1.7791 seconds

GROUP BY SUBSTRING(`date`,1,7)
2.2972 seconds

GROUP BY LEFT(`date`,7)
2.2346 seconds

应该注意的是，这些查询都不能使用索引;因此，如果您需要更好的性能，最简单的方法是添加一个带有索引的 VIRTUAL 列 （MySQL 5.7+）：

ALTER TABLE `foobar`
ADD `date_year_month` MEDIUMINT UNSIGNED AS (EXTRACT(YEAR_MONTH FROM `date`)) VIRTUAL
AFTER `date`,
ADD INDEX (`date_year_month`);

GROUP BY `date_year_month`
0.0330 seconds