Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Re^2: Advances SQL calculations in CSV file

by DrAxeman (Beadle)
on Aug 31, 2005 at 17:53 UTC ( #488168=note: print w/ replies, xml ) Need Help??


in reply to Re: Advances SQL calculations in CSV file
in thread Advances SQL calculations in CSV file

Sorry. I didn't mean for that to sound like a job request. I was more concerned with properly describing my needs that I was at formating my request for suggestions.

I have other scripts that calculate averages for these columns, and I will include that code here. I would like to use DBD::CSV on this, but lack the SQL experience to properly script this with out looping over and over in the file.

# Connect to the database, (the directory containing our csv file( +s)) my $dbh = DBI->connect("DBI:CSV:f_dir=.;csv_eol=\n;"); # Associate our csv file with the table name 'results' $dbh->{'csv_tables'}->{'results'} = { 'file' => "$CWD/$name"}; my $sth = $dbh->prepare("SELECT * FROM results WHERE 1=0"); $sth->execute; my @origcols = @{$sth->{NAME}}; my @cols; # = @origcols; shift @origcols; # Eliminate First Column foreach ( @origcols ) { push ( @cols , $_ ) unless /Bandwidth.*|MSTCPLoop.*/ ; # Remove + unwanted columns }; my $avgSQL = 'SELECT ' . join(', ', map { "avg($_) \n" } @cols ) . ' FROM results'; my @avgs = $dbh->selectrow_array($avgSQL); my %avgsHash; @avgsHash{ @cols } = @avgs; # uses a hash slice to populate %avgs +Hash for ( @cols ) { (my $changed = $_) =~ s/_/","/; printf STATS ("\"%s\",%f\n", $changed, $avgsHash{$_}); }

Honestly, I have only made a couple of attempts at this. I'm really stumped on how to format the SQL query. I think that is someone could point me in the direction the SQL query should look like, I'd be able to create it.


Comment on Re^2: Advances SQL calculations in CSV file
Download Code
Re^3: Advances SQL calculations in CSV file
by Roger (Parson) on Aug 31, 2005 at 18:01 UTC
    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;
      I'm getting the following error, but everything looks initialized to me..

      Use of uninitialized value in hash element at ./top.pl line 19, <MYFIL +E> line 994. Use of uninitialized value in addition (+) at ./top.pl line 19, <MYFIL +E> line 994. Use of uninitialized value in hash element at ./top.pl line 20, <MYFIL +E> line 994.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://488168]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (7)
As of 2015-07-04 14:55 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (60 votes), past polls