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分钟以内视为正常，超过两分钟为断点
所有回答(2)
0
关注.
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
