Benjamin Benjamin - 4 years ago 73
MySQL Question

Get last balance sign change in (My)SQL

I have a Transaction table that records every amount added to or subtracted from the balance of a Customer, with the new balance:

+----+------------+------------+--------+---------+
| id | customerId | timestamp | amount | balance |
+----+------------+------------+--------+---------+
| 1 | 1 | 1000000001 | 10 | 10 |
| 2 | 1 | 1000000002 | -20 | -10 |
| 3 | 1 | 1000000003 | -10 | -20 |
| 4 | 2 | 1000000004 | -5 | -5 |
| 5 | 2 | 1000000005 | -5 | -10 |
| 6 | 2 | 1000000006 | 10 | 0 |
| 7 | 3 | 1000000007 | -5 | -5 |
| 8 | 3 | 1000000008 | 10 | 5 |
| 9 | 3 | 1000000009 | 10 | 15 |
| 10 | 4 | 1000000010 | 5 | 5 |
+----+------------+------------+--------+---------+


The Customer table stores the current balance, and looks like:

+----+---------+
| id | balance |
+----+---------+
| 1 | -20 |
| 2 | 0 |
| 3 | 15 |
| 4 | 5 |
+----+---------+


I would like to add a
balanceSignSince
column, that would store the timestamp at which the balance sign last changed. Transitioning to and from positive, negative, or zero counts as a balance change.

After the update, based on the above data, the Customer table should contain:

+----+---------+------------------+
| id | balance | balanceSignSince |
+----+---------+------------------+
| 1 | -20 | 1000000002 |
| 2 | 0 | 1000000006 |
| 3 | 15 | 1000000008 |
| 4 | 5 | 1000000010 |
+----+---------+------------------+


How can I write a SQL query that updates every Customer with the last time the balance sign changed, based on the Transaction table?

I suspect I can't do this without a quite complex stored procedure, but am curious to see if any clever ideas come up.

Answer Source

This uses a simulated rank() function.

select customerId, min(tstamp) from
(
select tstamp,
       if (@cust = customerId and sign(@bal) = sign(balance), @rn := @rn,
           if (@cust = customerId and sign(@bal) <> sign(balance), @rn := @rn + 1, @rn := 0)) as rn,
       @cust := customerId as customerId, @bal := balance as balance
from
      (select @rn := 0) x,
      (select id, @cust := customerId as customerId, tstamp, amount, @bal := balance as balance 
       from trans order by customerId, tstamp desc) y
) z
where rn = 0
group by customerId;

Check it: http://rextester.com/XJVKK61181

This script returns a table like this:

+------------+----+------------+---------+
| tstamp     | rn | customerId | balance |
+------------+----+------------+---------+
| 1000000003 | 0  | 1          | -20     |
| 1000000002 | 0  | 1          | -10     |
| 1000000001 | 1  | 1          | 10      |
| 1000000006 | 0  | 2          | 0       |
| 1000000005 | 2  | 2          | -10     |
| 1000000004 | 2  | 2          | -5      |
| 1000000009 | 0  | 3          | 15      |
| 1000000008 | 2  | 3          | 5       |
| 1000000007 | 3  | 3          | -5      |
| 1000000010 | 0  | 4          | 5       |
+------------+----+------------+---------+

Then selecting min(timestamp) of files where rn = 0:

+------------+-------------+
| customerId | min(tstamp) |
+------------+-------------+
| 1          | 1000000002  |
+------------+-------------+
| 2          | 1000000006  |
+------------+-------------+
| 3          | 1000000009  |
+------------+-------------+
| 4          | 1000000010  |
+------------+-------------+
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download