如何用SQL按客户最早下单日期分组统计（含多笔同日首单）

要只统计客户最早下单日期的聚合数据（含同一天多笔订单的情况），核心是先锁定每个客户的最早下单日期，再筛选对应订单做统计，以下是两种实用实现方式：

方法1：子查询关联筛选 #

SELECT
    c.customer_id,
    c.name,
    MIN(o.order_date) AS first_purchase_date,
    SUM(i.quantity) AS n_items,
    SUM(i.quantity * i.price) AS total_price
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN items i ON o.order_id = i.order_id
WHERE o.order_date = (
    SELECT MIN(order_date)
    FROM orders
    WHERE customer_id = c.customer_id
)
GROUP BY c.customer_id, c.name, o.order_date;

方法2：窗口函数（大数据量更高效） #

WITH customer_first_orders AS (
    SELECT
        o.customer_id,
        o.order_date,
        o.order_id,
        RANK() OVER (PARTITION BY o.customer_id ORDER BY o.order_date) AS date_rank
    FROM orders o
)
SELECT
    c.customer_id,
    c.name,
    MIN(cfo.order_date) AS first_purchase_date,
    SUM(i.quantity) AS n_items,
    SUM(i.quantity * i.price) AS total_price
FROM customers c
JOIN customer_first_orders cfo ON c.customer_id = cfo.customer_id
JOIN items i ON cfo.order_id = i.order_id
WHERE cfo.date_rank = 1
GROUP BY c.customer_id, c.name;

核心说明 #

• 两种方式都确保仅统计客户最早下单日期当天的所有订单，同一天多笔订单的商品数量和总价会正确累加。
• 子查询方式适合小数据集，窗口函数则通过一次性标记所有客户的最早订单，性能更优。
• 分组时需保留customer_id和name（方法1需额外加order_date，因数据库严格模式要求聚合列需在分组中）。

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