-3

Please help me to find the no of occurrences in file. also to find the max and min values. Im able to find the no of occurrences.

file

SQL :: select * from person ...etc , Time Taken :: 30 
SQL :: select * from emp ...etc , Time Taken :: 5 
SQL :: select * from home ...etc , Time Taken :: 20 
SQL :: select * from emp ...etc , Time Taken :: 30 
SQL :: select * from person ...etc , Time Taken :: 10 
SQL :: select * from home ...etc , Time Taken :: 20 
SQL :: select * from person ...etc , Time Taken :: 50

output should beenter code here

SQL                    No of occu        total Min  Max 
select * from person    3                  90   10   50 
select * from emp       2                  35    5   30
Miller
  • 34,962
  • 4
  • 39
  • 60
user3265858
  • 1
  • 2
  • 1
    Your question could do with a bit more detail. Like - why is this a perl question, rather than a SQL question, for starters. – Sobrique Jul 14 '14 at 08:39
  • [What have you tried?](http://whathaveyoutried.com/) SO is not a write-some-code-for-me site. You'll have to try for yourself and when you're stuck somewhere then state your problem here and you'll get help. – dgw Jul 14 '14 at 08:42
  • Im reading this from file... Im generating report like each SQLs how mach time taking and min , max time of execution. – user3265858 Jul 14 '14 at 09:52

1 Answers1

0
#!/usr/bin/perl


# sql -> {times, min, max}
my $data = {};
while (<>) {
    if (m{SQL.*?(select.*?\.\.\.).*?Taken\s\:\:\s(\d+)}xmsi) {
        my ($sql, $time) = ($1, $2);
        my $times = $data->{$sql}->{times} || 0;
        my $min = $data->{$sql}->{min} || $time;
        my $max = $data->{$sql}->{max} || $time;
        $max = $time if $time > $max;
        $min = $time if $time < $min;
        $times++;

        $data->{$sql}->{min} = $min;
        $data->{$sql}->{max} = $max;
        $data->{$sql}->{times} = $times;
    }
}

print "SQL No of occu total Min Max\n";
foreach my $sql(sort keys %{$data}) {
    print "$sql $data->{$sql}->{times} total $data->{$sql}->{min} $data->{$sql}->{max}\n";
}

running:

stat.pl <your data file>

add total yourself.

javamonk
  • 180
  • 1
  • 7
  • Thank you so much.. It works. but im having issue with output in CSV.. if the column(SQLs) contains any comma (,) or question mark (?) then its split-ed with many columns. Tried Encoding => 'base64' in the attachments. but still im facing the same issue. please suggest. – user3265858 Jul 14 '14 at 13:59
  • You can use \' to escape comma, it's difficult to parse that csv, use '|' may be better. Using base64 is a better way. For data line "sql_in_base64,time", my ($sql_base64, $time) = split(/,/, $_); $sql = decodebase64($sql_base64); – javamonk Jul 15 '14 at 04:04