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

最新推荐文章于 2024-04-28 20:59:15 发布

ztnhnr

最新推荐文章于 2024-04-28 20:59:15 发布

阅读量1.6k

点赞数 1

分类专栏： mysql

本文链接：https://blog.youkuaiyun.com/ztnhnr/article/details/107429468

版权

mysql 专栏收录该内容

13 篇文章

订阅专栏

本文详细介绍如何使用MySQL的日期函数如YEAR、QUARTER、MONTH、WEEK和DATE_FORMAT，对温湿度采集数据按年、季度、月、周、日进行统计。通过具体SQL语句示例，展示如何计算日期并进行分组统计。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天在做统计报表的时候，需要对表数据按年、月、周和日分别进行统计。统计用到了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函数，计算出正确的日期，然后对数据进行统计。