首页 新闻 搜索 专区 学院

mysql中时间断点统计问题

0
悬赏园豆:100 [待解决问题]

怎么统计出如图所示的结果呢?时间间隔超过2分钟的为断点,否则算作正常的时间段

问题补充:
CREATE TABLE `bp_khtj` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `auth_type` varchar(40) DEFAULT NULL COMMENT '授权方式:phone、weixin',
  `tag` varchar(40) DEFAULT NULL COMMENT '验证标记(qq号,手机号)',
  `address` varchar(40) DEFAULT NULL,
  `cardtype` varchar(40) DEFAULT NULL,
  `ftutype` varchar(500) DEFAULT NULL,
  `router_sn` varchar(40) DEFAULT NULL COMMENT '盒子唯一码',
  `client_mac` varchar(40) DEFAULT NULL COMMENT '客户端手机的mac',
  `ip` varchar(50) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=155 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of bp_khtj
-- ----------------------------
INSERT INTO `bp_khtj` VALUES ('130', 'phone', '13922202371', '广东 广州市', '移动全球通卡', 'Apple', '88706c314c4d95c6', '60:d9:c7:82:d3:87', '192.168.10.117', '2014-09-15 00:36:03');
INSERT INTO `bp_khtj` VALUES ('131', 'phone', '13922202371', '广东 广州市', '移动全球通卡', 'Apple', '88706c314c4d95c6', '60:d9:c7:82:d3:87', '192.168.10.117', '2014-09-15 00:38:02');
INSERT INTO `bp_khtj` VALUES ('132', 'phone', '13922202371', '广东 广州市', '移动全球通卡', 'Apple', '88706c314c4d95c6', '60:d9:c7:82:d3:87', '192.168.10.117', '2014-09-15 00:40:02');
INSERT INTO `bp_khtj` VALUES ('133', 'phone', '13922202371', '广东 广州市', '移动全球通卡', 'Apple', '88706c314c4d95c6', '60:d9:c7:82:d3:87', '192.168.10.117', '2014-09-15 01:00:02');
INSERT INTO `bp_khtj` VALUES ('134', 'phone', '13922202371', '广东 广州市', '移动全球通卡', 'Apple', '88706c314c4d95c6', '60:d9:c7:82:d3:87', '192.168.10.117', '2014-09-15 01:02:02');
INSERT INTO `bp_khtj` VALUES ('135', 'phone', '13922202371', '广东 广州市', '移动全球通卡', 'Apple', '88706c314c4d95c6', '60:d9:c7:82:d3:87', '192.168.10.117', '2014-09-15 01:04:02');
INSERT INTO `bp_khtj` VALUES ('136', 'phone', '13922202371', '广东 广州市', '移动全球通卡', 'Apple', '88706c314c4d95c6', '60:d9:c7:82:d3:87', '192.168.10.117', '2014-09-15 08:20:02');
INSERT INTO `bp_khtj` VALUES ('137', 'phone', '13922202371', '广东 广州市', '移动全球通卡', 'Apple', '88706c314c4d95c6', '60:d9:c7:82:d3:87', '192.168.10.117', '2014-09-15 08:22:01');
INSERT INTO `bp_khtj` VALUES ('138', 'phone', '15914402001', '广东 广州市', '移动预付费卡', 'Meizu', 'e702dd54f3f6c947', '38:bc:1a:00:6b:f5', '192.168.10.135', '2014-09-15 15:00:02');
INSERT INTO `bp_khtj` VALUES ('139', 'phone', '15914402001', '广东 广州市', '移动预付费卡', 'Meizu', 'e702dd54f3f6c947', '38:bc:1a:00:6b:f5', '192.168.10.135', '2014-09-15 14:58:01');
INSERT INTO `bp_khtj` VALUES ('140', 'phone', '15914402001', '广东 广州市', '移动预付费卡', 'Meizu', 'e702dd54f3f6c947', '38:bc:1a:00:6b:f5', '192.168.10.135', '2014-09-15 14:56:01');
INSERT INTO `bp_khtj` VALUES ('141', 'phone', '15914402001', '广东 广州市', '移动预付费卡', 'Meizu', 'e702dd54f3f6c947', '38:bc:1a:00:6b:f5', '192.168.10.135', '2014-09-15 14:54:02');
INSERT INTO `bp_khtj` VALUES ('142', 'phone', '15914402001', '广东 广州市', '移动预付费卡', 'Meizu', 'e702dd54f3f6c947', '38:bc:1a:00:6b:f5', '192.168.10.135', '2014-09-15 14:52:02');
INSERT INTO `bp_khtj` VALUES ('143', 'phone', '15914402001', '广东 广州市', '移动预付费卡', 'Meizu', 'e702dd54f3f6c947', '38:bc:1a:00:6b:f5', '192.168.10.135', '2014-09-15 14:50:03');
INSERT INTO `bp_khtj` VALUES ('144', 'phone', '15914402001', '广东 广州市', '移动预付费卡', 'Meizu', 'e702dd54f3f6c947', '38:bc:1a:00:6b:f5', '192.168.10.135', '2014-09-15 14:48:02');
INSERT INTO `bp_khtj` VALUES ('145', 'phone', '15920904018', '广东 广州市', '移动预付费卡', 'GUANGDONG', 'e702dd54f3f6c947', 'e8:bb:a8:70:ae:ef', '192.168.10.173', '2014-09-15 11:46:03');
INSERT INTO `bp_khtj` VALUES ('146', 'phone', '15920904018', '广东 广州市', '移动预付费卡', 'GUANGDONG', 'e702dd54f3f6c947', 'e8:bb:a8:70:ae:ef', '192.168.10.173', '2014-09-15 11:48:03');
INSERT INTO `bp_khtj` VALUES ('147', 'phone', '15920904018', '广东 广州市', '移动预付费卡', 'GUANGDONG', 'e702dd54f3f6c947', 'e8:bb:a8:70:ae:ef', '192.168.10.173', '2014-09-15 11:50:03');

