如何在SQL Server中筛选后续无退货记录的交易数据

需求说明 #

需要从交易数据表中筛选出**后续紧跟的记录不是退货（Return）**的交易，需过滤掉那些后面紧邻退货的记录（示例中的第2、4条记录）。

数据示例 #

以下是测试数据的插入语句，其中加粗的记录为需要过滤的条目：

INSERT INTO Table1
    (acct_name, id_no, cube_id, accnt_number, Tran_Dt, Post_Dt, Description, Trcd, Amt)
VALUES
    ('TEST1', 2607269, 299933445, 1999957778, '2023-04-05 00:00:00', '2023-04-05 00:00:00', 'ITEM RETURN', 'R9P', '700-01-01 00:00:00'),
    **('TEST1', 1965041, 299933445, 1999957778, '2023-04-05 00:00:00', '2023-04-05 00:00:00', 'CASHBACK XFR', 'W8', '700-01-01 00:00:00'),**
    ('TEST2', 2607269, 288833446, 2999957776, '2023-04-06 00:00:00', '2023-04-06 00:00:00', 'ITEM RETURN', 'R9P', '900-01-01 00:00:00'),
    **('TEST2', 2607269, 288833446, 2999957776, '2023-04-06 00:00:00', '2023-04-06 00:00:00', 'CASHBACK XFR', 'W8', '900-01-01 00:00:00'),**
    ('TEST3', 3607867, 199929599, 8779597774, '2023-04-06 00:00:00', '2023-04-06 00:00:00', 'WEBXFR  PYMT', 'W8', '400-01-01 00:00:00')
;

期望输出 #

('TEST1', 2607269, 299933445, 1999957778, '2023-04-05 00:00:00', '2023-04-05 00:00:00', 'ITEM RETURN', 'R9P', '700-01-01 00:00:00'),     
('TEST2', 2607269, 288833446, 2999957776, '2023-04-06 00:00:00', '2023-04-06 00:00:00', 'ITEM RETURN', 'R9P', '900-01-01 00:00:00'),
('TEST3', 3607867, 199929599, 8779597774, '2023-04-06 00:00:00', '2023-04-06 00:00:00', 'WEBXFR  PYMT', 'W8', '400-01-01 00:00:00')
;

解决方案 #

使用SQL Server的LEAD()窗口函数，获取每条记录的下一条交易的描述信息，然后筛选出下一条记录不是退货，或者是最后一条无后续记录的交易。排序逻辑按账户+交易日期组合，确保后续记录是同一账户的紧邻交易。

查询代码 #

WITH ranked_transactions AS (
    SELECT 
        *,
        -- 获取下一条记录的描述，按账户+交易日期排序
        LEAD(Description) OVER (
            PARTITION BY acct_name, accnt_number 
            ORDER BY Tran_Dt, Post_Dt
        ) AS next_tran_description
    FROM Table1
)
SELECT 
    acct_name, id_no, cube_id, accnt_number, Tran_Dt, Post_Dt, Description, Trcd, Amt
FROM ranked_transactions
-- 保留无后续记录的交易，或后续记录不是退货的交易
WHERE next_tran_description IS NULL 
   OR next_tran_description NOT LIKE '%RETURN%';

逻辑说明 #

• PARTITION BY acct_name, accnt_number：按账户分组，仅对比同一账户内的交易顺序
• ORDER BY Tran_Dt, Post_Dt：按交易日期、记账日期排序，确定交易的先后顺序
• LEAD(Description)：获取当前记录的下一条交易描述
• 筛选条件覆盖两种合法场景：无后续记录的交易，以及后续记录非退货的交易

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