Beefy Boxes and Bandwidth Generously Provided by pair Networks Cowboy Neal with Hat
Keep It Simple, Stupid
 
PerlMonks  

calculation using multiple rows of a query

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

I have data in a table that looks like this:

date code id cur hrs tips 2011-01-21 12:00:00 1 BP1616 USD 10 5 2011-01-21 12:00:00 1 BP1616 USD 8 15 2011-01-22 12:00:00 1 BP1616 USD 3 0 2011-01-23 12:00:00 1 BP1616 USD 0 0 2011-01-24 12:00:00 1 BP1616 USD 8 15 2011-01-25 12:00:00 1 BP1616 USD 8 15 2011-01-26 12:00:00 1 BP1616 USD 8 15 2011-01-27 12:00:00 1 BP1616 USD 4 15 2011-01-28 12:00:00 1 BP1616 USD 8 15 2011-01-29 12:00:00 1 BP1616 USD 3 25 2011-01-21 12:00:00 1 KP1616 USD 8 60 2011-01-22 12:00:00 1 KP1616 USD 0 25 2011-01-23 12:00:00 1 KP1616 USD 0 0 2011-01-24 12:00:00 1 KP1616 USD 8 60 2011-01-25 12:00:00 1 KP1616 USD 8 60 2011-01-26 12:00:00 1 KP1616 USD 8 60 2011-01-27 12:00:00 1 KP1616 USD 0 0 2011-01-28 12:00:00 1 KP1616 USD 8 4 2011-01-29 12:00:00 1 KP1616 USD 0 0 2011-01-29 12:00:00 1 KP1616 USD 0 20 2011-01-21 12:00:00 1 PP3232 USD 100 5 2011-01-21 12:00:00 1 PP3232 USD 4 15 2011-01-22 12:00:00 1 PP3232 USD 4 10 2011-01-23 12:00:00 1 PP3232 USD 0 0 2011-01-24 12:00:00 1 PP3232 USD 4 15 2011-01-25 12:00:00 1 PP3232 USD 4 15 2011-01-26 12:00:00 1 PP3232 USD 20 150 2011-01-27 12:00:00 1 PP3232 USD 20 150 2011-01-28 12:00:00 1 PP3232 USD 200 1500 2011-01-29 12:00:00 1 PP3232 USD 0 5 2011-01-30 12:00:00 1 PP3232 USD 10 5 2011-01-21 12:00:00 1 PT3232 USD 10 25 2011-01-22 12:00:00 1 PT3232 USD 0 0 2011-01-23 12:00:00 1 PT3232 USD 12 100 2011-01-24 12:00:00 1 PT3232 USD 12 100 2011-01-25 12:00:00 1 PT3232 USD 12 100 2011-01-26 12:00:00 1 PT3232 USD 12 100 2011-01-27 12:00:00 1 PT3232 USD 12 100 2011-01-28 12:00:00 1 PT3232 USD 0 50 2011-01-29 12:00:00 1 PT3232 USD 10 50

There are more columns but not involved. I used the following code to query the data by using the (date, code, id, cur) as a key.

#!/usr/bin/perl use DBI; use DBD::mysql; print "Connecting...\n"; my $platform; my $database; my $host; my $port; my $tablename = "points"; my $user; my $pwd; open(STDOUT, ">C:\\perlscripts\\pts.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 dat, code, id, cur, hrs, +tips from points order by id, dat", { Slice => {} }); foreach my $row(@$query) { print "$row->{dat}\t$row->{code}\t$row->{id}\t$row->{cur}\t$row->{hrs} +\t$row->{tips}\n"; }

The data I get here looks like the following:

data code id cur hrs tips 2011-01-21 12:00:00 1 BP1616 USD 18 20 2011-01-22 12:00:00 1 BP1616 USD 3 0 2011-01-23 12:00:00 1 BP1616 USD 0 0 2011-01-24 12:00:00 1 BP1616 USD 8 15 2011-01-25 12:00:00 1 BP1616 USD 8 15 2011-01-26 12:00:00 1 BP1616 USD 8 15 2011-01-27 12:00:00 1 BP1616 USD 4 15 2011-01-28 12:00:00 1 BP1616 USD 8 15 2011-01-29 12:00:00 1 BP1616 USD 3 25 2011-01-21 12:00:00 1 KP1616 USD 8 60 2011-01-22 12:00:00 1 KP1616 USD 0 25 2011-01-23 12:00:00 1 KP1616 USD 0 0 2011-01-24 12:00:00 1 KP1616 USD 8 60 2011-01-25 12:00:00 1 KP1616 USD 8 60 2011-01-26 12:00:00 1 KP1616 USD 8 60 2011-01-27 12:00:00 1 KP1616 USD 0 0 2011-01-28 12:00:00 1 KP1616 USD 8 4 2011-01-29 12:00:00 1 KP1616 USD 0 20 2011-01-21 12:00:00 1 PP3232 USD 104 20 2011-01-22 12:00:00 1 PP3232 USD 4 10 2011-01-23 12:00:00 1 PP3232 USD 0 0 2011-01-24 12:00:00 1 PP3232 USD 4 15 2011-01-25 12:00:00 1 PP3232 USD 4 15 2011-01-26 12:00:00 1 PP3232 USD 20 150 2011-01-27 12:00:00 1 PP3232 USD 20 150 2011-01-28 12:00:00 1 PP3232 USD 200 1500 2011-01-29 12:00:00 1 PP3232 USD 0 5 2011-01-30 12:00:00 1 PP3232 USD 10 5 2011-01-21 12:00:00 1 PT3232 USD 10 25 2011-01-22 12:00:00 1 PT3232 USD 0 0 2011-01-23 12:00:00 1 PT3232 USD 12 100 2011-01-24 12:00:00 1 PT3232 USD 12 100 2011-01-25 12:00:00 1 PT3232 USD 12 100 2011-01-26 12:00:00 1 PT3232 USD 12 100 2011-01-27 12:00:00 1 PT3232 USD 12 100 2011-01-28 12:00:00 1 PT3232 USD 0 50 2011-01-29 12:00:00 1 PT3232 USD 10 50

