是否可以做一个简单的查询来计算我在一个确定的时间段内有多少记录，比如一年，一个月，或者一天，有一个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
每月进行统计。
谢谢!
当前回答
如果你想过滤特定年份(例如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行和date列索引的表生成的。
至于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.
最后一个是赢家。
其他回答
如果你的搜索已经超过几年了，你仍然想每月分组，我建议:
版本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)
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中有详细的说明
如果你想获得一个每月统计数据，每个月的行数按最近一个月排序，那么试试这个:
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
试一试
按年(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'
