是否可以做一个简单的查询来计算我在一个确定的时间段内有多少记录,比如一年,一个月,或者一天,有一个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
每月进行统计。
谢谢!
当前回答
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)
希望这为那些要找到这个帖子的人节省了一些时间。
如果你想过滤特定年份(例如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.
最后一个是赢家。
你可以在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 ');
.... 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')——>
下面的查询在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);