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

Create two SQL queries and have the sorting there, according to the order you need. Then run each query separately.

I disagree, if only on a technicality. Back to the idea that these are statistics from a batch scheduler, the chance that the underlying data may have changed between the separate query runs is not zero. Instead i would run one select so there is just a single select query cursor instance, and sort the data separately in perl as in 

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');


  $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 @col1; 
my @col2; 
my @oth1; 
my @oth2; 

    my $stmt="select name,data1,data2 from((select name,data1,data2 fr
+om datas order by data1 +data2 desc limit 5  )union all(select 'added
+' as name ,sum(data1)  as data1 ,sum(data2) as data2 from (select dat
+a1,data2 from datas order by data2+data1 desc limit  1844674407370955
+1615 offset 5 ) new)) new;";
    
    my $sth = $DBH->prepare( $stmt );
    $sth->execute() or die $sth->errstr;
    
    my $tmp = 0;
    while(my @row_array=$sth->fetchrow_array)
    {
          if ($row_array[0] ne 'added') { 
             push @col1,[$row_array[0],$row_array[1]]; 
             push @col2,[$row_array[0],$row_array[2]]; 
             } 
          else { 
             push @oth1,[$row_array[0],$row_array[1]]; 
             push @oth2,[$row_array[0],$row_array[2]]; 
             } 
    }
    $sth->finish;

my @sort1=((sort {$b->[1] <=> $a->[1]} @col1),@oth1); 
my $json1 = encode_json( \@sort1 );
print $json1."\n"; 

my @sort2=((sort {$b->[1] <=> $a->[1]} @col2),@oth2); 
my $json2 = encode_json( \@sort2 );
print $json2."\n"; 

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

[download]

Since they included the json producing code for the creating of the pie charts, i have done so as well. That also explains why they want the separate sorts.

To also add to the incomplete specs in the OP, notice how what was once called the others row has become the added row, yet that is not exhibited in the expected output.

cheap::mysqls is a module in my private library that allows me to easily connect to my mysql and sqlite databases. you may use whatever method you like to connect to yours instead.

Replies are listed 'Best First'.
Re^5: perl sql to separate and sort the column separately?
by poj (Abbot) on Apr 12, 2017 at 13:50 UTC

    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
[reply]
[d/l]

      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