mysql - 获取最后的余额符号变化(Mysql

Question

我有一个交易表，记录了客户余额中添加或减去的每笔金额，以及新余额:

+----+------------+------------+--------+---------+
| 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 |
+----+------------+------------+--------+---------+

客户表存储当前余额，如下所示:

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

我想添加一个 balanceSignSince 列，该列将存储余额符号上次更改的时间戳。在正、负或零之间转换都算作余额变化。

更新后，根据上述数据，Customer 表应包含:

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

如何编写一个 SQL 查询，根据事务表更新每个客户上次余额符号更改的时间？

我怀疑如果没有相当复杂的存储过程我就无法做到这一点，但我很好奇是否会出现任何聪明的想法。

Answer 1

这使用模拟的rank()函数。

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;

检查一下:http://rextester.com/XJVKK61181

此脚本返回一个如下表:

+------------+----+------------+---------+
| 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       |
+------------+----+------------+---------+

然后选择 rn = 0 的文件的最小值(时间戳):

+------------+-------------+
| customerId | min(tstamp) |
+------------+-------------+
| 1          | 1000000002  |
+------------+-------------+
| 2          | 1000000006  |
+------------+-------------+
| 3          | 1000000009  |
+------------+-------------+
| 4          | 1000000010  |
+------------+-------------+