首页 文章

MySQL查询GROUP BY日/月/年

提问于
浏览
551

有可能我做一个简单的查询来计算我在一个确定的时间段内有多少记录,如年,月或日,有一个 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

要有月度统计数据 .

谢谢!

13 回答

  • 7

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

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

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

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

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

    .... group by to_char(date,'MON') ---> MAY

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

  • 5

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

    我更喜欢优化一年组选择,如下所示:

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

    通过这种方式,您可以将年份绑定一次,例如 '2009' ,带有命名参数,无需担心添加 '-01-01' 或单独传入 '2010' .

    另外,假设我们只计算行数而_112116_永远不是 NULL ,我更喜欢 COUNT(*)COUNT(id) .

  • 5

    试试这个

    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) .

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

    Note (主要是潜在的挫败者) . 目前,这可能不如其他建议有效 . 尽管如此,我还是将它作为一种替代方案,也是一种方法,可以用来了解其他解决方案的速度 . (因为你可以在优化方面使用这个解决方案,以便在未来的某些(可能不是那么遥远)点上使这个解决方案在效率上与其他大多数人相当 .

  • 25

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

    version #1:

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

    version #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秒
    version2 (平均10次执行):0.780秒

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

  • -1

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

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

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

    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
    
  • 38
    GROUP BY YEAR(record_date), MONTH(record_date)
    

    查看MySQL中的date and time functions .

  • 7

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

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

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

  • 10

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

    最后一个是赢家 .

  • 37

    以下查询在Oracle Database 12c 12.1.0.1.0版中适用于我

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

相关问题