SQL高效实现客户交易匹配首笔同额贷记交易后排除后续行

基础信息 #

• 现有交易数据集字段：Cust_No（客户编号）、Transaction_date（交易日期）、amount（交易金额）、credit_debit（借贷标识，C为贷记、D为借记）、running_total（账户累计余额）、row_num（组内排序序号）
• 数据已按客户分组，组内按交易日期倒序排列（最新交易在前，row_num从1开始递增）

原始数据集样例：

Cust_No Transaction_date    amount  credit_debit    running_total   row_num
1       5/27/2022           800     D               -200            1
1       5/26/2022           300     D               600             2
1       5/22/2022           800     C               900             3
1       5/20/2022           100     C               100             4
                    
9       5/16/2022           500     D               -300            1
9       5/14/2022           300     D               200             2
9       5/6/2022            200     C               500             3 
9       5/5/2022            500     D               300             4
9       5/2/2022            300     D               800             5
9       5/2/2022            500     C               1100            6
9       5/1/2022            500     C               600             7
9       5/1/2022            100     C               100             8

过滤规则 #

1. 取每个客户最新一笔交易（组内row_num=1）的金额作为匹配基准值
2. 从新到旧遍历客户交易，找到第一笔满足「金额=基准值 且 credit_debit='C'」的交易
3. 保留该匹配交易及之前（时间更新方向）的所有记录，排除该交易之后的所有同客户记录

样例逻辑说明：客户9最新交易金额为500（借记），遍历找到最近的500元贷记交易是row_num=6的记录，因此保留客户9的1-6号记录，排除7、8号记录；客户1最新交易金额为800（借记），最近的800元贷记交易是row_num=3的记录，因此保留1-3号记录，排除4号记录。

期望输出结果样例：

Cust_No Transaction_date    amount  credit_debit    running_total   row_num
1       5/27/2022           800     D               -200            1
1       5/26/2022           300     D               600             2
1       5/22/2022           800     C               900             3

                    
9       5/16/2022           500     D               -300            1
9       5/14/2022           300     D               200             2
9       5/6/2022            200     C               500             3 
9       5/5/2022            500     D               300             4
9       5/2/2022            300     D               800             5
9       5/2/2022            500     C               1100            6

现有实现进度 #

• 已完成running_total字段计算，逻辑为窗口函数累计求和：

sum (case when credit_debit ='C' then amount else -1*amount end) over (partition by cust_no order by transaction_date desc ) as running_total

• 曾尝试固定偏移量的lead函数匹配方案，逐次取1-5位偏移值比对金额，该方案存在明显缺陷：多层函数嵌套性能差，且无法适配匹配交易位置不固定的场景，示例代码如下：

case when lead(amount, 1) over(partition by cust_no order by transaction_date desc) = amount then amount else null end as lead1

通用高效实现方案 #

实现思路 #

• 第一步：给每个客户的所有交易记录打上最新交易金额的标签，作为匹配基准
• 第二步：按客户分组，找到离最新交易最近的符合匹配条件的贷记交易，取其row_num作为截止行号
• 第三步：过滤保留所有行号小于等于截止行号的记录即可

可直接运行的SQL代码 #

兼容所有支持标准窗口函数的SQL引擎（Spark SQL、Hive、MySQL 8.0+、PostgreSQL等）：

WITH base_with_latest_amt AS (
    SELECT
        Cust_No,
        Transaction_date,
        amount,
        credit_debit,
        running_total,
        row_num,
        -- 取客户最新交易金额作为匹配基准，row_num严格递增时可将排序字段改为ORDER BY row_num，避免同日期交易排序歧义
        FIRST_VALUE(amount) OVER (PARTITION BY Cust_No ORDER BY Transaction_date DESC) AS match_amt
    FROM your_transaction_table -- 替换为你的原始表/子查询
),
customer_cutoff AS (
    SELECT
        Cust_No,
        -- 取最近的匹配贷记交易的行号作为截止点
        MIN(CASE WHEN amount = match_amt AND credit_debit = 'C' THEN row_num END) AS cutoff_row
    FROM base_with_latest_amt
    GROUP BY Cust_No
)
SELECT
    b.Cust_No,
    b.Transaction_date,
    b.amount,
    b.credit_debit,
    b.running_total,
    b.row_num
FROM base_with_latest_amt b
INNER JOIN customer_cutoff c
    ON b.Cust_No = c.Cust_No
WHERE b.row_num <= c.cutoff_row
ORDER BY b.Cust_No, b.row_num;

方案优势 #

• 性能高：仅需两次全表扫描即可完成计算，无多层嵌套函数，数据量大时性能优势明显
• 通用性强：不限制匹配交易和最新交易之间的间隔行数，适配任意长度的客户交易序列
• 逻辑稳定：基于row_num做截止判断，不会受同日期多笔交易的排序干扰

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