Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number

array of arrays calculations

by rocky13 (Acolyte)
on Jan 19, 2011 at 04:29 UTC ( #883054=perlquestion: print w/replies, xml ) Need Help??
rocky13 has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I have queried two similar result sets from a database as hash reference. For matching rows, I need to subtract set1 - set 2 for columns 3 and 4. Thanks! Here is the code with the subset of data I queried:

#!/usr/bin/perl use DBI; use DBD::mysql; my $platform = "mysql"; my $database = "db_name"; my $host = "host_name"; my $port = "port_num"; my $tablename = "hist"; my $user = "user_name"; my $pwd = "pass"; open(STDOUT, ">C:\\perlscripts\\new.txt") || die "Can't open the file" +; my $dsn = "dbi:mysql:$database:$host:$port"; my $dbh = DBI->connect($dsn,$user,$pwd) || die "Could not connect: $DB +I::errstr\n"; my $query = $dbh->selectall_arrayref("select cus,ta,sum(gd1) as gd1,su +m(gd2) as gd2 from hist group by cus,ta order by cus", {Slice => {} } +); my $query2 = $dbh->selectall_arrayref("select cus,ta,sum(gd1),sum(gd2) + from current group by cus,ta order by cus", {Slice => {} }); foreach my $ref (@$query) { push(@{$arry}, join(",",$ref->{cus},$ref->{ta},$ref->{gd1},$ref->{g +d2}))."\n"; } foreach my $ref (@$query2) { push(@{$arry2}, join(",",$ref->{cus},$ref->{ta},$ref->{gd1},$ref->{ +gd2}))."\n";

Here is the subset of data I get from this code. For columns 3 and 4, I am basically subtracting set1 - set2 (if match is found in set2, otherwise just print set1). I don't know what approach will allow me to get to the results. What would be the right approach?? Thanks.

set 1: set 2: Joe,A,85,90 Joe,A,80,85 Joe,B,80,99 Joe,B,70,90 Rob,A,50,70 Rob,A,40,70 Rob,B,60,65 Tim,A,70,89 Tim,A,87,89 Lou,A,30,51 Jon,B,82,92 Lou,A,30,51 Output: Joe,A,5,5 Joe,B,10,9 Rob,A,10,0 Rob,B,60,65 Tim,A,17,0 Jon,B,82,92 Lou,A,0,0

Replies are listed 'Best First'.
Re: array of arrays calculations
by thezip (Vicar) on Jan 19, 2011 at 04:41 UTC

    Why not just do the calculation right in the SQL?

    select h.cus, h.ta, h.gd1 - c.gd1 as diff1, h.gd2 - c.gd2 as diff2, from hist h, hist c where h.cus = c.cus and h.ta = c.ta order by h.cus, h.ta

    WARNING: not tested! YMMV!

    What can be asserted without proof can be dismissed without proof. - Christopher Hitchens
      I didn't try it, but don't you need a "LEFT JOIN" in there somewhere to get the contents of h.cus and h.ta in case c.cus and c.ta don't exist?


      A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

      SELECT h.e_id, h.cus, h.gd1-ifnull(t.gd1,0) as dif1, h.gd2-ifnull(t.gd2,0) as dif2 FROM tabla1 h left join tabla2 t on h.e_id = t.e_id and h.cus = t.cus;

      This works

      First, i updated the query in the question little bit to simplify the understanding of the question. As you can see, there are multiple rows for each cus, ta, gd1, gd2 and number of rows in one table is not the same as number of rows in other table.Thus, I think it would be best to group them by cus, ta while getting the sum of gd1 and gd2. I thought maybe there is a way with "union all". I did get the solution by using a hash for each and then doing the computation. However, if there is a way to do it just using sql query, I would love to know. Thanks!
Re: array of arrays calculations
by JavaFan (Canon) on Jan 19, 2011 at 10:12 UTC
    %data; $data{$_[0]}{$_[1]} = [@{$_}[2,3]] for @$query2; foreach my $row (@$query) { no warnings 'uninitialized'; $$row[2] -= $data{$$row[0]}{$$row[1]}[0]; $$row[3] -= $data{$$row[0]}{$$row[1]}[1]; local $" = ","; print "@$row\n"; }

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://883054]
Approved by ELISHEVA
[marto]: good morning all
[Corion]: Hi marto!
[Corion]: The fun show at $work continues, as The Big Project is now in its second week of frantic live-bugfixing and weekend releases where nobody knows what went live. Nothing has been tested anyway.
erix mutters cantankerously under his breath
Corion watches from the sidelines. Or rather, from behind, as my system only gets output from that process and my programs adhere strictly to the GIGO design principle.
[erix]: ah, that's nice to hear Corion :)
[Corion]: erix: Yeah, the sad thing is that all I can do is document things, so I can point fingers when the auditors come :-/
[Corion]: "I'm here to open tickets and point fingers. And I'm all out of tickets."

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (9)
As of 2017-03-28 08:51 GMT
Find Nodes?
    Voting Booth?
    Should Pluto Get Its Planethood Back?

    Results (328 votes). Check out past polls.