如何在SQL中按最早日期分组，处理同日期多订单场景？

你的问题核心是仅统计每个客户最早下单日期当天的订单数据，原查询错误地将客户所有订单纳入了聚合范围。下面提供两种可靠的解决方案：

方法一：先获取客户最早下单日期，再筛选对应订单 #

先通过子查询得到每个客户的最早下单日期，再关联订单表筛选出该日期的所有订单，最后关联客户和商品表完成聚合计算：

SELECT
    o.customer_id,
    c.name,
    cust_first_purchase.first_purchase_date,
    COUNT(*) AS n_items,
    SUM(i.price) AS total_price
FROM (
    -- 第一步：获取每个客户的最早下单日期
    SELECT
        customer_id,
        MIN(created_at) AS first_purchase_date
    FROM orders
    GROUP BY customer_id
) AS cust_first_purchase
-- 关联订单表，仅保留客户最早下单日期的订单
INNER JOIN orders o
    ON o.customer_id = cust_first_purchase.customer_id
    AND o.created_at = cust_first_purchase.first_purchase_date
INNER JOIN customers c
    ON o.customer_id = c.id
INNER JOIN items i
    ON o.item_id = i.id
GROUP BY
    o.customer_id,
    c.name,
    cust_first_purchase.first_purchase_date

方法二：使用窗口函数标记最早下单日期 #

利用窗口函数MIN() OVER (PARTITION BY ...)为每个订单标记所属客户的最早下单日期，再筛选出日期匹配的订单进行聚合：

SELECT
    customer_id,
    name,
    first_purchase_date,
    COUNT(*) AS n_items,
    SUM(price) AS total_price
FROM (
    -- 为每个订单添加所属客户的最早下单日期标记
    SELECT
        o.customer_id,
        c.name,
        MIN(o.created_at) OVER (PARTITION BY o.customer_id) AS first_purchase_date,
        i.price,
        o.created_at
    FROM orders o
    INNER JOIN customers c ON o.customer_id = c.id
    INNER JOIN items i ON o.item_id = i.id
) AS order_details
-- 仅保留最早下单日期的订单
WHERE created_at = first_purchase_date
GROUP BY
    customer_id,
    name,
    first_purchase_date

两种方法都能得到你期望的结果：

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