银行流水重复交易查询PostgreSQL转MySQL报错问题咨询

前置要求 #

• 需要使用MySQL 8.0及以上版本，低于该版本不支持CTE（WITH语法）、窗口函数（LAG/ROW_NUMBER/LAST_VALUE），无法执行该逻辑。

报错根因拆解 #

• 第一版语法报错：MySQL不支持SELECT子句中先写计算列、最后放置*的写法，必须将*放在最前，否则会触发语法解析错误。
• 第二版Can't group on 'transactions_count'报错：属于MySQL对位置序号引用的兼容问题，部分sql_mode配置下会将GROUP BY 1/ORDER BY 2的位置序号误解析为别名依赖关系，直接使用明确的列名/别名替代位置序号即可规避。
• 隐藏逻辑缺陷：PostgreSQL中直接对DATE类型做减号运算会返回天数差，MySQL中直接减会将日期转为YYYYMMDD格式的整数相减（例如'2021-01-05' - '2020-12-31' = 4874），完全不符合日期差计算预期，必须用DATEDIFF()函数计算天数差。

可直接执行的MySQL版本查询 #

WITH transactions_with_date_diff AS (
    SELECT
        *,
        -- 行号可用于后续校验交易序列，不需要可删除
        ROW_NUMBER() OVER(PARTITION BY description ORDER BY accounting_date) AS rn,
        -- 计算当前交易与同描述上一笔交易的间隔天数
        DATEDIFF(
            accounting_date, 
            LAG(accounting_date) OVER(PARTITION BY description ORDER BY accounting_date)
        ) AS date_diff,
        -- 修正窗口帧后取同描述下最新交易金额，不需要可删除
        LAST_VALUE(amount) OVER(
            PARTITION BY description 
            ORDER BY accounting_date
            RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS latest_amount
    FROM transactions
)
SELECT
    description,
    COUNT(*) AS transactions_count,
    MIN(accounting_date) AS subscription_started,
    MAX(accounting_date) AS latest_transaction,
    SUM(amount) AS total_amount
FROM transactions_with_date_diff
WHERE
    date_diff IS NOT NULL
    -- 25-35天覆盖月度周期的常规波动范围
    AND date_diff BETWEEN 25 AND 35
GROUP BY description
HAVING COUNT(*) > 1
ORDER BY transactions_count DESC;

精简版（无冗余列） #

如果不需要做交易序列校验、金额一致性校验，可删除CTE中冗余的窗口函数列，降低计算开销：

WITH transactions_with_date_diff AS (
    SELECT
        *,
        DATEDIFF(
            accounting_date, 
            LAG(accounting_date) OVER(PARTITION BY description ORDER BY accounting_date)
        ) AS date_diff
    FROM transactions
)
SELECT
    description,
    COUNT(*) AS transactions_count,
    MIN(accounting_date) AS subscription_started,
    MAX(accounting_date) AS latest_transaction,
    SUM(amount) AS total_amount
FROM transactions_with_date_diff
WHERE
    date_diff IS NOT NULL
    AND date_diff BETWEEN 25 AND 35
GROUP BY description
HAVING COUNT(*) > 1
ORDER BY transactions_count DESC;

测试说明 #

你提供的示例数据中所有交易的description均不重复，执行查询会返回空结果。可添加同描述下间隔30天左右的交易测试，例如新增一条accounting_date = '2021-02-01'、description = 'APPLE.COM/BILL'、amount = -24.03的记录，APPLE的两笔交易间隔为32天，会被识别为周期性交易。 #

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