MySQL中按周、月、季、年分组统计

最新推荐文章于 2022-10-21 11:02:52 发布

转载最新推荐文章于 2022-10-21 11:02:52 发布 · 4.5k 阅读

10 ·

CC 4.0 BY-SA版权

原文链接：https://blog.youkuaiyun.com/xie8409959/article/details/82663899

文章标签：

#MySQL

MySQL 专栏收录该内容

26 篇文章

订阅专栏

本文详细介绍了如何利用内建日期处理函数和MySQL查询，对金融数据按年、季、月、周进行分组统计，包括NOW(), UNIX_TIMESTAMP(), DATE_FORMAT()等函数的用法，并提供了SQL示例。适合初学者理解日期操作在数据库中的应用。

根据日期，按照周、月、季、年对数据进行分组统计
一、前言
带着问题去学习，我觉得是一种非常有效的学习方法，不知下面的几个问题是否也是你所需要考虑的：被分组的日期是否连续、周是以周日为起始日，还是以周一为起始日、日期跨年。在讨论日期查询的复杂性之前，我们有必要知道一些内建日期处理函数和Date/Time（日期/时间）数据类型,已经有一定了解的快速浏览下就好，直接去后半部分享用正餐。

二、内建日期处理函数
NOW() 函数
定义和用法：

NOW() 返回当前的日期和时间。
查询

select now();
结果

UNIX_TIMESTAMP() 函数
定义和用法：

1 无参数调用：UNIX_TIMESTAMP() 返回值：自'1970-01-01 00:00:00'的到当前时间的秒数差
2 有参数调用：UNIX_TIMESTAMP(date) 返回值：自'1970-01-01 00:00:00'与指定时间的秒数差
查询：

select UNIX_TIMESTAMP(), UNIX_TIMESTAMP('20180912');
结果：

DATE_FORMAT() 函数
定义和用法：

DATE_FORMAT(date,format) 函数用于以不同的格式显示日期/时间数据。
查询：

select DATE_FORMAT(NOW(),'%Y-%m-%d');
结果：

三、 Date类型
date数据类型
数据类型描述
DATE() 日期。格式：YYYY-MM-DD
注释：支持的范围是从 '1000-01-01' 到 '9999-12-31'

DATETIME() *日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS
注释：支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'

TIMESTAMP() *时间戳。TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的秒数来存储。格式：YYYY-MM-DD HH:MM:SS
注释：支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC

TIME() 时间。格式：HH:MM:SS
注释：支持的范围是从 '-838:59:59' 到 '838:59:59'

YEAR() 2 位或 4 位格式的年。
注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。

注：即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。
在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。
TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。
format 可以使用的格式有:
格式   描述
%a   缩写星期名
%b   缩写月名
%c   月，数值
%D   带有英文前缀的月中的天
%d   月的天，数值（00-31）
%e   月的天，数值（0-31）
%f   微秒
%H   小时（00-23）
%h   小时（01-12）
%I   小时（01-12）
%i   分钟，数值（00-59）
%j   年的天（001-366）
%k   小时（0-23）
%l   小时（1-12）
%M   月名
%m   月，数值（00-12）
%p   AM 或 PM
%r   时间，12-小时（hh:mm:ss AM 或 PM）
%S   秒（00-59）
%s   秒（00-59）
%T   时间, 24-小时（hh:mm:ss）
%U   周（00-53）星期日是一周的第一天
%u   周（00-53）星期一是一周的第一天
%V   周（01-53）星期日是一周的第一天，与 %X 使用
%v   周（01-53）星期一是一周的第一天，与 %x 使用
%W   星期名
%w   周的天（0=星期日, 6=星期六）
%X   年，其中的星期日是周的第一天，4 位，与 %V 使用
%x   年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y   年，4 位
%y   年，2 位

