SQL Server中查询取款交易前最后一笔存款记录的方法

我有一份包含客户金融交易（存款/取款/奖金/手续费等）的数据集，需要找出每笔取款交易之前的最后一笔存款记录，使用的工具是SSMS。

我尝试用lag()函数实现，但没法只筛选前置交易为Deposit的记录。我在order by子句里用了不同的case when语句都没成功：

第一个尝试的代码：

lag(transaction_no, 1) over (partition by tt.vtigeraccountid order by (case when Transaction_type_name = 'Deposit' then confirmation_time else getdate()end)

第二个尝试的代码：

select lag(transaction_no, 1) over (partition by tt.vtigeraccountid order by (case when type_number = 1 then confirmation_time + type_number end)) prev_deposit_no
from (select *, case when Transaction_type_name = 'Deposit' then 1 else 2 end as type_number from Panda_Transaction_Type_Name) as tt

请问在SQL Server里还有其他实现方法吗？

数据样本：

account_no  confirmation_time   transaction_no  Transaction_type_name
ACC11050231 2022-07-05 11:52:07.000 MTT500596   Deposit
ACC11050231 2022-07-05 12:08:11.000 MTT500607   Bonus
ACC11050231 2022-07-08 12:09:35.000 MTT501949   Deposit
ACC11050231 2022-07-08 12:11:09.000 MTT501950   Deposit
ACC11050231 2022-07-08 12:12:34.000 MTT501951   Deposit
ACC11050231 2022-07-08 12:14:17.000 MTT501953   Deposit
ACC11050231 2022-07-08 13:13:42.000 MTT501985   Bonus
ACC11050231 2022-09-14 07:35:55.000 MTT523696   Withdrawal
ACC11050231 2022-09-14 07:35:06.000 MTT525085   BonusCancelled
ACC11050231 2022-09-14 07:37:01.000 MTT525091   Fee
ACC11050231 2022-09-14 07:40:50.000 MTT525099   Withdrawal
ACC11050231 2022-09-14 07:41:01.000 MTT525100   Fee
ACC11050231 2022-09-14 07:48:00.000 MTT525107   Withdrawal
ACC11050231 2022-09-14 07:48:18.000 MTT525109   Fee 

解决方案 #

方法1：窗口函数标记存款分组 #

先按客户分组，用累计计数标记存款分组，再提取每个分组内的最新存款记录关联到取款交易：

WITH transaction_groups AS (
    SELECT 
        *,
        SUM(CASE WHEN Transaction_type_name = 'Deposit' THEN 1 ELSE 0 END) 
            OVER (PARTITION BY account_no ORDER BY confirmation_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS deposit_group
    FROM Panda_Transaction_Type_Name
),
latest_deposits AS (
    SELECT 
        account_no,
        deposit_group,
        MAX(confirmation_time) AS latest_deposit_time,
        MAX(transaction_no) AS latest_deposit_no
    FROM transaction_groups
    WHERE Transaction_type_name = 'Deposit'
    GROUP BY account_no, deposit_group
)
SELECT 
    t.account_no,
    t.confirmation_time AS withdrawal_time,
    t.transaction_no AS withdrawal_no,
    ld.latest_deposit_no AS prev_deposit_no,
    ld.latest_deposit_time AS prev_deposit_time
FROM transaction_groups t
JOIN latest_deposits ld 
    ON t.account_no = ld.account_no 
    AND t.deposit_group = ld.deposit_group
WHERE t.Transaction_type_name = 'Withdrawal'
ORDER BY t.account_no, t.confirmation_time;

方法2：LAST_VALUE条件筛选 #

利用LAST_VALUE窗口函数，仅保留窗口内Deposit类型的交易记录，自动继承最近的存款交易号：

SELECT 
    account_no,
    confirmation_time AS withdrawal_time,
    transaction_no AS withdrawal_no,
    LAST_VALUE(CASE WHEN Transaction_type_name = 'Deposit' THEN transaction_no END) 
        OVER (PARTITION BY account_no ORDER BY confirmation_time 
              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prev_deposit_no
FROM Panda_Transaction_Type_Name
WHERE Transaction_type_name = 'Withdrawal'
ORDER BY account_no, confirmation_time;

方法3：关联子查询 #

针对每笔取款，直接查询该客户在取款时间前的最后一笔存款，适合小数据集场景：

SELECT 
    t.account_no,
    t.confirmation_time AS withdrawal_time,
    t.transaction_no AS withdrawal_no,
    (SELECT TOP 1 transaction_no 
     FROM Panda_Transaction_Type_Name t2 
     WHERE t2.account_no = t.account_no 
       AND t2.Transaction_type_name = 'Deposit' 
       AND t2.confirmation_time <= t.confirmation_time
     ORDER BY t2.confirmation_time DESC) AS prev_deposit_no
FROM Panda_Transaction_Type_Name t
WHERE t.Transaction_type_name = 'Withdrawal'
ORDER BY t.account_no, t.confirmation_time;

注：数据量大时建议给account_no、Transaction_type_name、confirmation_time建立联合索引优化性能。

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