我有一个交易表,记录客户余额中增加或减少的每个金额,并带有新的余额:
+----+------------+------------+--------+---------+
| 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
列,它将存储余额符号上次更改的时间戳。从正、负或零转换算作平衡变化。
更新后,根据上述数据,客户表应包含:
+----+---------+------------------+
| id | balance | balanceSignSince |
+----+---------+------------------+
| 1 | -20 | 1000000002 |
| 2 | 0 | 1000000006 |
| 3 | 15 | 1000000008 |
| 4 | 5 | 1000000010 |
+----+---------+------------------+
如何编写一个 SQL 查询,根据交易表更新每个客户,其中包含上次余额符号更改的时间?
我怀疑如果没有一个相当复杂的存储过程,我就无法做到这一点,但我很好奇,看看是否有任何聪明的想法出现。
这使用模拟的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 |
+------------+-------------+
更新了答案,但限制这需要处理现有数据
以下查询应该适用于大多数情况,客户只有单个交易或没有符号更改仍然存在问题。由于这是一次性更新,我将运行下面的查询,然后对所有未设置时间戳的用户进行简单更新,对他们来说,这将是第一个事务的时间戳:
# Find the smallest timestamp, e.g. the
# transaction which changed the signum.
SELECT
p.customerId as customerId,
MIN(t.timestamp) as balanceSignSince
FROM
transaction as t,
(
# find the latest timestamp having
# a different sign for each user.
# Here is the issue with users having
# only a single transaction or no sign
# changes.
SELECT
u.customerId as customerId,
MAX(t.timestamp) as balanceSignSince
FROM
transaction as t,
customer as c,
(
# find the timestamp of the very last
# transaction for every user.
SELECT
t.customerId as customerId,
MAX(t.timestamp) as lastTransaction
FROM
transaction as t
GROUP BY
t.customerId
) as u
WHERE
u.customerId = c.id
AND u.customerId = t.customerId
AND SIGN(c.balance) <> SIGN(t.balance)
GROUP BY
u.customerId
) as p
WHERE
p.customerId = t.customerId
AND p.balanceSignSince < t.timestamp
GROUP BY
p.customerId;
小提琴:http://sqlfiddle.com/#!9/bd0760/13
原始答案
这应该可以获取符号更改的时间戳:
SELECT
c.id as id,
MAX(t.timestamp) as balanceSignSince
FROM
transaction as t,
customer as c
WHERE
t.customerId = c.id
AND SIGN(t.balance) <> SIGN(c.balance)
这需要在客户表使用新余额更新之前执行。如果你有一个关于 transation:insert 的触发器,你可能应该将上述内容放入更新客户表的查询中。