How to calculate a total by comparing current and next row

Question

I have a table with some rows:

I have a few questions to accomplish the task altogether. Is it possible to calculate total of transmitted and received based on checked_date and ip and connect_time using mysql query? If yes, can I use logical operators to calculate total of transmitted and received? To get correct total transmitted and received I need to use a formula like:

if connect_time[i] > connect_time[j] 
then total += transmitted[i]+transmitted[j] 
else total += ABS(transmitted[i]-transmitted[j]) 
end

How to get it done with this formula, does anyone have an idea? P.S. i = current row and j = next row.

Answer 1

Using user variables, you calculate running total for each line like a spreadsheet

PROPOSED QUERY

SET @total = 0;
SET @curr_trx = 0;
SET @curr_con = 0;
SET @lineitem = 0;
SELECT GrandTotal FROM
(
    SELECT
        (@lineitem:=@lineitem+1) line,
        (@prev_con:=@curr_con),
        (@curr_con:=connect_time),
        (@prev_trx:=@curr_trx),
        (@curr_trx:=transmitted),
        (@newvalue:=IF(@prev_con>@curr_con,@prev_trx+@curr_trx,ABS(@prev_trx-@curr_trx))) newval,
        (@total:=@total+@newvalue) GrandTotal
    FROM mytable
) A WHERE line = @lineitem;

YOUR SAMPLE DATA

mysql> DROP DATABASE IF EXISTS timyash;
Query OK, 1 row affected (0.22 sec)

mysql> CREATE DATABASE timyash;
Query OK, 1 row affected (0.00 sec)

mysql> USE timyash
Database changed
mysql> CREATE TABLE mytable
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     transmitted INT NOT NULL,
    ->     connect_time INT NOT NULL,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.35 sec)

mysql> INSERT INTO mytable (transmitted,connect_time) VALUES
    -> (2650131,117987),(6465178,78073),(25905117,159268),
    -> (59178089,410282),(73502942,596408),(75695427,683045),
    -> (77576167,740379);
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytable;
+----+-------------+--------------+
| id | transmitted | connect_time |
+----+-------------+--------------+
|  1 |     2650131 |       117987 |
|  2 |     6465178 |        78073 |
|  3 |    25905117 |       159268 |
|  4 |    59178089 |       410282 |
|  5 |    73502942 |       596408 |
|  6 |    75695427 |       683045 |
|  7 |    77576167 |       740379 |
+----+-------------+--------------+
7 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SET @total = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @curr_trx = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @curr_con = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @lineitem = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT GrandTotal FROM
    -> (
    ->     SELECT
    ->         (@lineitem:=@lineitem+1) line,
    ->         (@prev_con:=@curr_con),
    ->         (@curr_con:=connect_time),
    ->         (@prev_trx:=@curr_trx),
    ->         (@curr_trx:=transmitted),
    ->         (@newvalue:=IF(@prev_con>@curr_con,@prev_trx+@curr_trx,ABS(@prev_trx-@curr_trx))) newval,
    ->         (@total:=@total+@newvalue) GrandTotal
    ->     FROM mytable
    -> ) A WHERE line = @lineitem;
+------------+
| GrandTotal |
+------------+
|   82876429 |
+------------+
1 row in set (0.08 sec)

mysql>

PROPOSED QUERY EXPLAINED

Look at the subquery

SELECT
    (@lineitem:=@lineitem+1) line,
    (@prev_con:=@curr_con),
    (@curr_con:=connect_time),
    (@prev_trx:=@curr_trx),
    (@curr_trx:=transmitted),
    (@newvalue:=IF(@prev_con>@curr_con,@prev_trx+@curr_trx,ABS(@prev_trx-@curr_trx))),
    (@total:=@total+@newvalue) GrandTotal
FROM mytable

If you run this with the variables, you see everything being added up for you line by line

