源数据:
mysql> select * from perf_status where stat_day > '2008-03-01' and spes_id = 1008 and (ip = '172.24.68.74' or ip = '172.24.68.75') order by ip,stat_day;
+------------+---------+--------------+---------+--------------------+---------+-----------+----------------+-----------+
| stat_day | spes_id | ip | day_max | day_max_time | day_sum | night_max | night_max_time | night_sum |
+------------+---------+--------------+---------+--------------------+---------+-----------+----------------+-----------+
| 2008-03-04 | 1008 | 172.24.68.74 | 16 | #22:00:00 | 160 | 10 | #04:50:00 | 98 |
| 2008-03-05 | 1008 | 172.24.68.74 | 14 | #10:40:00 | 221 | 18 | #08:40:00 | 124 |
| 2008-03-06 | 1008 | 172.24.68.74 | 18 | #09:30:00 | 177 | 11 | #08:45:00 | 102 |
| 2008-03-07 | 1008 | 172.24.68.74 | 11 | #09:30:00 | 89 | 13 | #04:50:00 | 108 |
| 2008-03-08 | 1008 | 172.24.68.74 | 50 | #21:45:00 | 92 | 12 | #07:55:00 | 104 |
| 2008-03-09 | 1008 | 172.24.68.74 | 10 | #22:40:00 | 43 | 11 | #05:00:00 | 128 |
| 2008-03-10 | 1008 | 172.24.68.74 | 15 | #09:25:00 | 175 | 11 | #05:10:00 | 103 |
| 2008-03-11 | 1008 | 172.24.68.74 | 17 | #16:10:00 | 166 | 13 | #08:45:00 | 106 |
| 2008-03-12 | 1008 | 172.24.68.74 | 11 | #22:55:00 | 149 | 10 | #05:00:00 | 90 |
| 2008-03-04 | 1008 | 172.24.68.75 | 23 | #11:20:00 | 412 | 13 | #08:55:00 | 134 |
| 2008-03-05 | 1008 | 172.24.68.75 | 25 | #21:45:00 | 370 | 17 | #08:50:00 | 152 |
| 2008-03-06 | 1008 | 172.24.68.75 | 18 | #15:55:00 | 253 | 14 | #08:20:00 | 151 |
| 2008-03-07 | 1008 | 172.24.68.75 | 15 | #10:35:00 | 175 | 15 | #08:45:00 | 125 |
| 2008-03-08 | 1008 | 172.24.68.75 | 8 | #00:05:00 | 29 | 17 | #05:00:00 | 154 |
| 2008-03-09 | 1008 | 172.24.68.75 | 12 | #18:55:00 | 73 | 23 | #08:25:00 | 164 |
| 2008-03-10 | 1008 | 172.24.68.75 | 16 | #09:15:00 | 297 | 10 | #08:35:00 | 132 |
| 2008-03-11 | 1008 | 172.24.68.75 | 10 | #21:55:00#22:35:00 | 215 | 18 | #05:10:00 | 108 |
| 2008-03-12 | 1008 | 172.24.68.75 | 20 | #11:45:00 | 309 | 13 | #04:55:00 | 112 |
+------------+---------+--------------+---------+--------------------+---------+-----------+----------------+-----------+
18 rows in set (0.19 sec)
perl脚本:
#! /usr/bin/perl
use lib './';
use DBI;
use Data::Dumper;
use JSON;
my $dsn='dbi:mysql:dbStatus;192.168.182.5';
my $user='dbstatus';
my $pass='*******;
my $schema = DBI->connect($dsn,$user,$pass);
my $sql = qq {
select * from perf_status where stat_day > '2008-03-01' and spes_id = 1008 and (ip = '172.24.68.74' or ip = '172.24.68.75'
) order by ip,stat_day;
};
my $hashref = $schema->selectall_hashref($sql,['ip','stat_day']);
print Data::Dumper->Dumper($hashref);
$schema->disconnect();
Dump结果:
[root@robincui cbs_pre]# ./pf.pl
$VAR1 = 'Data::Dumper';
$VAR2 = {
'172.24.68.74' => {
'2008-03-12' => {
'stat_day' => '2008-03-12',
'ip' => '172.24.68.74',
'day_max' => '11',
'day_sum' => '149',
'night_max_time' => '#05:00:00',
'night_sum' => '90',
'spes_id' => '1008',
'day_max_time' => '#22:55:00',
'night_max' => '10'
},
'2008-03-07' => {
'stat_day' => '2008-03-07',
'ip' => '172.24.68.74',
'day_max' => '11',
'day_sum' => '89',
'night_max_time' => '#04:50:00',
'night_sum' => '108',
'spes_id' => '1008',
'day_max_time' => '#09:30:00',
'night_max' => '13'
},
'2008-03-05' => {
'stat_day' => '2008-03-05',
'ip' => '172.24.68.74',
'day_max' => '14',
'day_sum' => '221',
'night_max_time' => '#08:40:00',
'night_sum' => '124',
'spes_id' => '1008',
'day_max_time' => '#10:40:00',
'night_max' => '18'
},
'2008-03-11' => {
'stat_day' => '2008-03-11',
'ip' => '172.24.68.74',
'day_max' => '17',
'day_sum' => '166',
'night_max_time' => '#08:45:00',
'night_sum' => '106',
'spes_id' => '1008',
'day_max_time' => '#16:10:00',
'night_max' => '13'
},
'2008-03-10' => {
'stat_day' => '2008-03-10',
'ip' => '172.24.68.74',
'day_max' => '15',
'day_sum' => '175',
'night_max_time' => '#05:10:00',
'night_sum' => '103',
'spes_id' => '1008',
'day_max_time' => '#09:25:00',
'night_max' => '11'
},
'2008-03-08' => {
'stat_day' => '2008-03-08',
'ip' => '172.24.68.74',
'day_max' => '50',
'day_sum' => '92',
'night_max_time' => '#07:55:00',
'night_sum' => '104',
'spes_id' => '1008',
'day_max_time' => '#21:45:00',
'night_max' => '12'
},
'2008-03-04' => {
'stat_day' => '2008-03-04',
'ip' => '172.24.68.74',
'day_max' => '16',
'day_sum' => '160',
'night_max_time' => '#04:50:00',
'night_sum' => '98',
'spes_id' => '1008',
'day_max_time' => '#22:00:00',
'night_max' => '10'
},
'2008-03-09' => {
'stat_day' => '2008-03-09',
'ip' => '172.24.68.74',
'day_max' => '10',
'day_sum' => '43',
'night_max_time' => '#05:00:00',
'night_sum' => '128',
'spes_id' => '1008',
'day_max_time' => '#22:40:00',
'night_max' => '11'
},
'2008-03-06' => {
'stat_day' => '2008-03-06',
'ip' => '172.24.68.74',
'day_max' => '18',
'day_sum' => '177',
'night_max_time' => '#08:45:00',
'night_sum' => '102',
'spes_id' => '1008',
'day_max_time' => '#09:30:00',
'night_max' => '11'
}
},
'172.24.68.75' => {
'2008-03-12' => {
'stat_day' => '2008-03-12',
'ip' => '172.24.68.75',
'day_max' => '20',
'day_sum' => '309',
'night_max_time' => '#04:55:00',
'night_sum' => '112',
'spes_id' => '1008',
'day_max_time' => '#11:45:00',
'night_max' => '13'
},
'2008-03-07' => {
'stat_day' => '2008-03-07',
'ip' => '172.24.68.75',
'day_max' => '15',
'day_sum' => '175',
'night_max_time' => '#08:45:00',
'night_sum' => '125',
'spes_id' => '1008',
'day_max_time' => '#10:35:00',
'night_max' => '15'
},
'2008-03-05' => {
'stat_day' => '2008-03-05',
'ip' => '172.24.68.75',
'day_max' => '25',
'day_sum' => '370',
'night_max_time' => '#08:50:00',
'night_sum' => '152',
'spes_id' => '1008',
'day_max_time' => '#21:45:00',
'night_max' => '17'
},
'2008-03-11' => {
'stat_day' => '2008-03-11',
'ip' => '172.24.68.75',
'day_max' => '10',
'day_sum' => '215',
'night_max_time' => '#05:10:00',
'night_sum' => '108',
'spes_id' => '1008',
'day_max_time' => '#21:55:00#22:35:00',
'night_max' => '18'
},
'2008-03-10' => {
'stat_day' => '2008-03-10',
'ip' => '172.24.68.75',
'day_max' => '16',
'day_sum' => '297',
'night_max_time' => '#08:35:00',
'night_sum' => '132',
'spes_id' => '1008',
'day_max_time' => '#09:15:00',
'night_max' => '10'
},
'2008-03-08' => {
'stat_day' => '2008-03-08',
'ip' => '172.24.68.75',
'day_max' => '8',
'day_sum' => '29',
'night_max_time' => '#05:00:00',
'night_sum' => '154',
'spes_id' => '1008',
'day_max_time' => '#00:05:00',
'night_max' => '17'
},
'2008-03-04' => {
'stat_day' => '2008-03-04',
'ip' => '172.24.68.75',
'day_max' => '23',
'day_sum' => '412',
'night_max_time' => '#08:55:00',
'night_sum' => '134',
'spes_id' => '1008',
'day_max_time' => '#11:20:00',
'night_max' => '13'
},
'2008-03-09' => {
'stat_day' => '2008-03-09',
'ip' => '172.24.68.75',
'day_max' => '12',
'day_sum' => '73',
'night_max_time' => '#08:25:00',
'night_sum' => '164',
'spes_id' => '1008',
'day_max_time' => '#18:55:00',
'night_max' => '23'
},
'2008-03-06' => {
'stat_day' => '2008-03-06',
'ip' => '172.24.68.75',
'day_max' => '18',
'day_sum' => '253',
'night_max_time' => '#08:20:00',
&nbs
mysql> select * from perf_status where stat_day > '2008-03-01' and spes_id = 1008 and (ip = '172.24.68.74' or ip = '172.24.68.75') order by ip,stat_day;
+------------+---------+--------------+---------+--------------------+---------+-----------+----------------+-----------+
| stat_day | spes_id | ip | day_max | day_max_time | day_sum | night_max | night_max_time | night_sum |
+------------+---------+--------------+---------+--------------------+---------+-----------+----------------+-----------+
| 2008-03-04 | 1008 | 172.24.68.74 | 16 | #22:00:00 | 160 | 10 | #04:50:00 | 98 |
| 2008-03-05 | 1008 | 172.24.68.74 | 14 | #10:40:00 | 221 | 18 | #08:40:00 | 124 |
| 2008-03-06 | 1008 | 172.24.68.74 | 18 | #09:30:00 | 177 | 11 | #08:45:00 | 102 |
| 2008-03-07 | 1008 | 172.24.68.74 | 11 | #09:30:00 | 89 | 13 | #04:50:00 | 108 |
| 2008-03-08 | 1008 | 172.24.68.74 | 50 | #21:45:00 | 92 | 12 | #07:55:00 | 104 |
| 2008-03-09 | 1008 | 172.24.68.74 | 10 | #22:40:00 | 43 | 11 | #05:00:00 | 128 |
| 2008-03-10 | 1008 | 172.24.68.74 | 15 | #09:25:00 | 175 | 11 | #05:10:00 | 103 |
| 2008-03-11 | 1008 | 172.24.68.74 | 17 | #16:10:00 | 166 | 13 | #08:45:00 | 106 |
| 2008-03-12 | 1008 | 172.24.68.74 | 11 | #22:55:00 | 149 | 10 | #05:00:00 | 90 |
| 2008-03-04 | 1008 | 172.24.68.75 | 23 | #11:20:00 | 412 | 13 | #08:55:00 | 134 |
| 2008-03-05 | 1008 | 172.24.68.75 | 25 | #21:45:00 | 370 | 17 | #08:50:00 | 152 |
| 2008-03-06 | 1008 | 172.24.68.75 | 18 | #15:55:00 | 253 | 14 | #08:20:00 | 151 |
| 2008-03-07 | 1008 | 172.24.68.75 | 15 | #10:35:00 | 175 | 15 | #08:45:00 | 125 |
| 2008-03-08 | 1008 | 172.24.68.75 | 8 | #00:05:00 | 29 | 17 | #05:00:00 | 154 |
| 2008-03-09 | 1008 | 172.24.68.75 | 12 | #18:55:00 | 73 | 23 | #08:25:00 | 164 |
| 2008-03-10 | 1008 | 172.24.68.75 | 16 | #09:15:00 | 297 | 10 | #08:35:00 | 132 |
| 2008-03-11 | 1008 | 172.24.68.75 | 10 | #21:55:00#22:35:00 | 215 | 18 | #05:10:00 | 108 |
| 2008-03-12 | 1008 | 172.24.68.75 | 20 | #11:45:00 | 309 | 13 | #04:55:00 | 112 |
+------------+---------+--------------+---------+--------------------+---------+-----------+----------------+-----------+
18 rows in set (0.19 sec)
perl脚本:
#! /usr/bin/perl
use lib './';
use DBI;
use Data::Dumper;
use JSON;
my $dsn='dbi:mysql:dbStatus;192.168.182.5';
my $user='dbstatus';
my $pass='*******;
my $schema = DBI->connect($dsn,$user,$pass);
my $sql = qq {
select * from perf_status where stat_day > '2008-03-01' and spes_id = 1008 and (ip = '172.24.68.74' or ip = '172.24.68.75'
) order by ip,stat_day;
};
my $hashref = $schema->selectall_hashref($sql,['ip','stat_day']);
print Data::Dumper->Dumper($hashref);
$schema->disconnect();
Dump结果:
[root@robincui cbs_pre]# ./pf.pl
$VAR1 = 'Data::Dumper';
$VAR2 = {
'172.24.68.74' => {
'2008-03-12' => {
'stat_day' => '2008-03-12',
'ip' => '172.24.68.74',
'day_max' => '11',
'day_sum' => '149',
'night_max_time' => '#05:00:00',
'night_sum' => '90',
'spes_id' => '1008',
'day_max_time' => '#22:55:00',
'night_max' => '10'
},
'2008-03-07' => {
'stat_day' => '2008-03-07',
'ip' => '172.24.68.74',
'day_max' => '11',
'day_sum' => '89',
'night_max_time' => '#04:50:00',
'night_sum' => '108',
'spes_id' => '1008',
'day_max_time' => '#09:30:00',
'night_max' => '13'
},
'2008-03-05' => {
'stat_day' => '2008-03-05',
'ip' => '172.24.68.74',
'day_max' => '14',
'day_sum' => '221',
'night_max_time' => '#08:40:00',
'night_sum' => '124',
'spes_id' => '1008',
'day_max_time' => '#10:40:00',
'night_max' => '18'
},
'2008-03-11' => {
'stat_day' => '2008-03-11',
'ip' => '172.24.68.74',
'day_max' => '17',
'day_sum' => '166',
'night_max_time' => '#08:45:00',
'night_sum' => '106',
'spes_id' => '1008',
'day_max_time' => '#16:10:00',
'night_max' => '13'
},
'2008-03-10' => {
'stat_day' => '2008-03-10',
'ip' => '172.24.68.74',
'day_max' => '15',
'day_sum' => '175',
'night_max_time' => '#05:10:00',
'night_sum' => '103',
'spes_id' => '1008',
'day_max_time' => '#09:25:00',
'night_max' => '11'
},
'2008-03-08' => {
'stat_day' => '2008-03-08',
'ip' => '172.24.68.74',
'day_max' => '50',
'day_sum' => '92',
'night_max_time' => '#07:55:00',
'night_sum' => '104',
'spes_id' => '1008',
'day_max_time' => '#21:45:00',
'night_max' => '12'
},
'2008-03-04' => {
'stat_day' => '2008-03-04',
'ip' => '172.24.68.74',
'day_max' => '16',
'day_sum' => '160',
'night_max_time' => '#04:50:00',
'night_sum' => '98',
'spes_id' => '1008',
'day_max_time' => '#22:00:00',
'night_max' => '10'
},
'2008-03-09' => {
'stat_day' => '2008-03-09',
'ip' => '172.24.68.74',
'day_max' => '10',
'day_sum' => '43',
'night_max_time' => '#05:00:00',
'night_sum' => '128',
'spes_id' => '1008',
'day_max_time' => '#22:40:00',
'night_max' => '11'
},
'2008-03-06' => {
'stat_day' => '2008-03-06',
'ip' => '172.24.68.74',
'day_max' => '18',
'day_sum' => '177',
'night_max_time' => '#08:45:00',
'night_sum' => '102',
'spes_id' => '1008',
'day_max_time' => '#09:30:00',
'night_max' => '11'
}
},
'172.24.68.75' => {
'2008-03-12' => {
'stat_day' => '2008-03-12',
'ip' => '172.24.68.75',
'day_max' => '20',
'day_sum' => '309',
'night_max_time' => '#04:55:00',
'night_sum' => '112',
'spes_id' => '1008',
'day_max_time' => '#11:45:00',
'night_max' => '13'
},
'2008-03-07' => {
'stat_day' => '2008-03-07',
'ip' => '172.24.68.75',
'day_max' => '15',
'day_sum' => '175',
'night_max_time' => '#08:45:00',
'night_sum' => '125',
'spes_id' => '1008',
'day_max_time' => '#10:35:00',
'night_max' => '15'
},
'2008-03-05' => {
'stat_day' => '2008-03-05',
'ip' => '172.24.68.75',
'day_max' => '25',
'day_sum' => '370',
'night_max_time' => '#08:50:00',
'night_sum' => '152',
'spes_id' => '1008',
'day_max_time' => '#21:45:00',
'night_max' => '17'
},
'2008-03-11' => {
'stat_day' => '2008-03-11',
'ip' => '172.24.68.75',
'day_max' => '10',
'day_sum' => '215',
'night_max_time' => '#05:10:00',
'night_sum' => '108',
'spes_id' => '1008',
'day_max_time' => '#21:55:00#22:35:00',
'night_max' => '18'
},
'2008-03-10' => {
'stat_day' => '2008-03-10',
'ip' => '172.24.68.75',
'day_max' => '16',
'day_sum' => '297',
'night_max_time' => '#08:35:00',
'night_sum' => '132',
'spes_id' => '1008',
'day_max_time' => '#09:15:00',
'night_max' => '10'
},
'2008-03-08' => {
'stat_day' => '2008-03-08',
'ip' => '172.24.68.75',
'day_max' => '8',
'day_sum' => '29',
'night_max_time' => '#05:00:00',
'night_sum' => '154',
'spes_id' => '1008',
'day_max_time' => '#00:05:00',
'night_max' => '17'
},
'2008-03-04' => {
'stat_day' => '2008-03-04',
'ip' => '172.24.68.75',
'day_max' => '23',
'day_sum' => '412',
'night_max_time' => '#08:55:00',
'night_sum' => '134',
'spes_id' => '1008',
'day_max_time' => '#11:20:00',
'night_max' => '13'
},
'2008-03-09' => {
'stat_day' => '2008-03-09',
'ip' => '172.24.68.75',
'day_max' => '12',
'day_sum' => '73',
'night_max_time' => '#08:25:00',
'night_sum' => '164',
'spes_id' => '1008',
'day_max_time' => '#18:55:00',
'night_max' => '23'
},
'2008-03-06' => {
'stat_day' => '2008-03-06',
'ip' => '172.24.68.75',
'day_max' => '18',
'day_sum' => '253',
'night_max_time' => '#08:20:00',
&nbs
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/82392/viewspace-211671/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/82392/viewspace-211671/