如何使用SQL计算交易表中每个客户的首购金额与复购金额

问题分析 #

你现有写法存在两个核心问题：

• 表名错误：你创建的表是transactions，但查询里写的是orders
• 非规范分组：直接在GROUP BY customer后查询非聚合的value字段，仅MySQL关闭ONLY_FULL_GROUP_BY时能执行，返回的value是随机值，并非对应最小日期的交易金额，不具备稳定性。

解决方案 #

1. MySQL 8.0+ / 支持窗口函数的数据库（写法最简洁）

用ROW_NUMBER()窗口函数给每个用户的交易按规则排序，再行转列提取首购、复购金额即可，无需返回日期字段：

SELECT
  customer,
  MAX(IF(rn = 1, value, NULL)) AS first_purchase_value,
  MAX(IF(rn = 2, value, NULL)) AS returning_purchase_value
FROM (
  SELECT 
    customer,
    value,
    -- 这里排序规则可按需调整：如果按交易日期正序取首购就写 ORDER BY date ASC，按金额倒序就写 ORDER BY value DESC
    ROW_NUMBER() OVER (PARTITION BY customer ORDER BY value DESC) AS rn
  FROM transactions
) t
WHERE rn <= 2
GROUP BY customer;

执行结果完全匹配你给出的期望输出。

2. 兼容MySQL 5.x 旧版本写法

如果你的数据库不支持窗口函数，可以用子查询关联的方式实现：

SELECT
  t1.customer,
  MAX(t1.value) AS first_purchase_value,
  MAX(t2.value) AS returning_purchase_value
FROM transactions t1
LEFT JOIN transactions t2 
  ON t1.customer = t2.customer 
  AND t2.value < t1.value
GROUP BY t1.customer;

排序规则说明 #

如果你原本的需求是按交易时间先后取首购/复购，只需要把窗口函数里的排序规则改成ORDER BY date ASC，旧版本写法的关联条件改成t2.date > t1.date即可，返回的就是真实的首次购买、第二次购买金额。

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