in reply to Re^4: perl sql to separate and sort the column separately?
in thread perl sql to separate and sort the column separately?

Surely order by data1+data2 gives the same top 5 names just sorted differently whereas if you do separate queries the top 5 names might be different. To do both in 1 query I guess you could do something complicated like this 

#!perl
use strict; 
use DBI; 

my $DBH; my $data1; my $data2;
datas('dbname');
print "$data1\n$data2";

sub datas
{
  $data1 = "";
  $data2 = "";
  my $str = shift;
  $DBH = &connect or die "Cannot connect to the sql server \n";
  $DBH->do("USE $str;");
  my $stmt="
( select name,data1
  from datas 
  order by data1 desc 
  limit 5 )

union all
( select 'added' ,sum(data1) 
  from (
    select data1
    from datas 
    order by data1 desc 
    limit  18446744073709551615 offset 5
  ) t1
)

union all
( select name,data2
  from datas 
  order by data2 desc 
  limit 5 )

union all
( select 'added' ,sum(data2) 
  from (
    select data2
    from datas 
    order by data2 desc 
    limit  18446744073709551615 offset 5
  ) t1
)";
    
  my $sth = $DBH->prepare( $stmt );
  $sth->execute() or die $sth->errstr;
    
  my $tmp = 0;
  while(my @row_array=$sth->fetchrow_array)
  {
    if ($tmp > 5){
      if ($tmp == 6 ) {
        $data2 = "\[\"$row_array[0] \($row_array[1]\)\",$row_array[1]\
+]";
      }
      else {
        $data2 .= ",\[\"$row_array[0] \($row_array[1]\)\",$row_array[1
+]\]";
      }
    } else {
       if ($tmp == 0 ) {
         $data1 = "\[\"$row_array[0] \($row_array[1]\)\",$row_array[1]
+\]";
       }
       else {
         $data1 .= ",\[\"$row_array[0] \($row_array[1]\)\",$row_array[
+1]\]";
       }  
    }
    ++$tmp;
  }
  $sth->finish;
  $DBH->disconnect();
}

[download]
poj

Replies are listed 'Best First'.
Re^6: perl sql to separate and sort the column separately?
by huck (Prior) on Apr 12, 2017 at 13:59 UTC

    if 50 jobs were dumped into the queue system in-between runs of the separate queries the results would be quite different.

    the sort on the sum seemed to be to list the queues with the largest combined total, but then to sort each sublist by the elements in run/pending separately

    For each considered totally separate, if it were me, id just do it all in perl. 

    use strict; use warnings; 
use DBI; 
use JSON 'encode_json';
use cheap::mysqls qw/sql_connect/; 

  my ($DBH,$rc) = sql_connect(id=>'finddata',dbi=>'mysql');
#  my ($DBH,$rc) = sql_connect(id=>'finddata',dbi=>'sqlite');


  $DBH->do('DROP view IF EXISTS datas ');
  $DBH->do('create  
            view datas as
                 select queue_name as name, 
                       jobs_pend   as data1, 
                       jobs_run    as data2
                   from queues
           '); 



  my @arr; 
    my $stmt="select name,data1,data2 from datas ";

    my $sth = $DBH->prepare( $stmt );
    $sth->execute() or die $sth->errstr;
    
    while(my @row_array=$sth->fetchrow_array)
    {
             push @arr,[@row_array]; 
    }
    $sth->finish;

  print sumup(5,1,\@arr)."\n"; 
  print sumup(5,2,\@arr)."\n"; 

  $DBH->do('DROP view IF EXISTS datas ');
  $DBH->disconnect();

exit; 
sub sumup { 
  my $testn=shift; 
  my $col=shift; 
  my $arr=shift; 
  my @sort1=sort {$arr->[$b][$col] <=> $arr->[$a][$col]} 0..$#$arr; 
  my @out1; 
  my $rc=0; 
  for my $rown (@sort1) { 
    if ($rc<=$testn) { push @out1,[$arr->[$rown][0],$arr->[$rown][$col
+]];}
    else {$out1[-1][1]+=$arr->[$rown][$col]}
    $rc++;
  } 
  $out1[$testn][0]='added'; 
  return encode_json( \@out1 );
}

    [download]
    Result 
    [["adice_short","254"],["normal","170"],["ncsim_long","41"],["adice_lo
+ng","5"],["ncsim_lp","1"],["added",0]]
[["adice_short","192"],["ncsim_short","84"],["ncsim_long","78"],["adic
+e_long","39"],["normal","30"],["added",34]]

    [download]

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom