SQL多条件筛选求助：连续月份交易数据过滤需求

我是SQL初学者，知道这类问题可以用Python解决，但决定用纯SQL实现。现有数据表python_table，包含以下4列：

• account_receivable：应收账户ID
• datum：交易日期
• amount：金额
• account_payable：应付账户ID

需要筛选满足以下3个条件的记录：

1. 同一account_payable至少有连续3个月的交易
2. 同一月份内该account_payable对应交易不超过1笔
3. 连续月份交易组中，各交易日期与组内最早日期的间隔不超过5天

示例数据（标注符合/不符合条件的组别） #

61441   2014-04-28  102 45437871
61441   2014-04-28  15346   45437871
61441   2014-05-16  98  306658150
**61441 2014-04-28  711 323671229
61441   2014-05-23  694 323671229
61441   2014-06-25  701 323671229
61441   2014-07-25  702 323671229
61441   2014-08-25  694 323671229
61441   2014-09-25  644 323671229**
**61441 2014-06-09  3697    342058995   this set will not match condition as interval for day
61441   2014-07-04  3692    342058995   from lowest to highest is more than 5 days
61441   2014-08-06  3665    342058995
61441   2014-09-10  3672    342058995**
61441   2014-06-10  8409    357368301
61441   2014-04-24  4136    412899724
**61441 2014-04-28  1261    440261807
61441   2014-05-23  1271    440261807
61441   2014-06-25  1267    440261807
61441   2014-07-25  1259    440261807
61441   2014-08-25  1274    440261807
61441   2014-09-25  1120    440261807**
61441   2014-06-19  141 441460477
61441   2014-08-06  314 518735975
**61441 2014-04-01  17032   547166056
61441   2014-05-02  45773   547166056
61441   2014-06-02  17821   547166056
61441   2014-07-01  17445   547166056
61441   2014-08-01  25562   547166056
61441   2014-09-02  17459   547166056**
61441   2014-09-05  157 686201636
61441   2014-09-19  126 686201636
**61441 2014-04-14  7233    762490320  This will not match condition as it has 3 transactions in
61441   2014-05-19  9703    762490320  same month
61441   2014-06-16  8875    762490320
61441   2014-07-14  8274    762490320
61441   2014-07-18  1436    762490320
61441   2014-07-28  841 762490320
61441   2014-08-15  11008   762490320
61441   2014-09-16  8334    762490320**
61441   2014-05-16  340 838201881
61441   2014-05-21  2480    838201881
61441   2014-07-14  295 838201881
61441   2014-07-14  933 838201881
61441   2014-08-25  1696    838201881
61441   2014-08-25  849 838201881
61441   2014-04-28  2011    842644517
61441   2014-09-22  8295    842644517
61441   2014-07-09  35  982718888

已编写的基础SQL语句 #

SELECT t1.account_receivable,t1.datum,t1.amount,t1.account_payable
FROM python_table as t1
WHERE t1.account_receivable IN (
    SELECT t2.account_receivable
    FROM python_table as t2
    GROUP BY 1                  )

完整筛选方案SQL #

下面的SQL使用窗口函数逐步满足三个条件，注释会说明每一步的作用：

WITH cleaned_data AS (
    -- 先过滤掉同一account_payable同一月份有多笔交易的记录（满足条件2）
    SELECT 
        account_receivable,
        datum,
        amount,
        account_payable,
        DATE_TRUNC('month', datum) AS transaction_month
    FROM python_table
    GROUP BY account_receivable, datum, amount, account_payable, transaction_month
    HAVING COUNT(*) = 1
),
ranked_transactions AS (
    -- 对每个account_payable的交易按月份排序，计算与前一个交易的月份差
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY account_payable ORDER BY transaction_month) AS rn,
        -- 计算当前交易月份与前一个交易月份的间隔
        EXTRACT(MONTH FROM transaction_month) - EXTRACT(MONTH FROM LAG(transaction_month) OVER (PARTITION BY account_payable ORDER BY transaction_month)) AS month_diff,
        -- 记录每个account_payable的最早交易日期
        MIN(datum) OVER (PARTITION BY account_payable) AS earliest_date
    FROM cleaned_data
),
continuous_groups AS (
    -- 识别连续月份的交易组：当month_diff=1时属于同一连续组，否则开启新组
    SELECT 
        *,
        SUM(CASE WHEN month_diff != 1 OR month_diff IS NULL THEN 1 ELSE 0 END) OVER (PARTITION BY account_payable ORDER BY transaction_month) AS group_id
    FROM ranked_transactions
),
valid_groups AS (
    -- 筛选出连续月份数≥3的组，且组内所有交易日期与最早日期间隔≤5天（满足条件1和3）
    SELECT 
        account_payable,
        group_id
    FROM continuous_groups
    GROUP BY account_payable, group_id
    HAVING COUNT(*) >= 3 
        AND MAX(ABS(datum - earliest_date)) <= INTERVAL '5 days'
)
-- 最终查询符合条件的所有记录
SELECT 
    cd.account_receivable,
    cd.datum,
    cd.amount,
    cd.account_payable
FROM cleaned_data cd
JOIN continuous_groups cg ON cd.account_receivable = cg.account_receivable 
    AND cd.datum = cg.datum 
    AND cd.account_payable = cg.account_payable
JOIN valid_groups vg ON cg.account_payable = vg.account_payable 
    AND cg.group_id = vg.group_id
ORDER BY cd.account_payable, cd.datum;

代码说明 #

1. cleaned_data：先剔除同一account_payable同一月份有多笔交易的记录，直接满足条件2。
2. ranked_transactions：给每个account_payable的交易按月份排序，计算相邻交易的月份差，同时记录该账户的最早交易日期。
3. continuous_groups：通过累加标记划分连续月份的交易组，相邻月份差为1的归为同一组。
4. valid_groups：筛选出组内交易数≥3（满足条件1），且组内所有交易日期与最早日期间隔≤5天（满足条件3）的组ID。
5. 最后通过关联查询，取出所有符合条件的记录。

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