# taking from mysqlsla
sub compact_IN
{
   my $in = shift;

   my $t;  # type of vals: N or 'S'
   my $n;  # number of N or 'S' vals

   $t = ($in =~ /N/ ? 'N' : 'S');  # determine type of vals
   $n = ($in =~ tr/,//) + 1;       # count number of vals

   if($abstract_in)
   {
      use integer;
      my $z = $abstract_in;  # just for brevity
      $n = (($n / $z) * $z) . '-' . (((($n / $z) + 1) * $z) - 1);
   }

   #return "($t$n)";
   return "($t)";
}

# taking from mysqlsla
sub compact_VALUES
{
   my $vals = shift;

   my $n;  # number of (vals)
   my $v;

   $n = 1;
   $n++ while ($vals =~ /\)\s?\,\s?\(/g);  # count number of (vals)

   # take first (vals) if there are > 1
   if($n > 1) { ($v) = ($vals =~ /^(\(.+?\))\s?\,\s?\(/); }
   else       { $v = $vals; }

   return "$v" if $abstract_values;

   return "$v$n";
}

# taking from mysqlsla
sub abstract_stmt
{
   my $q = lc shift;  # scalar having statement to abstract

   my $t;  # position in q while compacting IN and VALUES

   # --- Regex copied from mysqldumpslow
   $q =~ s/\b\d+\b/N/go;
   $q =~ s/\b0x[0-9A-Fa-f]+\b/N/go;
   $q =~ s/''/'S'/go;
   $q =~ s/""/"S"/go;
   $q =~ s/(\\')//go;
   $q =~ s/(\\")//go;
   $q =~ s/'[^']+'/'S'/go;
   $q =~ s/"[^"]+"/"S"/go;
   # ---

   $q =~ s/^\s+//go;      # remove leading blank space
   $q =~ s/\s{2,}/ /go;   # compact 2 or more blank spaces to 1
   $q =~ s/\n/ /go;       # remove newlines
   $q =~ s/`//go;         # remove graves/backticks

   # compact IN clauses: (N, N, N) --> (N3)
   while ($q =~ m/( in\s?)/go)
   {
      $t = pos($q);
      $q =~ s/\G\((?=(?:N|'S'))(.+?)\)/compact_IN($1)/e;
      pos($q) = $t;
   }

   # compact VALUES clauses: (NULL, 'S'), (NULL, 'S') --> (NULL, 'S')2
   while ($q =~ m/( values\s?)/go)
   {
      $t = pos($q);
      $q =~ s/\G(.+?)(\s?)(;|on|\z)/compact_VALUES($1)."$2$3"/e;
      pos($q) = $t;
   }

   return $q;  # abstracted form of stmt
}

my ($time, $query, $thread, $aq);
my %stats;
my $count = 0;

foreach my $file (@ARGV)
{
    open FILE, $file;

    while (my $line = <FILE>)
    {
        # 2009-02-05 10:58:49 7456207 -- SELECT ...
        if ($line =~ /^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}) (\d+) \-\- (.*)$/)
        {
            # last query has finished, so process it now
            if ($time =~ /^2009\-02\-04 12\:5/)
            {
            	$aq = abstract_stmt($query);
            	$stats{$aq} = {
                	count => 0,
                	eg => $query,
            	} unless $stats{$aq};
            	$stats{$aq}{count}++;
            	$count++;
            }

            # first line of the query
            $time = $1;
            $thread = $2;
            $query = $3;
        }
        else
        {
            # the query is continued
            $query .= $line;
        }

    }
    close FILE;
}

print "Total: $count\n\n";

if ($count > 0)
{
    my @sorted = sort {$stats{$b}{count} <=> $stats{$a}{count}} keys(%stats);
    foreach $i (0..20)
    {
        $s = $stats{$sorted[$i]};
        $p = sprintf("%.2f", $$s{count}/$count*100);
        print "Count: $$s{count} ($p\%)\nAbstract: $sorted[$i]\nExample: $$s{eg}\n\n";
    }
}