MySQL查询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

要有月度统计数据.

谢谢!

1> codelogic..：

---

GROUP BY YEAR(record_date), MONTH(record_date)

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

---

在某些情况下,您可能需要添加额外的列以增加清晰度,例如记录跨越数年.SELECT COUNT(event_id),DATE_FORMAT(event_start,'%Y /%m')

2> Andriy M..：

---

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

---

注意(主要是潜在的downvoters).目前,这可能不如其他建议有效.尽管如此,我还是将其作为一种替代方案,也是一种可以用来了解其他解决方案的速度.(因为除非你看到差异,否则你无法从慢速快速告诉.)此外,随着时间的推移,可以对MySQL的引擎进行优化方面的改变,以便在某些方面做出这个解决方案(也许不是这样)未来的重点,与其他大多数人的效率相当.

---

我感觉这不能很好地执行，因为格式功能将无法使用日期列上的索引。

3> dimazaid..：

---

我尝试使用上面的'WHERE'语句,我认为它是正确的,因为没有人纠正它,但我错了; 经过一些搜索后,我发现这是WHERE语句的正确公式,所以代码变成这样:

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

4> 小智..：

---

试试这个

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

EXTRACT(unit FROM date)函数更好,因为使用的分组越少,函数返回一个数值.

分组时的比较条件比DATE_FORMAT函数(返回字符串值)快.尝试使用返回SQL比较条件(WHERE,HAVING,ORDER BY,GROUP BY)的非字符串值的函数字段.

5> mr.baby123..：

---

如果您的搜索时间超过几年,并且您仍希望每月进行分组,我建议:

版本#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)进行了比较,第二个版本似乎有更好的结果.

version1 (平均10次执行):1.404秒
版本2 (平均10次执行):0.780秒

(SQL_NO_CACHE添加了密钥以防止MySQL从CACHING查询.)

---

如果您使用COUNT(1)insteed COUNT(*)它将更快,结果数据相同.

---

什么是第2版的'*100'？提前致谢.

6> Haijerome..：

---

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

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

希望这为那些想要找到这个帖子的人节省一些时间.

---

值得注意的是,您还需要按"MONTH(record_date)"进行分组,以便计算多个月.

7> Salman A..：

---

如果要过滤特定年份的记录(例如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.

最后一个是赢家.

8> Cees Timmerm..：

---

完整而简单的解决方案,具有同样性能,更短,更灵活的替代方案

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

9> user3019799..：

---

如果您想获得按月最新订购的每月行数的月度统计信息,请尝试以下操作:

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

10> Faisal..：

---

您可以在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 ');