Teradata 中高效识别 recurring payment patterns

2026-10-24支付阿杰

结论

不建议继续增加 self join 的次数。更合适的处理方式是：

用一次自连接生成“可能属于下一周期”的交易关系；
用递归 CTE 沿这些关系继续向后追踪；
根据连续出现次数判断 weekly、fortnightly 或 monthly recurring pattern。

这样就不必写死连接层数，中间结果通常也比四五层自连接更容易控制。没有必要先把 40 million 行数据迁移到 R。应先在 Teradata 内缩小候选集，同时检查数据分布、统计信息和索引设计是否合理。

以下示例假设每笔交易都有唯一的 transaction_id。如果原表没有唯一交易标识，建议先生成一个稳定标识，否则无法可靠地区分同一天、同金额的多笔交易。

为什么多层自连接效率较差

每增加一次自连接，候选组合都可能成倍增长。假设某个账户在一个日期窗口内有 5 笔候选交易，连续匹配 4 个周期时，理论组合数可能接近 5^4。

原查询还有几个容易忽略的问题：

a.channel = b.channel = c.channel 表示整个 recurring sequence 必须使用相同的 channel。如果付款渠道允许变化，就不应把 channel 放入连接条件。
相邻金额分别允许 ±10%，可能导致“金额漂移”。例如 100 → 109 → 118 每一步都满足条件，但首尾已经相差 18%。
BETWEEN a.date + 6 AND a.date + 9 允许的实际间隔是 6～9 天，并不只是“提前或延后一天”。
同一周期内有多笔候选交易时，一笔交易可能生成多条路径，因此需要明确最佳匹配的选择方式。

第一步：生成候选关系

先建立每笔交易与“下一笔可能的周期付款”之间的边。这里只需要一次自连接：

CREATE MULTISET TABLE weekly_edges AS
(
    SELECT
        a.account,
        a.transaction_id AS from_transaction_id,
        b.transaction_id AS to_transaction_id,
        a.transaction_date AS from_date,
        b.transaction_date AS to_date,
        a.amount AS from_amount,
        b.amount AS to_amount
    FROM table1 AS a
    INNER JOIN table1 AS b
      ON a.account = b.account
     AND b.transaction_date BETWEEN a.transaction_date + 6
                                AND a.transaction_date + 8
     AND b.amount BETWEEN a.amount * 0.90
                      AND a.amount * 1.10
     /* 如果整个序列必须使用相同渠道，再启用：
     AND a.channel = b.channel
     */
)
WITH DATA
PRIMARY INDEX (account);

如果“提前或延后一天”是相对 7 天而言，窗口通常应为 6～8 天。原来的 6～9 天还允许延后两天。如果这符合业务要求，可以继续使用 6～9 天。

金额条件建议以较早的交易为基准：

b.amount BETWEEN a.amount * 0.90 AND a.amount * 1.10

它比下面这种写法更直观：

a.amount BETWEEN b.amount * 0.90 AND b.amount * 1.10

这两个条件并不完全对称。

第二步：用递归 CTE 延伸序列

下面的查询从每条候选边开始，持续寻找下一周期的交易。由于日期始终向后推进，不会形成循环：

WITH RECURSIVE payment_chain
(
    account,
    start_transaction_id,
    current_transaction_id,
    start_date,
    current_date,
    start_amount,
    current_amount,
    recurrence_count
) AS
(
    SELECT
        account,
        from_transaction_id,
        to_transaction_id,
        from_date,
        to_date,
        from_amount,
        to_amount,
        2
    FROM weekly_edges

    UNION ALL

    SELECT
        c.account,
        c.start_transaction_id,
        e.to_transaction_id,
        c.start_date,
        e.to_date,
        c.start_amount,
        e.to_amount,
        c.recurrence_count + 1
    FROM payment_chain AS c
    INNER JOIN weekly_edges AS e
      ON e.account = c.account
     AND e.from_transaction_id = c.current_transaction_id
    WHERE c.recurrence_count < 52
)
SELECT
    account,
    start_transaction_id,
    MAX(recurrence_count) AS recurrence_count,
    MIN(start_date) AS pattern_start_date,
    MAX(current_date) AS pattern_end_date
FROM payment_chain
GROUP BY 1, 2
HAVING MAX(recurrence_count) >= 4;

这里把第一对交易计为两次付款，所以：

HAVING MAX(recurrence_count) >= 4

表示至少找到了 4 笔连续的周期付款。

