MySQL Query GROUP BY天/月/年

是否可以做一个简单的查询来计算我在一年、一个月或一天等确定的时间段内有多少条记录，具有TIMESTAMP字段，如： 

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

甚至：

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

每月统计一次。

谢谢！

827027 次浏览
小开
最佳答案 
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')

Note (primarily, to potential downvoters). Presently, this may not be as efficient as other suggestions. Still, I leave it as an alternative, and a one, too, that can serve in seeing how faster other solutions are. (For you can't really tell fast from slow until you see the difference.) Also, as time goes on, changes could be made to MySQL's engine with regard to optimisation so as to make this solution, at some (perhaps, not so distant) point in future, to become quite comparable in efficiency with most others.

小开

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

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

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

小开

试试这个

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

提取（单位从日期）函数更好，因为使用的分组更少并且函数返回一个数字值。

比较条件分组将比DATE_FORMAT函数（返回字符串值）更快。尝试使用函数|字段返回非字符串值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行的大表（）上比较了这些版本， 第二个版本似乎有更好的结果。

版本1（平均10次执行）:1.404秒
Version2（平均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'。

此外，大概我们只是在计算行，id永远不会是NULL，我更喜欢COUNT(*)而不是COUNT(id)。

小开

以下查询适用于我的Oracle数据库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')--->五月

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

小开

您可以在GROUP BY中简单地执行MysqlDATE_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）分组


提交答案