Re^3: Advances SQL calculations in CSV file

[Roger]

SQL is the unnecessary complexity you don't want in such a simple task. Besides DBD::CSV implements a very limited subset of SQL.

What you should do is just to have a single pass over your file reading a line at the time, build your hash table of statistics along the way while splitting the records, and print out the stats at the end.

my %stats;

open MYFILE, "<data.txt" or die "Can not open file: $!";
chomp(my $heading = <MYFILE>);
my @col = split /,/, $heading;
while (my $line = <MYFILE>) {
  # build a hash for the input record
  chomp($line);
  my @rec = split /,/, $line;
  my %rec;
  @rec{@col} = @rec;
  
  # collect stats
  $stats{$rec{rec_id}}{BAL} += $rec{bal};
  $stats{$rec{rec_id}}{NUM} ++;
}

# print the stats here
for my $id (keys %rec) {
  if ($rec{$id}{NUM}) {
    print "$id => ", $rec{$id}{BAL}/$rec{$id}{NUM}, "\n" 
  }
}

close MYFILE;