c.recurrence_count < 52 是一个业务保护值，用于防止异常数据触发过长的递归，并不是 Teradata 的固定限制。具体数值应根据分析周期调整。

防止金额逐期漂移

如果要求整个序列的金额始终在第一笔金额的 ±10% 以内，还应在递归时加入以下条件：

AND e.to_amount BETWEEN c.start_amount * 0.90
                    AND c.start_amount * 1.10

完整的递归连接部分可以写成：

FROM payment_chain AS c
INNER JOIN weekly_edges AS e
  ON e.account = c.account
 AND e.from_transaction_id = c.current_transaction_id
 AND e.to_amount BETWEEN c.start_amount * 0.90
                     AND c.start_amount * 1.10
WHERE c.recurrence_count < 52

三种金额规则的含义不同：

与上一笔比较：允许金额逐渐变化，适合账单金额缓慢上涨的场景。
与第一笔比较：限制整个序列的金额范围，更适合固定订阅或固定扣款。
与序列平均值比较：稳定性更好，但递归时需要同时维护累计金额和笔数。

处理同一窗口内的多笔候选交易

如果一个账户在 6～8 天的窗口内有多笔金额接近的交易，递归仍可能产生大量分支。可以先为每笔交易选出与目标周期和金额最接近的候选：

CREATE MULTISET TABLE weekly_edges AS
(
    SELECT
        a.account,
        a.transaction_id AS from_transaction_id,
        b.transaction_id AS to_transaction_id,
        a.transaction_date AS from_date,
        b.transaction_date AS to_date,
        a.amount AS from_amount,
        b.amount AS to_amount
    FROM table1 AS a
    INNER JOIN table1 AS b
      ON a.account = b.account
     AND b.transaction_date BETWEEN a.transaction_date + 6
                                AND a.transaction_date + 8
     AND b.amount BETWEEN a.amount * 0.90
                      AND a.amount * 1.10
    QUALIFY ROW_NUMBER() OVER
    (
        PARTITION BY a.account, a.transaction_id
        ORDER BY
            ABS((b.transaction_date - a.transaction_date) - 7),
            ABS(b.amount - a.amount),
            b.transaction_id
    ) = 1
)
WITH DATA
PRIMARY INDEX (account);

这种方法可以大幅减少路径分叉，但它采用的是贪心选择。局部最接近的下一笔交易，不一定能组成最长的完整序列。如果业务需要找出所有可能的序列，就不能直接丢弃其他候选。

两周和每月模式

两周一次的模式只需修改候选边的日期窗口。例如允许目标日期前后一天时，可以写成：

b.transaction_date BETWEEN a.transaction_date + 13
                       AND a.transaction_date + 15

月度付款不宜固定按 30 天计算，因为月份长度为 28～31 天。可以围绕 ADD_MONTHS 设置容差：

b.transaction_date BETWEEN ADD_MONTHS(a.transaction_date, 1) - 1
                       AND ADD_MONTHS(a.transaction_date, 1) + 1

如果一笔交易可能同时符合多种频率，建议分别生成 weekly、fortnightly 和 monthly 结果，再按以下规则解决冲突：

优先选择连续次数更多的模式；
连续次数相同时，选择日期偏差总和更小的模式；
如果仍然相同，则选择金额偏差更小的模式。

Teradata 侧的性能注意事项

分析前先按业务条件过滤无关交易，例如退款、零金额、小额测试交易和不相关的交易类型。
PRIMARY INDEX 应尽量让同一账户的数据落在同一 AMP，同时注意大账户可能造成的数据倾斜。
为参与连接和过滤的列收集统计信息，至少应包括 account、日期列，以及实际使用的组合统计信息。
候选边表只保留递归需要的列，不要使用 a.*、b.*，以免增加 spool 和 I/O。
如果要求 channel 相同，可以把它保留在候选边和递归键中。如果 channel 允许变化，就不要按 channel 分割序列。
日期列如果命名为 date，容易与关键字或数据类型混淆。建议改为 transaction_date，或者按照现有对象定义进行转义。
全量运行前，先检查候选边数量、每笔交易的平均候选数和最大候选数。性能问题通常来自候选分支过多，而不是递归层数本身。

部分较新的 Teradata 版本还提供 MATCH_RECOGNIZE，可以用于顺序模式识别。不过，它更适合检查排序后的相邻记录。当数据中夹杂无关交易、同一天存在多笔候选，或者需要跳过记录寻找最佳路径时，候选边配合递归 CTE 通常更容易准确表达业务规则。是否支持该功能以及具体语法，应以当前使用的 Teradata 版本为准。