calculation using multiple rows of a query

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
[download]

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";

}
[download]

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
[download]

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
[download]

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.
[download]

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

Replies are listed 'Best First'.
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"; }` [download] - Miller	[reply] [d/l]
Re: calculation using multiple rows of a query by jfroebe (Parson) 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	[reply]
Re: calculation using multiple rows of a query by roboticus (Chancellor) 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.	[reply]