是否可以做一个简单的查询来计算我在一个确定的时间段内有多少记录,比如一年,一个月,或者一天,有一个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
每月进行统计。
谢谢!
是否可以做一个简单的查询来计算我在一个确定的时间段内有多少记录,比如一年,一个月,或者一天,有一个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
每月进行统计。
谢谢!
当前回答
如果你想获得一个每月统计数据,每个月的行数按最近一个月排序,那么试试这个:
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
其他回答
下面的查询在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);
完整而简单的解决方案,具有类似的性能,但更短,更灵活的替代方案,目前活跃:
SELECT COUNT(*) FROM stats
-- GROUP BY YEAR(record_date), MONTH(record_date), DAYOFMONTH(record_date)
GROUP BY DATE_FORMAT(record_date, '%Y-%m-%d')
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.
如果你的搜索已经超过几年了,你仍然想每月分组,我建议:
版本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对查询进行缓存。)
.... group by to_char(date, 'YYYY')——> 1989
.... group by to_char(date,'MM')——>05
.... 3 .用to_char(date,'DD')——>
.... group by to_char(date,'MON')——>
.... 9 . group by to_char(date,'YY')——>