如何筛选近3个月每月同日（±1天）重复的交易记录？

原交易表（transactions）结构及数据 #

查询需求 #

筛选近3个月内每月在同日（允许±1天偏差）重复发生的交易，按merchant、category、user、amount分组，排除那些没有在每个月至少有1条符合日期条件记录的分组，最终返回符合条件的交易记录，并额外显示交易日期的当月天数（格式为两位数字，如02）。

期望查询结果 #

现有查询的问题 #

当前使用的SQL只能获取上月当日的交易，完全无法满足需求：

SELECT *
FROM transactions 
WHERE
    transaction_date = DATE_SUB(CURDATE(), INTERVAL 1 month) AND
    DAY(transaction_date) = DAY(DATE_SUB(CURDATE(), INTERVAL 1 month));

优化后的SQL查询 #

WITH transaction_groups AS (
    -- 预处理：筛选近3个月交易，提取日、所属月份，标记基准日
    SELECT 
        t.*,
        DAY(transaction_date) AS day_of_the_month,
        DATE_FORMAT(transaction_date, '%Y-%m') AS transaction_month,
        DAY(transaction_date) AS target_day
    FROM transactions t
    WHERE transaction_date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
),
valid_groups AS (
    -- 验证分组：统计每个商户-分类-用户-金额-基准日组合覆盖的月份数，仅保留覆盖全部3个月的组
    SELECT 
        merchant,
        category,
        user,
        amount,
        target_day,
        COUNT(DISTINCT transaction_month) AS month_count
    FROM transaction_groups
    GROUP BY merchant, category, user, amount, target_day
    HAVING month_count = 3
),
valid_transactions AS (
    -- 筛选有效交易：关联有效组，取最新月份中符合±1天偏差的记录
    SELECT 
        tg.*
    FROM transaction_groups tg
    JOIN valid_groups vg 
        ON tg.merchant = vg.merchant
        AND tg.category = vg.category
        AND tg.user = vg.user
        AND tg.amount = vg.amount
        AND ABS(tg.day_of_the_month - vg.target_day) <= 1
    WHERE tg.transaction_month = (SELECT MAX(transaction_month) FROM transaction_groups)
)
-- 最终输出，格式化日部分为两位数字
SELECT 
    id,
    merchant,
    category,
    user,
    type,
    amount,
    transaction_date,
    LPAD(day_of_the_month, 2, '0') AS day_of_the_month
FROM valid_transactions
ORDER BY merchant;

逻辑说明 #

1. 预处理阶段：先过滤出近3个月的交易，提取每条交易的日、所属月份，同时标记该交易对应的基准日，用于后续匹配±1天的范围。
2. 验证分组阶段：按商户、分类、用户、金额、基准日分组，统计这个组合覆盖了多少个不同的月份。只有覆盖全部3个月的组合，才是每月重复发生的有效交易组。
3. 筛选有效交易阶段：把预处理的交易和有效组关联，筛选出最新月份里，交易日和基准日偏差在±1天内的记录，最后把日部分格式化为两位数字，匹配期望结果的显示格式。

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