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 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;

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 159,015评论 4赞 362
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 67,262评论 1赞 292
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 108,727评论 0赞 243
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 43,986评论 0赞 205
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 52,363评论 3赞 287
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 40,610评论 1赞 219
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 31,871评论 2赞 312
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 30,582评论 0赞 198
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 34,297评论 1赞 242
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 30,551评论 2赞 246
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 32,053评论 1赞 260
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 28,385评论 2赞 253
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 33,035评论 3赞 236
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 26,079评论 0赞 8
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 26,841评论 0赞 195
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 35,648评论 2赞 274
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 35,550评论 2赞 270

mysql 分组统计参考

推荐阅读更多精彩内容