小弟采用附件中的MR數(shù)據(jù)的提取腳本去提取數(shù)據(jù)，提取到的數(shù)據(jù)量相當(dāng)大。如圖是一個(gè)BSC的一個(gè)早忙時(shí)的數(shù)據(jù)：
[localimg=360,69]1[/localimg]
求高手指點(diǎn)如何去修改腳本。

use strict;
print "Enter your sqlplus user_name:\n";
#my $DBUSER=<STDIN>;
my $DBUSER="替換成賬戶";
$DBUSER=~s/\s//g;
print "Enter your sqlplus password:\n";
#my $PWORD=<STDIN>;
my $PWORD="替換成密碼";
$PWORD=~s/\s//g;
&print_CF_Query();
system ("sqlplus -S $DBUSER/$PWORD \@cf.sql");
unlink ("cf.sql");

&print_DAC_Query();
system ("sqlplus -S $DBUSER/$PWORD \@dac.sql");
unlink ("dac.sql");
&print_rx_stats_Query();
system ("sqlplus -S $DBUSER/$PWORD \@rx_stats.sql");
unlink ("rx_stats.sql");


sub print_CF_Query
{
open (QUERY,">cf.sql");
print QUERY "set pagesize 32000\n";
print QUERY "set linesize 1000\n";
print QUERY "set trimspool on\n";
print QUERY "set verify off\n";
print QUERY "set termout off\n";
print QUERY "set feedback off\n\n";
print QUERY "COLUMN header HEADING 'DATE PERIOD_START_TIME DURATION BTS_INT_ID BSC_NAME BTS_NAME BTS_ID NCC BCC BCCH DB_VALUE_LOW DB_VALUE_HIGH AVE_DL_SIGNAL_STRENGTH STANDARD_DEVIATION NUM_OF_SAMPLES_IN_CLASS_1 NUM_OF_SAMPLES_IN_CLASS_2 NUM_OF_SAMPLES_IN_CLASS_3'\n\n";

print QUERY "spool cf.txt\n\n";
print QUERY "SELECT\n";
print QUERY "TO_CHAR(c.period_start_time,'MM/DD/RR')\n";
print QUERY "|| CHR(9) || TO_CHAR(c.period_start_time,'HH24:\"00:00\"')\n";
print QUERY "|| CHR(9) || c.period_duration\n";
print QUERY "|| CHR(9) || c.bts_int_id\n";
print QUERY "|| CHR(9) || o_bsc.name\n";
print QUERY "|| CHR(9) || o_bts.name\n";
print QUERY "|| CHR(9) || c.bts_id\n";
print QUERY "|| CHR(9) || c.ncc\n";
print QUERY "|| CHR(9) || c.bcc\n";
print QUERY "|| CHR(9) || c.bcch\n";
print QUERY "|| CHR(9) || c.db_value_low\n";
print QUERY "|| CHR(9) || c.db_value_high\n";
print QUERY "|| CHR(9) || c.ave_dl_signal_strength\n";
print QUERY "|| CHR(9) || c.standard_deviation\n";
print QUERY "|| CHR(9) || c.num_of_samples_in_class_1\n";
print QUERY "|| CHR(9) || c.num_of_samples_in_class_2\n";
print QUERY "|| CHR(9) || c.num_of_samples_in_class_3 header\n";
print QUERY "FROM \n";
print QUERY "p_nbsc_channel_finder c,\n";
print QUERY "objects o_bts,\n";
print QUERY "objects o_bsc\n";
print QUERY "WHERE\n";
print QUERY "to_char(c.period_start_time,'mmddyy')=TO_CHAR(sysdate-1, 'MMDDRR') \n";
print QUERY "AND o_bts.int_id=c.bts_int_id\n";
print QUERY "AND o_bts.object_class=4\n";
print QUERY "AND o_bsc.object_class=3\n";
print QUERY "AND o_bsc.name in ('BSC21') \n";
print QUERY "GROUP BY\n";
print QUERY "TO_CHAR(c.period_start_time,'MM/DD/RR'),\n";
print QUERY "TO_CHAR(c.period_start_time,'HH24:\"00:00\"'),\n";
print QUERY "c.period_duration,\n";
print QUERY "c.bts_int_id,\n";
print QUERY "o_bsc.name,\n";
print QUERY "o_bts.name,\n";
print QUERY "c.bts_id,\n";
print QUERY "c.ncc,\n";
print QUERY "c.bcc,\n";
print QUERY "c.bcch,\n";
print QUERY "c.db_value_low,\n";
print QUERY "c.db_value_high,\n";
print QUERY "c.ave_dl_signal_strength,\n";
print QUERY "c.standard_deviation,\n";
print QUERY "c.num_of_samples_in_class_1,\n";
print QUERY "c.num_of_samples_in_class_2,\n";
print QUERY "c.num_of_samples_in_class_3\n";
print QUERY ";\n";
print QUERY "spool off\n\n";
print QUERY "exit\n";
close (QUERY);
}
sub print_DAC_Query
{
open (QUERY,">dac.sql");
print QUERY "set pagesize 32000\n";
print QUERY "set linesize 1000\n";
print QUERY "set trimspool on\n";
print QUERY "set verify off\n";
print QUERY "set termout off\n";
print QUERY "set feedback off\n\n";
print QUERY "COLUMN header HEADING 'DATE PERIOD_START_TIME DURATION BTS_INT_ID BSC_NAME BTS_NAME BTS_ID NCC BCC BCCH DB_VALUE_LOW DB_VALUE_HIGH AVE_DL_SIG_STR_SERV_CELL_SUM AVE_DL_SIG_STR_SERV_CELL_DEN STD_DEV_OF_SERV_CELL AVE_DL_SIG_STR_ADJ_CELL STD_DEV_OF_ADJ_CELL NBR_OF_SAMPLES_IN_CLASS_1 NBR_OF_SAMPLES_IN_CLASS_2 NBR_OF_SAMPLES_IN_CLASS_3'\n\n";

print QUERY "spool dac.txt\n\n";
print QUERY "SELECT\n";
print QUERY "TO_CHAR(d.period_start_time,'MM/DD/RR')\n";
print QUERY "|| CHR(9) || TO_CHAR(d.period_start_time,'HH24:\"00:00\"')\n";
print QUERY "|| CHR(9) || d.period_duration\n";
print QUERY "|| CHR(9) || d.bts_int_id\n";
print QUERY "|| CHR(9) || o_bsc.name\n";
print QUERY "|| CHR(9) || o_bts.name\n";
print QUERY "|| CHR(9) || d.bts_id\n";
print QUERY "|| CHR(9) || d.ncc\n";
print QUERY "|| CHR(9) || d.bcc\n";
print QUERY "|| CHR(9) || d.bcch\n";
print QUERY "|| CHR(9) || d.db_value_low\n";
print QUERY "|| CHR(9) || d.db_value_high\n";
print QUERY "|| CHR(9) || d.ave_dl_sig_str_serv_cell_sum\n";
print QUERY "|| CHR(9) || d.ave_dl_sig_str_serv_cell_den\n";
print QUERY "|| CHR(9) || d.std_dev_of_serv_cell\n";
print QUERY "|| CHR(9) || d.ave_dl_sig_str_adj_cell\n";
print QUERY "|| CHR(9) || d.std_dev_of_adj_cell\n";
print QUERY "|| CHR(9) || d.nbr_of_samples_in_class_1\n";
print QUERY "|| CHR(9) || d.nbr_of_samples_in_class_2\n";
print QUERY "|| CHR(9) || d.nbr_of_samples_in_class_3header\n";
print QUERY "FROM \n";
print QUERY "p_nbsc_def_adj_cell d,\n";
print QUERY "objects o_bts,\n";
print QUERY "objects o_bsc\n";
print QUERY "WHERE\n";
print QUERY "to_char(d.period_start_time,'mmddyy')=TO_CHAR(sysdate-1, 'MMDDRR') \n";
print QUERY "AND o_bts.int_id=d.bts_int_id\n";
print QUERY "AND o_bts.object_class=4\n";
print QUERY "AND o_bsc.object_class=3\n";
print QUERY "AND o_bsc.name in ('BSC21') \n";
print QUERY "GROUP BY\n";
print QUERY "TO_CHAR(d.period_start_time,'MM/DD/RR'),\n";
print QUERY "TO_CHAR(d.period_start_time,'HH24:\"00:00\"'),\n";
print QUERY "d.period_duration,\n";
print QUERY "d.bts_int_id,\n";
print QUERY "o_bsc.name,\n";
print QUERY "o_bts.name,\n";
print QUERY "d.BTS_ID,\n";
print QUERY "d.ncc,\n";
print QUERY "d.bcc,\n";
print QUERY "d.bcch,\n";
print QUERY "d.db_value_low,\n";
print QUERY "d.db_value_high,\n";
print QUERY "d.ave_dl_sig_str_serv_cell_sum,\n";
print QUERY "d.ave_dl_sig_str_serv_cell_den,\n";
print QUERY "d.std_dev_of_serv_cell,\n";
print QUERY "d.ave_dl_sig_str_adj_cell,\n";
print QUERY "d.std_dev_of_adj_cell,\n";
print QUERY "d.nbr_of_samples_in_class_1,\n";
print QUERY "d.nbr_of_samples_in_class_2,\n";
print QUERY "d.nbr_of_samples_in_class_3\n";
print QUERY ";\n";
print QUERY "spool off\n\n";
print QUERY "exit\n";
close (QUERY);
}

