为了统计信息,写了此脚本。首先介绍一下三张mysql表的定义:
/**
ups建表数据初始化及数据样例
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tbl_ipinfo`
-- ----------------------------
DROP TABLE IF EXISTS `ipinfo`;
CREATE TABLE `ipinfo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`country` varchar(150) DEFAULT NULL COMMENT 'ip所在的国家',
`city` varchar(150) DEFAULT NULL COMMENT 'ip所在的城市',
`ipAddress` varchar(50) DEFAULT NULL COMMENT 'ip地址',
`ipv4Flag` int(11) DEFAULT NULL COMMENT '是否是ipv4地址,1-是 0-否',
`resolved` int(11) DEFAULT NULL COMMENT '是否解析过 1-为解析 2-已解析',
PRIMARY KEY (`id`),
KEY `ipinfo_index` (`ipAddress`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for `tbl_playerbaseinfo`
-- ----------------------------
DROP TABLE IF EXISTS `playerbaseinfo`;
CREATE TABLE `playerbaseinfo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`deviceType` varchar(50) DEFAULT NULL COMMENT '设备型号',
`mode` int(11) DEFAULT NULL COMMENT '上报的模式 0-手动 1-自动',
`accountId` varchar(50) NOT NULL COMMENT '玩家的账号id',
`osVersion` varchar(20) DEFAULT NULL COMMENT '客户端操作系统版本号',
`isp` varchar(250) DEFAULT NULL COMMENT '网络提供商',
`packageName` varchar(80) DEFAULT NULL COMMENT '客户端包名',
`netWorkState` int(11) DEFAULT NULL COMMENT '客户端使用的网络类型',
`totalHops` int(11) DEFAULT NULL COMMENT '路由总的条数',
`version` varchar(50) DEFAULT NULL COMMENT '客户端版本号',
`reportTime` varchar(20) NOT NULL COMMENT '上报的时间',
PRIMARY KEY (`id`),
KEY `playerbaseinfo_index` (`accountId`,`reportTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
alter table playerbaseinfo add column avgPacketLoss double(12,3);
-- ----------------------------
-- Records of tbl_playerbaseinfo
-- ----------------------------
-- ----------------------------
-- Table structure for `tbl_playerrouteinfo`
-- ----------------------------
DROP TABLE IF EXISTS `playerrouteinfo`;
CREATE TABLE `playerrouteinfo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`varianceDelay` double(12,3) DEFAULT NULL COMMENT 'ip所在的城市',
`maxDelay` double(12,3) DEFAULT NULL COMMENT '最大延迟',
`userStamp` varchar(50) DEFAULT NULL COMMENT '用户id和当前时间组成的',
`ipAddr` varchar(50) DEFAULT NULL COMMENT 'ip地址',
`packetLoss` double(12,3) DEFAULT NULL COMMENT '丢包率',
`hopNum` int(11) DEFAULT NULL COMMENT '第几条',
`avgDelay` double(12,3) DEFAULT NULL COMMENT '平均延迟',
`minDelay` double(12,3) DEFAULT NULL COMMENT '最小延迟',
`probes` int(11) DEFAULT NULL COMMENT '探测次数',
`detailDelay0` double(12,3) DEFAULT NULL COMMENT '第1次探测延迟',
`detailDelay1` double(12,3) DEFAULT NULL COMMENT '第2次探测延迟',
`detailDelay2` double(12,3) DEFAULT NULL COMMENT '第3次探测延迟',
`detailDelay3` double(12,3) DEFAULT NULL COMMENT '第4次探测延迟',
`detailDelay4` double(12,3) DEFAULT NULL COMMENT '第5次探测延迟',
PRIMARY KEY (`id`),
KEY `playerrouteinfo_index` (`userStamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tbl_playerrouteinfo
-- ----------------------------
shell脚本如下:
#!/bin/bash
HOST="127.0.0.1"PORT="3306"
USERNAME="upsadmin"
PASSWORD="ggDjIbegt0OTPF3f"
DATABASE="ups"
SQL="select count(1) ,1 as mode from playerbaseinfo where mode=1;"
SQL1="select sum(avgLoss) as totalLoss ,count(1) as sum ,'500' as value from (select max(r.avgDelay) delay, r.hopNum,r.userStamp,b.accountId , b.avgPacketLoss avgLoss from playerbaseinfo as b left join playerrouteinfo r on (r.userStamp = CONCAT(b.accountId,'_',b.reportTime)) where b.mode = 1 GROUP BY r.userStamp) B where B.delay>500 order by avgLoss"
SQL11="select (r.totalLoss/r.sum) as avgLoss,r.value from (${SQL1}) r;"
#echo ${SQL11}
SQL2="select sum(avgLoss)as totalLoss,count(1) as sum ,'400~500' as value from (select max(r.avgDelay) delay, r.hopNum,r.userStamp,b.accountId , b.avgPacketLoss avgLoss from playerbaseinfo as b left join playerrouteinfo r on (r.userStamp = CONCAT(b.accountId,'_',b.reportTime)) where b.mode = 1 GROUP BY r.userStamp) B where B.delay>400 and B.delay<500 order by avgLoss"
SQL12="select (r.totalLoss/r.sum) as avgLoss,r.value from (${SQL2}) r;"
SQL3="select sum(avgLoss) as totalLoss,count(1) as sum ,'300~400' as value from (select max(r.avgDelay) delay, r.hopNum,r.userStamp,b.accountId , b.avgPacketLoss avgLoss from playerbaseinfo as b left join playerrouteinfo r on (r.userStamp = CONCAT(b.accountId,'_',b.reportTime)) where b.mode = 1 GROUP BY r.userStamp) B where B.delay>300 and B.delay<=400 order by avgLoss"
SQL13="select (r.totalLoss/r.sum) as avgLoss,r.value from (${SQL3}) r;"
SQL4="select sum(avgLoss) as totalLoss,count(1) as sum ,'200~300' as value from (select max(r.avgDelay) delay, r.hopNum,r.userStamp,b.accountId , b.avgPacketLoss avgLoss from playerbaseinfo as b left join playerrouteinfo r on (r.userStamp = CONCAT(b.accountId,'_',b.reportTime)) where b.mode = 1 GROUP BY r.userStamp) B where B.delay>200 and B.delay<=300 order by avgLoss"
SQL14="select (r.totalLoss/r.sum) as avgLoss,r.value from (${SQL4}) r;"
SQL5="select sum(avgLoss)as totalLoss,count(1) as sum,'100~200' as value from (select max(r.avgDelay) delay, r.hopNum,r.userStamp,b.accountId , b.avgPacketLoss avgLoss from playerbaseinfo as b left join playerrouteinfo r on (r.userStamp = CONCAT(b.accountId,'_',b.reportTime)) where b.mode = 1 GROUP BY r.userStamp) B where B.delay>100 and B.delay<=200 order by avgLoss"
SQL15="select (r.totalLoss/r.sum) as avgLoss,r.value from (${SQL5}) r;"
SQL6="select sum(avgLoss) as totalLoss,count(1) as sum ,'0~100' as value from (select max(r.avgDelay) delay, r.hopNum,r.userStamp,b.accountId , b.avgPacketLoss avgLoss from playerbaseinfo as b left join playerrouteinfo r on (r.userStamp = CONCAT(b.accountId,'_',b.reportTime)) where b.mode = 1 GROUP BY r.userStamp) B where B.delay>0 and B.delay<=100 order by avgLoss"
SQL16="select (r.totalLoss/r.sum) as avgLoss,r.value from (${SQL6}) r;"
mysql -h${HOST} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DATABASE} -e "${SQL}"
mysql -h${HOST} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DATABASE} -e "${SQL1};${SQL2};${SQL3};${SQL4};${SQL5};${SQL6}"
mysql -h${HOST} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DATABASE} -e "${SQL11}${SQL12}${SQL13}${SQL14}${SQL15}${SQL16}"
shell脚本确实方便,不用再linux上连上mysql不停的输入sql语句。还学会了mysql算是比较高级的查询吧。