MySQL查询组(按天/月/年)

GROUP BY YEAR(record_date), MONTH(record_date)

退房 date and time functions 在MySQL中。

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

注释 (主要是针对潜在的低投票人)。目前,这可能不如其他建议有效。尽管如此,我还是把它作为一个替代方案,而且也是一个替代方案,它可以帮助我们了解其他解决方案的速度有多快。(因为你看不到区别,就不能从慢到快)而且,随着时间的推移,MySQL的引擎在优化方面也会发生变化,从而使这个解决方案在未来的某个时候(也许,不那么遥远)变得与大多数其他解决方案的效率相当。

我尝试使用上面的“Where”语句,我认为它是正确的,因为没有人更正它,但我错了;经过一些搜索,我发现这是Where语句的正确公式,因此代码变成这样:

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

试试这个

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

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

分组时的比较条件将比日期格式函数(返回字符串值)快。尝试使用为SQL比较条件(其中,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)

我在一张大桌子上把这些版本和1357918行作了比较( innodb ) 第二个版本似乎有更好的结果。

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

( SQL_NO_CACHE 添加了防止mysql缓存到查询的键。)

如果要在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行和“日期”列索引的表生成的。

至于 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
-- GROUP BY YEAR(record_date), MONTH(record_date), DAYOFMONTH(record_date)
GROUP BY DATE_FORMAT(record_date, '%Y-%m-%d')

如果要获取按最新月份订购的每年每月行计数的月度统计数据,请尝试以下操作:

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数据库12c版本12.1.0.1.0中对我有效。

SELECT COUNT(*)
FROM stats
GROUP BY 
extract(MONTH FROM TIMESTAMP),
extract(MONTH FROM TIMESTAMP),
extract(YEAR  FROM TIMESTAMP);

你可以简单地使用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 ');

我更喜欢这样优化一年的团队选择:

SELECT COUNT(*)
  FROM stats
 WHERE record_date >= :year 
   AND record_date <  :year + INTERVAL 1 YEAR;

这样,您就可以一次性绑定年份,例如 '2009' ,使用命名参数,不需要担心添加 '-01-01' 或通过 '2010' 分别地。

而且,正如我们推测的那样,我们只是在计算行和 id 永远不会 NULL 我更喜欢 COUNT(*) 到 COUNT(id) .

.... group by to_char(date, 'YYYY') --GT;1989

.... group by to_char(date,'MM') --GT;05

.... group by to_char(date,'DD') ---gt;23

.... group by to_char(date,'MON') ---五月

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