mysql> SET @total = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @curr_trx = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @curr_con = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @lineitem = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->     (@lineitem:=@lineitem+1) line,
    ->     (@prev_con:=@curr_con),
    ->     (@curr_con:=connect_time),
    ->     (@prev_trx:=@curr_trx),
    ->     (@curr_trx:=transmitted),
    ->     (@newvalue:=IF(@prev_con>@curr_con,@prev_trx+@curr_trx,ABS(@prev_trx-@curr_trx))) newval,
    ->     (@total:=@total+@newvalue) GrandTotal
    -> FROM mytable;
+------+------------------------+---------------------------+------------------------+--------------------------+----------+------------+
| line | (@prev_con:=@curr_con) | (@curr_con:=connect_time) | (@prev_trx:=@curr_trx) | (@curr_trx:=transmitted) | newval   | GrandTotal |
+------+------------------------+---------------------------+------------------------+--------------------------+----------+------------+
|    1 |                      0 |                    117987 |                      0 |                  2650131 |  2650131 |    2650131 |
|    2 |                 117987 |                     78073 |                2650131 |                  6465178 |  9115309 |   11765440 |
|    3 |                  78073 |                    159268 |                6465178 |                 25905117 | 19439939 |   31205379 |
|    4 |                 159268 |                    410282 |               25905117 |                 59178089 | 33272972 |   64478351 |
|    5 |                 410282 |                    596408 |               59178089 |                 73502942 | 14324853 |   78803204 |
|    6 |                 596408 |                    683045 |               73502942 |                 75695427 |  2192485 |   80995689 |
|    7 |                 683045 |                    740379 |               75695427 |                 77576167 |  1880740 |   82876429 |
+------+------------------------+---------------------------+------------------------+--------------------------+----------+------------+
7 rows in set (0.00 sec)

mysql>

The proposed query has the subquery return a lineitem number. I get the last one to show the final total.

Remember the formula from your question ?

if connect_time[i] > connect_time[j] 
then total += transmitted[i]+transmitted[j] 
else total += ABS(transmitted[i]-transmitted[j]) 
end

The newval column computes the formula

Answer 2

Still not sure I understand what ordering operator to use, but I assume it is checked_date. I also assume that (ip, mac, checked_date) is unique. This would be almost trivial in a DBMS that support window functions, unfortenately MySQL does not. Here's one suggestion (untested, it really helps if you provide sample data instead of an image) to get the data you need for your calculations.

select x.checked_date, x.ip, x.mac, x.transmitted, x.received, x.connect_time
     , y.checked_date, y.transmitted, y.received, y.connect_time
from T as x
join T as y
    on y.ip = x.ip
    and y.mac = x.mac
    and y.checked_date = ( select min(z.checked_date)
                           from T z 
                           where z.ip = x.ip
                             and z.mac = x.mac
                             and z.checked_date > x.checked_date )

The result for the row can be calculated as:

case when x.connect_time > y.connect_time 
     then x.transmitted + y.transmitted
     else abs(x.transmitted - y.transmitted)
end

To get the running total you need to sum this expression for all rows less than or equal to the current row. Probably easiest to create a view as:

create view V as 
select x.checked_date, x.ip, x.mac
     , case when x.connect_time > y.connect_time 
            then x.transmitted + y.transmitted
            else abs(x.transmitted - y.transmitted) 
       end as total
from T as x
join T as y
    on y.ip = x.ip
    and y.mac = x.mac
    and y.checked_date = ( select min(z.checked_date)
                           from T z 
                           where z.ip = x.ip
                             and z.mac = x.mac
                             and z.checked_date > x.checked_date )

and then sum up the total as:

select v1.checked_date, v1.ip, v1.mac, sum(v2.total)
from v as v1
join v as v2
    on v1.ip = v2.ip
   and v1.mac = v2.mac
   and v1.checked_date >= v2.checked_date
group by v1.checked_date, v1.ip, v1.mac