时间间隔为2分钟以内视为正常,超过两分钟为断点

jiayongchao的主页 jiayongchao | 初学一级 | 园豆:85
提问于:2014-09-15 19:53
< >
分享
所有回答(2)
0

关注.

晓菜鸟 | 园豆:2594 (老鸟四级) | 2014-09-15 20:29
0

借助用户变量可完成,方法是错行比较,对于符合条件的做一标记,统计时按手机号和所做标记分组进行即可出结果,脚本如下:

 1 set @pn='00000000000',@nv=0 ,@rid=0;
 2 select t.auth_type 类型,t.tag 手机号码,t.address 地址,t.cardtype 卡类型,t.ftutype 终端,
 3 t.router_sn,t.client_mac MAC地址,t.ip ip,date_format(min(t.create_date),'%Y/%m/%d %H:%i') 上线时间,
 4 date_format(max(t.create_date),'%Y/%m/%d %H:%i') 下线时间,
 5 floor((time_to_sec(date_format(max(t.create_date),'%H:%i:00')) - 
 6 time_to_sec(date_format(min(t.create_date),'%H:%i:00'))) / 60) "在线时长(分钟)" from
 7 (
 8 SELECT *,if(@pn=tag,
 9 if(round(time_to_sec(date_format(create_date,'%H:%i:%s')) / 60,2) - @nv > 2,
10 @rid := @rid + 1,@rid),
11 @rid := 1) cc,@nv:=round(time_to_sec(date_format(create_date,'%H:%i:%s')) / 60,2) nv, @pn:=tag
12 FROM m4db.bp_khtj )t
13 group by t.tag,t.cc
习之也 | 园豆:202 (菜鸟二级) | 2014-09-18 22:16
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册