首页 > 亚博2018平台 > SQL小技巧-按每几个小时分组

SQL小技巧-按每几个小时分组

2019-09-12

1.1 准备数据
数据表结构如下:
图片描述
数据如下:
图片描述
建表语句:
[SQL] 纯文本查看 复制代码
?
create table tb_user (
id int(10) unsigned not null auto_increment,
username varchar(255) not null,
addtime datetime not null,
primary key (id)
) engine=innodb default charset=utf8;
?
insert into tb_user values ('1', 'zhangsan', '2018-06-01 01:27:03');
insert into tb_user values ('2', 'lisi', '2018-06-01 04:12:03');
insert into tb_user values ('3', 'wangwu', '2018-06-01 06:13:03');
insert into tb_user values ('4', 'zhaoliu', '2018-06-01 07:22:03');
insert into tb_user values ('5', 'sunqi', '2018-06-01 11:27:03');
insert into tb_user values ('6', 'wangba', '2018-06-01 14:55:03');
insert into tb_user values ('7', 'zhangjiu', '2018-06-01 16:28:03');
insert into tb_user values ('8', 'meishi', '2018-06-01 21:35:03');
insert into tb_user values ('9', 'bainianzi', '2018-06-01 23:01:03');
insert into tb_user values ('10', 'bikaqiu', '2018-06-01 04:55:03');
insert into tb_user values ('11', 'xiyangyang', '2018-06-01 06:22:03');
insert into tb_user values ('12', 'meiyangyang', '2018-06-01 07:25:03');
insert into tb_user values ('13', 'xiongda', '2018-06-01 11:09:03');
insert into tb_user values ('14', 'guangtouqiang', '2018-06-01 08:23:03');

1.2 按时间分组
需要使用到mysql中的date_format函数
DATE_FORMAT(date,format)
根据format 字符串安排date 值的格式。
以下说明符可用在 format 字符串中:
说明符
说明
%a 工作日的缩写名称 (Sun..Sat)
%b 月份的缩写名称 (Jan..Dec)
%c 月份,数字形式(0..12)
%D 带有英语后缀的该月日期 (0th, 1st, 2nd, 3rd, ...)
%d 该月日期, 数字形式 (00..31)
%e 该月日期, 数字形式(0..31)
%f 微秒 (000000..999999)
%H 小时(00..23)
%h 小时(01..12)
%I 小时 (01..12)
%i 分钟,数字形式 (00..59)
%j 一年中的天数 (001..366)
%k 小时 (0..23)
%l 小时 (1..12)
%M 月份名称 (January..December)
%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位数)
%% ‘%’文字字符
sql语句:
[SQL] 纯文本查看 复制代码
?
1
select username,addtime from tb_user group by date_format(addtime,'%Y-%m-%d %H'),username;
效果图:
图片描述

1.3 如果要求是按照 每隔2小时或3小时,或5小时 来进行分组
需要用到div函数,整除函数,用法如下:

比如对以上数据每个5小时分组,sql语句如下
[SQL] 纯文本查看 复制代码
?
1
2
select username,addtime,hour(addtime) div 5 from tb_user
group by date_format(addtime,'%y-%m-%d'),hour(addtime) div 5 ,username

效果图:
图片描述

原文链接:https://segmentfault.com/a/1190000020372376

文章标签: