mysql如何按季度分组统计_MySQL中按周、⽉、季、年分组
统计
根据⽇期，按照周、⽉、季、年对数据进⾏分组统计
⼀、前⾔
带着问题去学习，我觉得是⼀种⾮常有效的学习⽅法，不知下⾯的⼏个问题是否也是你所需要考虑的：被分组的⽇期是否连续、周是以周⽇为起始⽇，还是以周⼀为起始⽇、⽇期跨年。在讨论⽇期查询的复杂性之前，我们有必要知道⼀些内建⽇期处理函数和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)
%pAM 或 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 fordate_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) / 604800week, 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) / 604800week, SUM(a.lowPrice) FROM date_group AS a GROUP BY a.ei, week;---------------------