sub print_rx_stats_Query
{
open (QUERY,">rx_stats.sql");
print QUERY "set pagesize 50000\n";
print QUERY "set linesize 10000\n";
print QUERY "set trimspool on\n";
print QUERY "set verify off\n";
print QUERY "set feedback off\n\n";
print QUERY "spool rx_stats.txt\n\n";
print QUERY "COLUMN header HEADING 'BSC_NAME BTS_NAME START STOP PERIOD_DURATION BTS_ID TRX_ID BSC_INT_ID BTS_INT_ID TRX_INT_ID CLASS_UPPER_RANGE FREQ_UL_QUAL0 FREQ_UL_QUAL1 FREQ_UL_QUAL2 FREQ_UL_QUAL3 FREQ_UL_QUAL4 FREQ_UL_QUAL5 FREQ_UL_QUAL6 FREQ_UL_QUAL7 FREQ_DL_QUAL0 FREQ_DL_QUAL1 FREQ_DL_QUAL2 FREQ_DL_QUAL3 FREQ_DL_QUAL4 FREQ_DL_QUAL5 FREQ_DL_QUAL6 FREQ_DL_QUAL7'\n\n";
print QUERY "SELECT\n";
print QUERY "o_bsc.name\n";
print QUERY "|| CHR(9) ||o_bts.name\n";
print QUERY "|| CHR(9) ||TO_CHAR(rx.period_start_time,'RRRRMMDDHH24')\n";
print QUERY "|| CHR(9) ||TO_CHAR(rx.period_stop_time,'RRRRMMDDHH24')\n";
print QUERY "|| CHR(9) ||rx.period_duration\n";
print QUERY "|| CHR(9) ||rx.bts_id\n";
print QUERY "|| CHR(9) ||rx.trx_id\n";
print QUERY "|| CHR(9) ||rx.int_id\n";
print QUERY "|| CHR(9) ||rx.bts_int_id\n";
print QUERY "|| CHR(9) ||rx.trx_int_id\n";
print QUERY "|| CHR(9) ||rx.class_upper_range\n";
print QUERY "|| CHR(9) ||sum(rx.freq_ul_qual0)\n";
print QUERY "|| CHR(9) ||sum(rx.freq_ul_qual1)\n";
print QUERY "|| CHR(9) ||sum(rx.freq_ul_qual2)\n";
print QUERY "|| CHR(9) ||sum(rx.freq_ul_qual3)\n";
print QUERY "|| CHR(9) ||sum(rx.freq_ul_qual4)\n";
print QUERY "|| CHR(9) ||sum(rx.freq_ul_qual5)\n";
print QUERY "|| CHR(9) ||sum(rx.freq_ul_qual6)\n";
print QUERY "|| CHR(9) ||sum(rx.freq_ul_qual7)\n";
print QUERY "|| CHR(9) ||sum(rx.freq_dl_qual0)\n";
print QUERY "|| CHR(9) ||sum(rx.freq_dl_qual1)\n";
print QUERY "|| CHR(9) ||sum(rx.freq_dl_qual2)\n";
print QUERY "|| CHR(9) ||sum(rx.freq_dl_qual3)\n";
print QUERY "|| CHR(9) ||sum(rx.freq_dl_qual4)\n";
print QUERY "|| CHR(9) ||sum(rx.freq_dl_qual5)\n";
print QUERY "|| CHR(9) ||sum(rx.freq_dl_qual6)\n";
print QUERY "|| CHR(9) ||sum(rx.freq_dl_qual7) header\n";
print QUERY "FROM \n";
print QUERY "p_nbsc_rx_statistics rx,\n";
print QUERY "objects o_bts, \n";
print QUERY "objects o_bsc \n";
print QUERY "WHERE\n";
print QUERY "TO_CHAR(rx.period_start_time,'MMDDRR') = to_char(sysdate-1, 'MMDDRR')\n";
print QUERY "AND TO_CHAR(rx.period_start_time,'HH24') between 08 and 11\n";
print QUERY "AND o_bts.int_id=rx.bts_int_id\n";
print QUERY "AND o_bts.object_class=4\n";
print QUERY "AND o_bsc.int_id=rx.int_id\n";
print QUERY "AND o_bsc.object_class=3 \n";
print QUERY "AND o_bsc.name in ('BSC21') \n";
print QUERY "GROUP BY\n";
print QUERY "TO_CHAR(rx.period_start_time,'RRRRMMDDHH24'),\n";
print QUERY "TO_CHAR(rx.period_stop_time,'RRRRMMDDHH24'),\n";
print QUERY "rx.period_duration,\n";
print QUERY "rx.int_id,\n";
print QUERY "o_bsc.name,\n";
print QUERY "rx.bts_int_id,\n";
print QUERY "o_bts.name,\n";
print QUERY "rx.bts_id,\n";
print QUERY "rx.trx_id,\n";
print QUERY "rx.trx_int_id,\n";
print QUERY "rx.class_upper_range\n";
print QUERY "ORDER BY\n";
print QUERY "TO_CHAR(rx.period_start_time,'RRRRMMDDHH24'),\n";
print QUERY "TO_CHAR(rx.period_stop_time,'RRRRMMDDHH24'),\n";
print QUERY "rx.period_duration,\n";
print QUERY "rx.int_id,\n";
print QUERY "o_bsc.name,\n";
print QUERY "rx.bts_int_id,\n";
print QUERY "o_bts.name,\n";
print QUERY "rx.bts_id,\n";
print QUERY "rx.trx_id\n";
print QUERY ";\n\n";
print QUERY "spool off\n";
print QUERY "exit\n";
close (QUERY);
}