按event_id分组查询累计交易金额首次达阈值对应支付日期的实现方法

你原有写法的核心问题是累加变量@sum没有按event_id重置，所有event_id的交易记录会共用同一个累加器，无法得到每个分组的正确累计值，可按数据库版本选择以下方案修改：

方案1：MySQL 8.0+/MariaDB 10.2+ 支持窗口函数（推荐） #

用分组累加窗口函数直接实现，逻辑更清晰，无需依赖自定义变量，稳定性更高。

WITH cumulative_calc AS (
    SELECT 
        event_id,
        date_paid,
        -- 按event_id分组，按支付日期排序计算累计净金额
        SUM(amount - refund_amount - fee) OVER (
            PARTITION BY event_id 
            ORDER BY date_paid 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS running_total
    FROM transactions
    WHERE t_status = 'paid'
)
SELECT 
    event_id,
    MIN(date_paid) AS first_reach_threshold_date -- 取首次达标日期
FROM cumulative_calc
WHERE running_total >= 20 -- 替换为你的阈值
GROUP BY event_id;

如果需要关联events_history表取额外字段，直接在外层加JOIN逻辑即可。

方案2：MySQL 5.x 兼容版本（自定义双变量实现） #

如果数据库版本不支持窗口函数，用两个变量分别记录当前分组的event_id和累计值，遇到新的event_id自动重置累加器。

SELECT event_id, MIN(date_paid) AS first_reach_threshold_date
FROM (
    SELECT 
        event_id,
        date_paid,
        -- 同个event_id累加，新event_id重置累加值为当前行净金额
        @sum := IF(@pre_event = event_id, @sum + amount - refund_amount - fee, amount - refund_amount - fee) AS running_total,
        @pre_event := event_id -- 更新当前分组的event_id
    FROM transactions
    CROSS JOIN (SELECT @sum := 0, @pre_event := NULL) AS init
    WHERE t_status = 'paid'
    -- 必须先按event_id排序，再按日期排序，保证同分组记录连续
    ORDER BY event_id, date_paid
) AS temp
WHERE running_total >= 20 -- 替换为你的阈值
GROUP BY event_id;

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