Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW

Sum Fields in Array

by drodinthe559 (Monk)
on Feb 02, 2010 at 21:38 UTC ( #821040=perlquestion: print w/replies, xml ) Need Help??
drodinthe559 has asked for the wisdom of the Perl Monks concerning the following question:

Monks, is it possible to sum value using an array for certain clients? This would be similar to SQL in doing a group by.I want to sum the debit and credit fields. Below is what my array looks like.
push @fields, {type => $type, batch_number => $batch_num, effective_entry => $effent_date, entry_description => $entry_desc, date_created => $date_crt, load_number => $load_num, fooddebits => $debits, foodcredits => $credits};

Replies are listed 'Best First'.
Re: Sum Fields in Array
by Corion (Pope) on Feb 02, 2010 at 21:53 UTC

    The Perl equivalent to a GROUP BY clause in SQL is a hash. You use the parameter of the GROUP BY ... clause as the keys of the hash, and aggregate the value(s) of the rows in the values of the hash.

    A rough Perl implementation for a single aggregate (sum) could look like this:

    use Scalar::MoreUtils qw(zip); # I'm lazy sub sum_group_by { my ($key_columns, $aggregate_columns, $rows) = @_; my %result; for my $input_row (@$rows) { # Create the synthetic key, assuming that no value will contai +n a \0: my @key_values = @{ $input_row }{ @$key_columns }; my $key = join "\0", @key_values; # If this is a new key, create an new result row for it that c +ontains the key values: $result{ $key } ||= { zip @$key_columns, @key_values }; for my $aggregate (@$aggregate_columns) { $result{ $key }->{ $aggregate } += $input_row->{ $aggregat +e }; }; }; # Return the result as new rows, ordered asciibetically (because I +'m lazy) by their key: map { $result{ $_ } } sort keys %result; };

    If you can manage that all your operations return lists sorted by their keys, or can allow an intermediate step that (pre)sorts all inputs according to their keys, you can make the aggregation much simpler by aggregating into a single value and outputting a new row whenever the key (after which you sorted) changes. I have seen no paper or anaylis that measures which is better, pre-sorting the input and then pipelined aggregation or aggregation into a hash and then (potentially sorted) output.

    Update: Also consider just using SQL through either DBD::SQLite or DBD::AnyData as a way to manipulate data in a way that is very efficient to write for you, and likely better tested than a hand-rolled aggregation.

      Great! Thank you. You've given me something to work with.
Re: Sum Fields in Array
by toolic (Bishop) on Feb 02, 2010 at 21:49 UTC
    Loop through your array, then reference the debit and credit keys, then add them:
    use strict; use warnings; my @fields; push @fields, {fooddebits => 55, foodcredits => 22}; push @fields, {fooddebits => 12, foodcredits => 10}; for (@fields) { print $_->{fooddebits} + $_->{foodcredits}, "\n"; } __END__ 77 22
Re: Sum Fields in Array
by linuxer (Curate) on Feb 02, 2010 at 21:50 UTC
    I admit, I have to reactivate my SQL knowledge .... Maybe List::Util can help you:
    use List::Util qw( sum ); my $sum_debit = sum map { $_->{fooddebits} } @fields;

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://821040]
Approved by linuxer
and snow settles gently...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (6)
As of 2018-02-24 12:54 GMT
Find Nodes?
    Voting Booth?
    When it is dark outside I am happiest to see ...

    Results (310 votes). Check out past polls.