四、分组统计
说明
我们先在数据库中创建一张表

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for date_group
-- ----------------------------
DROP TABLE IF EXISTS `date_group`;
CREATE TABLE `date_group` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`ei` bigint(20) NOT NULL,
`day` varchar(25) DEFAULT NULL COMMENT '日期',
`openPrice` decimal(25,2) DEFAULT NULL COMMENT '开盘价',
`closePrice` decimal(25,2) DEFAULT NULL COMMENT '收盘价',
`lowPrice` decimal(25,2) DEFAULT NULL COMMENT '最低价',
`higPrice` decimal(25,2) DEFAULT NULL COMMENT '最高价',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of date_group
-- ----------------------------
INSERT INTO `date_group` VALUES ('1', '10001', '20161230', '10.00', '20.00', '5.00', '25.00');
INSERT INTO `date_group` VALUES ('2', '10001', '20161231', '11.00', '19.00', '6.00', '26.00');
INSERT INTO `date_group` VALUES ('3', '10001', '20170101', '12.00', '18.00', '7.00', '27.00');
INSERT INTO `date_group` VALUES ('5', '10001', '20170102', '12.00', '18.00', '7.00', '27.00');
INSERT INTO `date_group` VALUES ('6', '10001', '20170203', '13.00', '19.00', '8.00', '21.00');
INSERT INTO `date_group` VALUES ('7', '10001', '20170204', '15.00', '20.00', '9.00', '24.00');
INSERT INTO `date_group` VALUES ('8', '10001', '20170205', '13.00', '12.00', '4.00', '27.00');
INSERT INTO `date_group` VALUES ('9', '10001', '20170206', '12.00', '13.00', '5.00', '25.00');
INSERT INTO `date_group` VALUES ('10', '10001', '20180109', '14.00', '11.00', '6.00', '22.00');
INSERT INTO `date_group` VALUES ('11', '10001', '20180110', '17.00', '18.00', '8.00', '23.00');
INSERT INTO `date_group` VALUES ('12', '10001', '20180111', '19.00', '12.00', '9.00', '22.00');
INSERT INTO `date_group` VALUES ('13', '10001', '20180112', '13.00', '16.00', '2.00', '25.00');
INSERT INTO `date_group` VALUES ('14', '10001', '20180113', '11.00', '17.00', '1.00', '28.00');
INSERT INTO `date_group` VALUES ('15', '10001', '20180114', '10.00', '13.00', '8.00', '24.00');
INSERT INTO `date_group` VALUES ('16', '10002', '20161230', '15.00', '22.00', '5.00', '25.00');
INSERT INTO `date_group` VALUES ('17', '10002', '20161231', '13.00', '19.00', '6.00', '22.00');
INSERT INTO `date_group` VALUES ('18', '10002', '20170101', '12.00', '14.00', '7.00', '23.00');
INSERT INTO `date_group` VALUES ('19', '10002', '20170102', '14.00', '18.00', '7.00', '22.00');
INSERT INTO `date_group` VALUES ('20', '10002', '20170203', '17.00', '12.00', '8.00', '23.00');
INSERT INTO `date_group` VALUES ('21', '10002', '20170204', '19.00', '20.00', '9.00', '22.00');
INSERT INTO `date_group` VALUES ('22', '10002', '20170205', '13.00', '17.00', '4.00', '22.00');
INSERT INTO `date_group` VALUES ('23', '10002', '20170206', '11.00', '13.00', '6.00', '25.00');
INSERT INTO `date_group` VALUES ('24', '10002', '20180109', '10.00', '17.00', '8.00', '28.00');
INSERT INTO `date_group` VALUES ('25', '10002', '20180110', '17.00', '18.00', '7.00', '24.00');
INSERT INTO `date_group` VALUES ('26', '10002', '20180111', '19.00', '25.00', '8.00', '22.00');
INSERT INTO `date_group` VALUES ('27', '10002', '20180112', '13.00', '19.00', '9.00', '25.00');
INSERT INTO `date_group` VALUES ('28', '10002', '20180113', '15.00', '26.00', '4.00', '28.00');
INSERT INTO `date_group` VALUES ('29', '10002', '20180114', '13.00', '16.00', '5.00', '24.00');

按年分组统计
-- 第一种
SELECT a.ei, a.day, DATE_FORMAT(a.day,'%Y') year, SUM(a.openPrice) FROM date_group AS a GROUP BY a.ei, year;
-- 第二种
SELECT a.ei, a.day, YEAR(a.day) year, AVG(a.closePrice) FROM date_group AS a GROUP BY a.ei, year;

按季分组统计
-- 注意区分出年

-- 第一种
SELECT a.ei, a.day, CONCAT(YEAR(a.day),FLOOR((date_format(a.day, '%m')+2)/3)) quarter, SUM(a.openPrice) FROM date_group AS a GROUP BY a.ei, quarter;
-- 第二种
SELECT a.ei, a.day, CONCAT(YEAR(a.day),QUARTER(a.day)) quarter, AVG(a.closePrice) FROM date_group AS a GROUP BY a.ei, quarter;
按月分组统计
-- 第一种
SELECT a.ei, a.day, date_format(a.day, '%Y%m') month, SUM(a.openPrice) FROM date_group AS a GROUP BY a.ei, month;

-- 第二种
-- month(date)函数和 %c 差不多，返回的是月的值（如一月为 ‘1’）
SELECT a.ei, a.day, CONCAT(YEAR(a.day),MONTH(a.day)) month, AVG(a.closePrice) FROM date_group AS a GROUP BY a.ei, month;
SELECT a.ei, a.day, date_format(a.day, '%Y%c') month, AVG(a.closePrice) FROM date_group AS a GROUP BY a.ei, month;
按周分组统计
-- 第一种
-- 以周一为一周起始，所以20170101周日，为2016年最后一周，20170102 为2017年第一周
SELECT a.ei, a.day, date_format(a.day, '%x%v') week, SUM(a.lowPrice) FROM date_group AS a GROUP BY a.ei, week;
SELECT a.ei, a.day, (UNIX_TIMESTAMP(a.day) - (if(date_format(a.day, '%w'), date_format(a.day, '%w') - 1, 6) * 86400) - 316800) / 604800 week, SUM(a.lowPrice) FROM date_group AS a GROUP BY a.ei, week;

-- 第二种
-- 以周日为一周起始，所以20170101位2017年第一周
SELECT a.ei, a.day, date_format(a.day, '%X%V') week, SUM(a.lowPrice) FROM date_group AS a GROUP BY a.ei, week;
SELECT a.ei, a.day, (UNIX_TIMESTAMP(a.day) - date_format(a.day, '%w') * 86400 - 316800) / 604800 week, SUM(a.lowPrice) FROM date_group AS a GROUP BY a.ei, week;
五、后记
这是一篇来源于最近业务需求的总结，简化版了很多。之所以挑选这个需求，这是我第一次想到写博客时定下的日子，所以在平时有注意别的博主怎么写的，但真的当自己写起来时发现不是那么容易，有些代码里的想法不知道要不要表达出来，怕过于啰嗦，如果这篇文章有幸能被有经验的博主阅读到，希望能给我点指导，同时也期待其它读者关于就这篇文章的不足之处讨论交流。