如何基于记录查询账户余额变动数据并计算余额差值

需求说明 #

需要从账户余额记录中筛选出余额发生变动的行（含账户首次出现的记录），明确每个账户的余额变动时机；同时要能计算出每次变动时，最新余额与上一次变动余额的差值。

示例数据 #

期望查询结果 #

用户尝试的SQL #

DECLARE @x TABLE(acct INT,  balance money,[time] DATETIME)

INSERT @x VALUES
(123450,'1000.00','2008-01-30 00:00:00'),
(123456,'1000.00','2008-02-29 00:00:00'),
(123456,'1000.00','2008-03-31 00:00:00'),
(123456,'1000.00','2008-04-30 00:00:00'),
(123456,'1000.00','2008-05-31 00:00:00'),
(123456,'2000.00','2008-06-30 00:00:00'),
(654321,'1000.00','2008-02-29 00:00:00'),
(654321,'2000.00','2008-03-31 00:00:00');
select * from @x
; with temp as
(
SELECT 
    acct, balance, [time],  lag(balance) over (order by [time] ) as lastValue
FROM    @x
) 
SELECT 
    acct,[time] , balance
FROM 
    temp 
WHERE acct > lastValue
order by acct, time 

问题分析 #

原SQL存在两个核心错误：

1. LAG()函数未按acct分区，导致跨账户取上一条记录的余额，逻辑完全错误；
2. WHERE acct > lastValue条件毫无意义，应该判断当前余额与上一次余额是否不同，同时保留账户的首次记录（此时LAG()返回NULL）。

正确解决方案 #

1. 筛选余额变动记录（含首次记录） #

DECLARE @x TABLE(acct INT,  balance money,[time] DATETIME)

INSERT @x VALUES
(123450,'1000.00','2008-01-30 00:00:00'),
(123456,'1000.00','2008-02-29 00:00:00'),
(123456,'1000.00','2008-03-31 00:00:00'),
(123456,'1000.00','2008-04-30 00:00:00'),
(123456,'1000.00','2008-05-31 00:00:00'),
(123456,'2000.00','2008-06-30 00:00:00'),
(654321,'1000.00','2008-02-29 00:00:00'),
(654321,'2000.00','2008-03-31 00:00:00');

WITH temp AS (
    SELECT 
        acct, 
        balance, 
        [time],
        -- 按账户分区，按时间排序取上一次的余额
        LAG(balance) OVER (PARTITION BY acct ORDER BY [time]) AS last_balance
    FROM @x
)
SELECT 
    acct,
    balance,
    [time]
FROM temp
-- 筛选条件：首次记录（last_balance为NULL） 或 当前余额与上一次不同
WHERE last_balance IS NULL OR balance <> last_balance
ORDER BY acct, [time];

2. 计算每次变动的余额差值 #

基于上述变动记录，再用一次LAG()函数计算差值：

DECLARE @x TABLE(acct INT,  balance money,[time] DATETIME)

INSERT @x VALUES
(123450,'1000.00','2008-01-30 00:00:00'),
(123456,'1000.00','2008-02-29 00:00:00'),
(123456,'1000.00','2008-03-31 00:00:00'),
(123456,'1000.00','2008-04-30 00:00:00'),
(123456,'1000.00','2008-05-31 00:00:00'),
(123456,'2000.00','2008-06-30 00:00:00'),
(654321,'1000.00','2008-02-29 00:00:00'),
(654321,'2000.00','2008-03-31 00:00:00');

WITH change_records AS (
    SELECT 
        acct, 
        balance, 
        [time],
        LAG(balance) OVER (PARTITION BY acct ORDER BY [time]) AS last_balance
    FROM @x
    WHERE LAG(balance) OVER (PARTITION BY acct ORDER BY [time]) IS NULL 
        OR balance <> LAG(balance) OVER (PARTITION BY acct ORDER BY [time])
)
SELECT 
    acct,
    [time],
    balance,
    last_balance,
    -- 计算差值，首次记录差值为NULL或0，按需调整
    CASE WHEN last_balance IS NULL THEN NULL ELSE balance - last_balance END AS balance_diff
FROM change_records
ORDER BY acct, [time];

内容的提问来源于stack exchange，提问作者user2654626