MySQL 查询非首次购买且月度交易总额大于 0 的客户

2026-10-24支付小周

结论

先按“客户 + 月份”汇总交易金额，再计算每位客户的首次购买时间。老客户需要同时满足两个条件：

当月交易总额 > 0
首次购买时间早于当月第一天

这样可以排除首次购买发生在当月的新客户。

假设表中包含以下字段：

customer_id：客户 ID
transaction_date：交易时间
amount：交易金额

MySQL 8.0 可以这样写：

WITH first_purchase AS (
    SELECT
        customer_id,
        MIN(transaction_date) AS first_purchase_at
    FROM transactions
    GROUP BY customer_id
),
monthly_totals AS (
    SELECT
        customer_id,
        CAST(DATE_FORMAT(transaction_date, '%Y-%m-01') AS DATE) AS month_start,
        SUM(amount) AS monthly_total
    FROM transactions
    GROUP BY
        customer_id,
        CAST(DATE_FORMAT(transaction_date, '%Y-%m-01') AS DATE)
)
SELECT
    YEAR(mt.month_start) AS `Year`,
    MONTH(mt.month_start) AS `Month`,
    COUNT(*) AS NumOfCustomersWithPositiveTotals
FROM monthly_totals AS mt
JOIN first_purchase AS fp
    ON fp.customer_id = mt.customer_id
WHERE mt.monthly_total > 0
  AND fp.first_purchase_at < mt.month_start
GROUP BY mt.month_start
ORDER BY mt.month_start;

查询逻辑

first_purchase 通过下面的表达式找出每位客户最早的一笔交易：

MIN(transaction_date)

monthly_totals 把同一客户在同一自然月内的所有交易合并，计算当月净交易总额：

SUM(amount)

最终查询用下面的条件排除当月才首次购买的客户：

fp.first_purchase_at < mt.month_start

这里不宜只判断“首次交易月份不等于当前月份”。与当月第一天直接比较更准确，因为这个条件明确要求客户在该月开始前已经完成首次购买。

monthly_totals 中每位客户每个月只有一行，因此最终的：

COUNT(*)

就是符合条件的客户数。

MySQL 5.7 写法

MySQL 5.7 不支持 CTE，可以改用派生表：

SELECT
    YEAR(mt.month_start) AS `Year`,
    MONTH(mt.month_start) AS `Month`,
    COUNT(*) AS NumOfCustomersWithPositiveTotals
FROM (
    SELECT
        customer_id,
        CAST(DATE_FORMAT(transaction_date, '%Y-%m-01') AS DATE) AS month_start,
        SUM(amount) AS monthly_total
    FROM transactions
    GROUP BY
        customer_id,
        CAST(DATE_FORMAT(transaction_date, '%Y-%m-01') AS DATE)
) AS mt
JOIN (
    SELECT
        customer_id,
        MIN(transaction_date) AS first_purchase_at
    FROM transactions
    GROUP BY customer_id
) AS fp
    ON fp.customer_id = mt.customer_id
WHERE mt.monthly_total > 0
  AND fp.first_purchase_at < mt.month_start
GROUP BY mt.month_start
ORDER BY mt.month_start;

如果表中包含未完成或取消的交易

“首次购买”和“月度交易总额”通常都应基于有效交易计算。假设有效交易的状态是 completed，两个汇总需要使用相同的过滤条件：

WHERE status = 'completed'

例如：

WITH completed_transactions AS (
    SELECT
        customer_id,
        transaction_date,
        amount
    FROM transactions
    WHERE status = 'completed'
),
first_purchase AS (
    SELECT
        customer_id,
        MIN(transaction_date) AS first_purchase_at
    FROM completed_transactions
    GROUP BY customer_id
),
monthly_totals AS (
    SELECT
        customer_id,
        CAST(DATE_FORMAT(transaction_date, '%Y-%m-01') AS DATE) AS month_start,
        SUM(amount) AS monthly_total
    FROM completed_transactions
    GROUP BY
        customer_id,
        CAST(DATE_FORMAT(transaction_date, '%Y-%m-01') AS DATE)
)
SELECT
    YEAR(mt.month_start) AS `Year`,
    MONTH(mt.month_start) AS `Month`,
    COUNT(*) AS NumOfCustomersWithPositiveTotals
FROM monthly_totals AS mt
JOIN first_purchase AS fp
    ON fp.customer_id = mt.customer_id
WHERE mt.monthly_total > 0
  AND fp.first_purchase_at < mt.month_start
GROUP BY mt.month_start
ORDER BY mt.month_start;

注意事项

不要在月度汇总前使用：

WHERE amount > 0

除非业务规则明确要求忽略退款或负数交易。题目要求客户当月的交易总额大于 0，因此应先汇总该客户当月的全部有效交易，再使用：

HAVING SUM(amount) > 0

或者在外层使用：

WHERE monthly_total > 0

进行筛选。

如果“首次购买”只计算正金额订单，而退款等负金额记录也保存在同一张表中，则首次购买子查询需要单独添加：

WHERE amount > 0

月度汇总仍要保留退款记录，才能计算真实的当月净交易总额。字段名和有效交易条件需要按照实际表结构替换。