WithCoderWithCoderWithCoder

MySQL按 年 月 周 日统计表中的数据

    今天在做统计报表的时候,需要对表数据按年、月、周和日分别进行统计。统计用到了MySQL日期函数DATE_FORMAT、YEAR、QUARTER、MONTH和WEEK,本文就这些函数的使用和功能实现进行简单记录,以备后续使用参考。

    准备工作

        本文使用的表结构(温湿度采集表):

CREATE TABLE `tn_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `temperature` float DEFAULT '0' COMMENT '温度',
  `humidity` float DEFAULT '0' COMMENT '湿度',
  `updatetime` datetime DEFAULT NULL COMMENT '采集时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1824 DEFAULT CHARSET=utf8mb4

        要实现统计功能:

            按年、季度、月、周、日统计采集的数据量   

    一、按年统计

        实现的原理是,使用日期函数 YEAR(),计算每个日期的年,然后使用 GROUP BY 统计最终的结果,sql 语句如下(注意日期的计算):        

SELECT 
    CONCAT(YEAR (updatetime),'年') dt,
    COUNT(1) AS num 
FROM tn_data 
GROUP BY dt ORDER BY YEAR(updatetime)

        执行以上 sql 代码,结果如下:        

dt          num  
-------  --------
2020年          26

    二、按季度统计

        实现的原理是,使用日期函数 QUARTER(),计算每个日期所在的季度,然后使用 GROUP BY 统计最终的结果,sql 语句如下(注意日期的计算):        

SELECT 
    CONCAT(YEAR (updatetime),'年',QUARTER (updatetime),'季度') dt,
    COUNT(1) AS num 
FROM tn_data 
GROUP BY dt ORDER BY QUARTER(updatetime)

        执行以上 sql 代码,结果如下:        

dt                 num  
--------------  --------
2020年1季度               9
2020年2季度              17

    三、按月统计

        实现的原理是,使用日期函数 MONTH(),计算每个日期所在的月份,然后使用 GROUP BY 统计最终的结果,sql 语句如下(注意日期的计算):        

SELECT 
    CONCAT(YEAR (updatetime),'年',MONTH(updatetime),'月') dt,
    COUNT(1) AS num 
FROM tn_data 
GROUP BY dt ORDER BY MONTH(updatetime)

        执行以上 sql 代码,结果如下:        

dt              num  
-----------  --------
2020年1月             2
2020年2月             3
2020年3月             4
2020年4月             4
2020年5月             4
2020年6月             9

    四、按周统计

        实现的原理是,使用日期函数 WEEK(),计算每个日期所在的周,然后使用 GROUP BY 统计最终的结果,sql 语句如下(注意日期的计算):        

SELECT 
    CONCAT(YEAR (updatetime),'年',WEEK(updatetime),'周') dt,
    COUNT(1) AS num 
FROM tn_data 
GROUP BY dt ORDER BY WEEK(updatetime)

        执行以上 sql 代码,结果如下:        

dt               num  
------------  --------
2020年0周              2
2020年4周              3
2020年9周              4
2020年13周             4
2020年21周             4
2020年22周             9

    五、按日统计

        实现的原理是,使用日期函数 DATE_FORMAT(),计算每个日期,然后使用 GROUP BY 统计最终的结果,sql 语句如下(注意日期的计算):        

SELECT 
    DATE_FORMAT(updatetime, '%Y-%m-%d') dt,
    COUNT(1) AS num 
FROM tn_data 
GROUP BY dt ORDER BY dt

        执行以上 sql 代码,结果如下:        

dt             num  
----------  --------
2020-01-01         2
2020-02-01         3
2020-03-01         4
2020-04-01         4
2020-05-29         4
2020-06-01         9

     通过上面的简单介绍,可以看出,实现按年、季度、月、周、日统计数据量,主要是使用相应的MySQL函数,计算出正确的日期,然后对数据进行统计。


欢迎分享交流,转载请注明出处:WithCoder » MySQL按 年 月 周 日统计表中的数据