Python/SQL实现连续N个月周期性交易识别及符合条件卡号筛选

需求说明 #

筛选指定时间范围内，同一卡号、同一商户、相同交易金额下，存在至少N次连续月份交易的卡号。当前示例要求N=3，也可自定义为4、5等任意连续次数要求。

实现方案 #

Python 实现 #

核心逻辑：将交易日期转换为总月份数值（避免跨年计算误差），分组后对去重排序的月份计算连续序列长度，判断是否满足阈值要求。
完整可运行代码如下：

import pandas as pd

# 示例数据集
df = pd.DataFrame.from_dict({
    "Card_No":[1234,1234,1234,4321,4321,4321],
    "Merchant_Country":["USA", "USA", "USA", "USA","USA", "USA"],
    "Merchant_Name":["BestBuy", "BestBuy", "BestBuy", "BestBuy","BestBuy", "BestBuy"],
    "Date": ["2021-01-15", "2021-02-15", "2021-03-15", "2021-04-15", "2021-05-15", "2021-07-15"],
    "TrxAmount": [99.99, 99.99, 99.99, 89.99, 89.99, 89.99]
})

# 1. 日期预处理：转换为总月份数（年*12+月，跨年场景也可正常计算）
df['Date'] = pd.to_datetime(df['Date'])
df['month_total'] = df['Date'].dt.year * 12 + df['Date'].dt.month

# 2. 定义连续月份校验函数
def check_continuous(months, n=3):
    sorted_months = sorted(months.unique())
    if len(sorted_months) < n:
        return False
    max_continuous = current = 1
    for i in range(1, len(sorted_months)):
        if sorted_months[i] - sorted_months[i-1] == 1:
            current += 1
            max_continuous = max(max_continuous, current)
        else:
            current = 1
    return max_continuous >= n

# 3. 分组校验，筛选符合条件的卡号
N = 3 # 可自定义为任意连续次数要求
result = df.groupby(["Card_No", "Merchant_Name","TrxAmount"])['month_total'].apply(lambda x: check_continuous(x, N)).reset_index(name='is_qualified')
qualified_cards = result[result['is_qualified']]['Card_No'].unique()

print("符合条件的卡号：", qualified_cards) # 示例输出为 [1234]

SQL 实现 #

核心逻辑：通过窗口函数解决「连续序列岛屿问题」，计算连续月份分组的记录数，判断是否满足阈值，支持MySQL 8.0+、PostgreSQL、Hive等支持窗口函数的SQL引擎。
实现代码如下：

-- 定义连续次数阈值，可自定义
SET @N = 3;

WITH trx_preprocess AS (
    -- 第一步：预处理交易年月，去重同组同月的重复交易
    SELECT 
        Card_No,
        Merchant_Name,
        TrxAmount,
        YEAR(Date)*12 + MONTH(Date) AS month_total
    FROM transaction_table
    -- 可添加时间范围过滤，如 WHERE YEAR(Date) = 2020
    GROUP BY Card_No, Merchant_Name, TrxAmount, month_total
),
continuous_group AS (
    -- 第二步：生成连续分组标识，相同标识的记录属于同一连续月份序列
    SELECT 
        *,
        month_total - ROW_NUMBER() OVER(PARTITION BY Card_No, Merchant_Name, TrxAmount ORDER BY month_total) AS group_flag
    FROM trx_preprocess
),
group_count AS (
    -- 第三步：统计每个连续序列的长度
    SELECT 
        Card_No,
        Merchant_Name,
        TrxAmount,
        group_flag,
        COUNT(*) AS continuous_length
    FROM continuous_group
    GROUP BY Card_No, Merchant_Name, TrxAmount, group_flag
)
-- 第四步：筛选符合条件的卡号
SELECT DISTINCT Card_No
FROM group_count
WHERE continuous_length >= @N;

示例场景下执行上述SQL，返回结果为卡号1234。

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