After calculations, I wish to achieve the following results which will be inserted into an empty table that is already in place with the appropriate columns.

date code id cur beg_hr end_hr beg_tip end +_tip 2011-01-21 12:00:00 1 BP1616 USD 0 18 0 20 2011-01-22 12:00:00 1 BP1616 USD 18 21 20 20 2011-01-23 12:00:00 1 BP1616 USD 21 21 20 20 2011-01-24 12:00:00 1 BP1616 USD 21 29 20 35 2011-01-25 12:00:00 1 BP1616 USD 29 37 35 50 2011-01-26 12:00:00 1 BP1616 USD 37 45 50 65 2011-01-21 12:00:00 1 KP1616 USD 0 8 0 60 2011-01-22 12:00:00 1 KP1616 USD 8 8 60 85 2011-01-23 12:00:00 1 KP1616 USD 8 8 85 85 2011-01-24 12:00:00 1 KP1616 USD 8 16 85 145 2011-01-25 12:00:00 1 KP1616 USD 16 24 145 205 2011-01-26 12:00:00 1 KP1616 USD 24 32 205 265 2011-01-21 12:00:00 1 PP3232 USD 0 104 0 20 2011-01-22 12:00:00 1 PP3232 USD 104 108 20 30 2011-01-23 12:00:00 1 PP3232 USD 108 108 30 30 2011-01-24 12:00:00 1 PP3232 USD 108 112 30 45 2011-01-25 12:00:00 1 PP3232 USD 112 116 45 60

Here is the concept. Using the QUERY results:

For the first day, beg_hr = beg_tip = 0; end_hr = beg_hr + (hrs for first day from data); end_tip = beg_tip + (tips for first day from data). For every day afterwards: beg_hr(current) = end_hr(previous day); beg_tip(current) = end_tip(previous day); end_hr(current) = beg_hr(current) + hrs(current) end_tip(current) = beg_tip(current) + tips(current) This pattern should continue until last day is reached.

Here is my question/problem? How do I use the previous row's data with the current row's data? Or, what is the correct methodology or approach for this problem? Can you do all of this using an sql query? Thanks in advance!

Comment on calculation using multiple rows of a query
Select or Download Code
Re: calculation using multiple rows of a query
by wind (Priest) on Jan 30, 2011 at 20:19 UTC
    Looks like you've already done all the work with your query, so just do the math.
    my $last_id = ''; my $hrs_total; my $tips_total; foreach my $row (@$query) { if ($last_id ne $row->{id}) { $last_id = $row->{id}; $hrs_total = 0; $tips_total = 0; } $row->{beg_hr} = $hrs_total; $row->{end_hr} = $hrs_total += $row->{hrs}; $row->{beg_tip} = $tips_total; $row->{end_tip} = $tips_total += $row->{tips}; print join("\t", @{$row}{qw(dat code id cur beg_hr end_hr beg_tip +end_tip)}) . "\n"; }
    - Miller
Re: calculation using multiple rows of a query
by jfroebe (Vicar) on Jan 30, 2011 at 22:31 UTC

    Minor redundancy. You don't need use DBD::mysql; as DBI will load the driver when you specify it in connect(). It doesn't hurt anything but it's extra typing.

    Jason L. Froebe

    Blog, Tech Blog

Re: calculation using multiple rows of a query
by roboticus (Canon) on Jan 31, 2011 at 11:16 UTC

    rocky13:

    For small tables, doing the work in perl can be fine. For large tables, though, dumping the entire table through the network to compute results can be a performance problem. So I usually try to do computations involving entire tables inside the database itself. So a quick google for "SQL compute running totals" comes up with: http://www.sqlteam.com/article/calculating-running-totals showing three different ways to do the job.

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://885148]
Approved by Corion
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (11)
As of 2014-04-18 12:04 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (466 votes), past polls